Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

1,690 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
17 votes
0 answers
2k views

Extract TopoJSON directly from PostGIS

I have a simple "region" table: id (TEXT), name (TEXT), geom (MULTIPOLYGON). Here's the query I can use to extract a GeoJSON FeatureCollection: SELECT row_to_json(fc) FROM ( SELECT '...
user avatar
  • 479
14 votes
0 answers
665 views

High PostgreSQL/PostGIS memory and disk usage when saving QGIS project

Why would simply pressing the Save button in QGIS cause my local PostgreSQL instance to cause memory and disk usage to spike to "Not Responding" levels? Seems to me like the PostgreSQL ...
user avatar
  • 22.5k
10 votes
0 answers
263 views

Saving ArcGIS Pro symbology to PostGIS DB for each layer

I want to have a default style/symbology assigned to each layer in a PostGIS database so that whenever it is loaded in ArcGIS Pro, it will come up with the default style. There is a way in QGIS to ...
user avatar
  • 1,440
10 votes
0 answers
486 views

Calculate volume of space defined by multiple overlapping 3D polygons

I have thousands of 3D polygons scattered in space but they mostly overlap with many of their neighbors in big extent. Is there any way how to compute volume they define effectively? I would love ...
user avatar
  • 203
9 votes
0 answers
206 views

Intersections in linestrings using PostGIS

I need to get the intersections of streets and create a graph, so to get the intersections I use this select: SELECT ST_GeometryType(geom), geom FROM ( SELECT a.gid, ST_Intersection(a....
user avatar
9 votes
0 answers
516 views

Removing overlapping features by percentage in PostGIS

I have geometry records of type MultiLineStringZM and I am trying to INSERT new records that overlap some of the records currently in the table. If they were type MultiPolygon I could use ST_Area() as ...
user avatar
  • 695
8 votes
0 answers
87 views

Does QGIS use data source-specific operations?

This may be a very basic question, but I couldn't find an appropriate answer. Does QGIS use data source specific operations where available and appropriate? For example, when using the Join attributes ...
user avatar
  • 346
8 votes
1 answer
1k views

PostGIS sequence in QGIS not working

I have a Multipolygon-Layer stored in a PostgreSQL/PostGIS database. The primary key field 'gid' is SERIAL. When trying to add a new polygon in QGIS (LYON 2.12.1 on OSX El Capitan), the attributes ...
user avatar
  • 1,190
7 votes
0 answers
549 views

PostGIS issue with ST_Clip?

I'm trying to clip a raster using a polygon but I end up with a result that doesn't seem right and where some raster pixels are outside the polygon used for the clip. See image below where polygon is ...
user avatar
7 votes
0 answers
2k views

What is the best way to display a large shapefile (with polygons) as a layer on a leaflet map?

I have a leaflet map of the UK with around ~2000 points total points and a bit of metadata loading for each point with two side-panels showing the metadata. These points are drawn from several tables ...
user avatar
7 votes
0 answers
649 views

PostGIS (Postgres 9.6) - order bounding box query results by date (large table)

I'm querying database entries using a bounding box with ST_MakeEnvelope and intersecting that with a coordinates column (type GEOMETRY), consisting of simple POINT(lng, lat) entries. I've created a ...
user avatar
  • 71
7 votes
0 answers
1k views

PostGIS: Storing large gridded time series

I'm trying to store a large raster timeseries on PostGIS-2.2 / PostgreSQL 9.5. The data comes as a collection of HDF-EOS (basically HDF4) files. Each file contains 24 bands, representing hourly ...
user avatar
  • 171
7 votes
1 answer
2k views

Uploading raster format (*.tif) to PostGIS through raster2pgsql

I'm new to PostGIS and raster file format. I'm currently working on NetCDF files and I want to import them into PostGIS through curl command line tool but its failed for me. So I jumped on to GDAL v1....
user avatar
  • 163
6 votes
0 answers
67 views

ST_LocateBetween results into MultiLineString cannot contain MultiLineString element error

I found a problem while calculating the geometry between two measures using line string geometry with M coordinates. Query execution results into [XX000] ERROR: MultiLineString cannot contain ...
user avatar
  • 111
6 votes
0 answers
368 views

Error trying to split a Linestring in PostGis

I'm trying to split a group of Linestrings when they cross a Polygon shape, I'm using the following query: SELECT v.*, c.name, (st_dump(st_split(v.geom, c.geom))).geom AS geom INTO table FROM ...
user avatar
6 votes
0 answers
249 views

Implementing PostGIS query using sf in R to make ST_Intersection faster

I'm trying to implement this query that speeds up st_intersections by using faster functions, namely ST_Contains and ST_Within, to do ST_Intersection only where absolutely necessary, from PostGIS to R ...
user avatar
  • 3,279
6 votes
1 answer
2k views

Fast Dynamic MVT generation

I am trying to migrate from serving static MVT to generated on the backend by PostGIS with ST_AsMVT(). Or find a way of minimizing the time of building MVT from the database. I have a dataset of ...
user avatar
  • 111
6 votes
1 answer
140 views

Joining points using PostGIS

I have a set of points which represents a series of telegraph poles. I want to link them to simulate the wire running along them. The code I've been using is shown below. SELECT DISTINCT ON (p1.id) ...
user avatar
6 votes
0 answers
184 views

ST_3DIntersects says 'no' despite geometries intersecting

Considering the following query I expect both geomb and geomc to 3D Intersect with geoma. According to postgis, only geomc intersects. Unfortunately I can't come up with a more simplified polygon as ...
user avatar
  • 3,217
6 votes
0 answers
291 views

PostGIS ST_Length returns 0 for geographies with high number of decimals

I'm having a weird issue when computing the distance between two geographies using ST_Length (PostgreSQL 10.4, PostGIS 2.4.4). Basically if the number of decimals is above six, ST_Length returns 0 in ...
user avatar
  • 331
6 votes
1 answer
837 views

Backup QGIS (3.2.1) layer to independent PostGIS table?

I'd like to make multiple, progressive backup copies of QGIS (3.2.1) layers that are stored in PostGIS tables. I've tried multiple routes to export them as files on disk, but they all seem to destroy ...
user avatar
6 votes
0 answers
412 views

ST_Difference in PostGIS : problem with geometry result (Polygon)

I'm experimenting a result issue using the ST_Difference spatial function in PostGIS. Input : Feature 1 and 2 overlaps each other. Both contains 5 vertices, like a rectangle (4 plus an additional one ...
user avatar
  • 61
6 votes
0 answers
1k views

Force layer reload in QGIS?

I'm working on integrating QGIS and PostGIS, but looking for a way to force a layer reload. I don't need it to be automated -- I'm happy to run a command every time I want to refresh the map. The ...
user avatar
  • 655
6 votes
0 answers
137 views

Speed up identify in QGIS

I have just transferred my Postgis database onto an RDS server, and there is a bit of a performance hit when using the Identify tool in QGIS (3) compared to localhost. I presume this is mostly network ...
user avatar
  • 6,472
6 votes
1 answer
212 views

Loading vector layers from database/ schema using login username and password in QGIS?

I have a QGIS setup with a PostgreSQL / PostGIS database on a server and multiple clients. I have created schemas in the database for each user. With a username password combination, each user is ...
user avatar
6 votes
0 answers
668 views

How to add geometries using EntityFramework 6, EF designer from database, npgsql and postgis?

I have a postgre database with postgis geometries that i would like to access using Entity framework and npgsql. I tried to go for the "EF designer from database" , but when doing so i only get the ...
user avatar
  • 149
6 votes
0 answers
147 views

Automatically creating user CRS from postgis in QGIS

I have my custom CRS in table "spatial_ref_sys" in database. When I load layer from schema "public", QGIS create this CRS. But if layer from other schema, it's not. What I need to solve this problem?
user avatar
  • 133
6 votes
1 answer
757 views

Computing large distance matrix with pgrouting?

The last weeks I have tried to make a Distance Matrix with pgrouting. On small numbers I get a result. For instance < 100 will be 1 sec. < 1000 aprox 15 min. and 2000 aprox 20 min. If I am ...
user avatar
  • 317
6 votes
0 answers
394 views

Customizing rules for Address Standardizer using PostGIS?

Following PostGIS documentation, I created a custom table for having different pagc_rules (also pagc_lex and pagc_gaz) so I can standardize/normalize addresses with different formats than the US where ...
user avatar
  • 61
6 votes
0 answers
500 views

How to use pgRouting together with Django?

I am using pgRouting together with QGIS. Now I want to convert everything into a web-solution. I decided to use Django for that. Creating the data-model and receiving geographical data from the ...
user avatar
  • 1,165
6 votes
1 answer
135 views

multi-multi point pgrouting functions

I have two point layers, X layer and Y layer, with nearest node on a road network calculated for each. I wish to calculate the shortest path from X nearest node to Y nearest node. I do not want ...
user avatar
6 votes
0 answers
396 views

QGIS doesn't handle PostGis connection resets?

I'm highly frustrated by the fact that if I leave the database alone for a more than a few minutes, I can no longer insert new lines. If I restart QGIS, everything goes back to normal. I believe this ...
user avatar
  • 161
6 votes
0 answers
497 views

Postgis load raster to DB running on AWS RDS with raster2pgsql

I want to perform very common task: I have Postgresql + Postgis running on Amazon Web Service cloud, using RDS service. I have there some vector layers and can execute SQL (Postgis) commands via ...
user avatar
  • 1,689
6 votes
0 answers
110 views

Why won't my points (that I created by intersection) intersect with the geometry they've been created from?

I created a set of points by intersecting a linestring layer with the boundary of a multipolygon layer like this: CREATE TABLE freiraumkataster_grenze AS SELECT gid, ST_CollectionHomogenize(...
user avatar
6 votes
0 answers
1k views

Trilateration with error account or lack of precision

I am developing a node.js/javascript application where I would have dozens of beacons in a row (positions known), which gives me their distances to specific object (3 or 4 distances per object). The ...
user avatar
6 votes
0 answers
297 views

Spatial Precision for Editing in Multiple GIS Clients

I have something I'd like to discuss, I'll try to explain it first, why it's important, and I'll explain how ArcGIS handles the issue, which I think is a good way to handle it. Finally I'd like to ...
user avatar
  • 13.1k
5 votes
0 answers
109 views

Transform detached MultiLineString to LineString by adding connection lines between each line segment in PostGIS

I have MulitLineStrings that may consist of several detached lines like these three: What I need is to transform it to a LineString. For my use case it would be the best strategy if I could connect ...
user avatar
  • 1,170
5 votes
1 answer
85 views

Counting stacked polygons in PostGIS

In my data set I have cases where I have multiple polygons stacked upon top of each other. I assume that those polygons have equal geometries but since there are a lot of them I am not sure 100%. Data ...
user avatar
  • 721
5 votes
0 answers
426 views

Upgrading from PostGIS 2.5 to PostGIS 3.1 - calling two set returning functions in a UDF is 7x slower

I'm migrating our PostGIS 2.5 stack to PostGIS 3.1. In general it's been really smooth - a few of our operations on larger geometries are much faster, and the rest of our performance is about the same....
user avatar
5 votes
1 answer
149 views

Getting active PostgreSQL connection username when stored through authentication configuration in QGIS 3.16

How do I get the username on the current layer PostGIS in QGIS 3.16.2-Hannover? We tried examples: Getting active PostgreSQL connection username when stored through authentication configuration in ...
user avatar
5 votes
1 answer
452 views

Slow retrieve PostGIS raster value through ST_Value

I have a PostGIS DB containing a set of daily raster data from 1981 up to 2021. The inserted raster tile size is 83x90. Problem: I wanna query the data through ST_Value for a specified Lat Lon, ...
user avatar
  • 113
5 votes
0 answers
235 views

Generate global grid of circles with minimum overlap with PostGIS

I want to generate a table of geometry points in PostGIS which represent the centers of circles, each with the same radius of X meters, that together form a "grid" that covers the entire ...
user avatar
  • 183
5 votes
1 answer
116 views

Pgrouting: problem with pgr_withpointsDD

I'd like to calculate a propagation from sources using my road graph. In extenso, I'd like to answer the following question: where are the limits of my isochrones for every sources I got. My sources (...
user avatar
5 votes
1 answer
485 views

raster2pgsql not in C:\Program Files\PostgreSQL\12\bin

After adding both C:\Program Files\PostgreSQL\12\bin C:\Program Files\PostgreSQL\12\lib to my windows environmental variables PATH thing, I can't access raster2pgsql: 'raster2pgsql' is not ...
user avatar
5 votes
1 answer
195 views

ST_SnapToGrid takes a long time on

My ultimate goal is to fetch all points from a 4 million record table that fit within a given rectangle and group the results in clusters. All of the queries I have tried up until now take too long. ...
user avatar
  • 413
5 votes
1 answer
232 views

Aggregating multiple rasters within a polygon in PostGIS

I have several raster tables in PostGIS (four in fact but in this case let us assume there are just two) - same cell sizes, same alignment but with different types of value (e.g 3d_pot, 3d_area). ...
user avatar
5 votes
0 answers
619 views

PostGIS JOIN table ON ST_Intersects

The following query and subquery help me finding a parcell using a GPS coordinate: SELECT row_to_json(fc) FROM( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features ...
user avatar
  • 171
5 votes
0 answers
248 views

Increasing PostGIS/PostgreSQL memory to avoid Out Of Memory (OOM) error?

This is a follow-up question to my earlier question about the memory management of some PostGIS/PostgreSQL functions: How do PostGIS functions handle buffers and external storage? As suggested by ...
user avatar
  • 10.4k
5 votes
0 answers
106 views

ST_IsPlanar returns true even for invalid 3D polygons

We are trying to find a way to validate 3D polygons with arbitrary rotation. It seems like you should be able to do this with the function ST_IsPlanar, because it claims to "not drop the z index", but ...
user avatar
  • 3,318
5 votes
1 answer
244 views

T-rex tile server with PostGIS (Windows) : utf-8 encoding error

I want to use T-rex as vector tileserver on Windows. I load osm.pbf file (from geofabrik) to PostGIS database with osm2pgsql successfully. When I run the server with the following commands, I get the ...
user avatar
  • 93

1
2 3 4 5
34