Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
1,863
questions with no upvoted or accepted answers
14
votes
0
answers
993
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 ...
- 23k
12
votes
0
answers
439
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 ...
- 1,708
12
votes
0
answers
729
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 to ...
- 231
9
votes
0
answers
112
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 ...
- 376
9
votes
0
answers
316
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....
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 ...
- 1,288
7
votes
0
answers
220
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 ...
- 1,210
7
votes
0
answers
167
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 ...
- 6,634
7
votes
1
answer
266
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 ...
- 145
7
votes
0
answers
682
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 ...
- 870
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 ...
- 510
7
votes
0
answers
673
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 ...
- 71
7
votes
0
answers
590
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 ...
- 1,225
7
votes
0
answers
433
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 ...
- 171
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 ...
- 171
7
votes
1
answer
3k
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....
- 163
6
votes
0
answers
104
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 ...
- 111
6
votes
0
answers
600
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
...
6
votes
0
answers
308
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
...
- 3,444
6
votes
0
answers
847
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
...
- 181
6
votes
1
answer
3k
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 ...
- 111
6
votes
1
answer
155
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)
...
- 61
6
votes
0
answers
230
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 ...
- 3,267
6
votes
0
answers
397
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 ...
- 331
6
votes
1
answer
919
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 ...
- 91
6
votes
0
answers
576
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 ...
- 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 ...
- 655
6
votes
0
answers
856
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 ...
- 249
6
votes
0
answers
172
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?
- 133
6
votes
1
answer
899
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 ...
- 317
6
votes
0
answers
479
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 ...
- 61
6
votes
1
answer
280
views
Upgrading PostGIS and PostgreSQL but running upgrade to PostgreSQL it errors?
I want to upgrade
PostGIS（2.0.4 ---> 2.2.3）
and
PostgreSQL (9.2.4 ----> 9.6)
so
First I upgrade PostGIS
1、install PostGIS 2.2.3 on postgresql 9.2.4
2、alter EXTENSION postgis 2.0.4 ---&...
- 93
6
votes
1
answer
157
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 ...
- 81
6
votes
0
answers
587
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 ...
- 1,799
6
votes
0
answers
119
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(...
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 ...
- 61
6
votes
0
answers
306
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 ...
- 13.4k
5
votes
0
answers
190
views
Long QGIS project loading time due to QgsPostgresProvider SQL calls
QGIS 3.22.8 LTR.
Setup
I have a QGIS project file which contains around 100 PostGIS layers, hosted in Azure PostgreSQL (similar to AWS RDS). All layers were added via 'Add PostGIS Layers...' tool (not ...
- 1,787
5
votes
1
answer
2k
views
ogr2ogr errors: PROJ: proj_create no database context specified, cannot prase CRS, proj.db lacks database.layout.version.major/minor metadata etc
I have a PostgreSQL/PostGIS database that I have been working on and it's generated in a docker container. I access it via PGAdmin to the docker DB or sometimes I create a backup and restore locally ...
- 51
5
votes
0
answers
145
views
Select subset that is spatially equally distributed
Let's say I have a table point with ~10.000 rows. Each row contains a POINT(x, y).
I also have a table polygon with POLYGONS(...).
The points in the point table are unequally distributed in space. ...
- 3,370
5
votes
0
answers
702
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....
- 1,206
5
votes
2
answers
269
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 ...
- 143
5
votes
0
answers
404
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 ...
- 183
5
votes
0
answers
302
views
How to get the raster values of a point using Django's ORM (equivalent to ST_Value in PostGIS)?
I am working on a Django project that uses Django ORM to export raster data into PostGIS database. Here is the Django model:
from django.contrib.gis.db import models
class RasterWithName(models.Model):...
- 215
5
votes
1
answer
773
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 ...
- 71
5
votes
1
answer
328
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. ...
- 421
5
votes
1
answer
291
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). ...
- 51
5
votes
0
answers
330
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 ...
- 11k
5
votes
0
answers
185
views
How to insert raster and other columns using raster2pgsql?
I would like to have a table with a few more columns than just the raster data. Let's say I'd like to have anotherColumn.
I tried
raster2pgsql -s 3857 -R -a -k -f public.table.image -q -e -F -X ...
- 766
5
votes
0
answers
142
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 ...
- 3,488