Questions tagged [postgis]

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

1,900 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
14 votes
0 answers
1k 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 ...
RyanKDalton's user avatar
  • 23.1k
12 votes
0 answers
466 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 ...
ar-siddiqui's user avatar
  • 1,738
12 votes
0 answers
783 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 ...
vildead's user avatar
  • 231
9 votes
0 answers
119 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 ...
Geotob's user avatar
  • 376
9 votes
0 answers
334 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....
Jessicadomingues's user avatar
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 ...
geom's user avatar
  • 1,288
7 votes
0 answers
246 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 ...
JoeBe's user avatar
  • 1,230
7 votes
1 answer
600 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 ...
Olivier's user avatar
  • 71
7 votes
0 answers
172 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 ...
Stev_k's user avatar
  • 6,689
7 votes
1 answer
274 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 ...
Ganesh Nagarajan's user avatar
7 votes
0 answers
689 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 ...
JonasPedersen's 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 ...
Jeremy Kidwell's user avatar
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 ...
woudsma's user avatar
  • 71
7 votes
0 answers
599 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 ...
AndreasK's user avatar
  • 1,235
7 votes
0 answers
448 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 ...
TalL's user avatar
  • 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 ...
Tom's user avatar
  • 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....
AshokDev's user avatar
  • 163
6 votes
0 answers
108 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 ...
milan's user avatar
  • 111
6 votes
0 answers
637 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 ...
Daniel Alonso Parra's user avatar
6 votes
0 answers
313 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 ...
Elio Diaz's user avatar
  • 3,456
6 votes
0 answers
867 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 ...
Mooncake's user avatar
  • 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 ...
MiklerGM's user avatar
  • 111
6 votes
1 answer
156 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) ...
macsmith's user avatar
6 votes
1 answer
439 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 ...
Jukurrpa's user avatar
  • 331
6 votes
1 answer
928 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 ...
LorenAmelang's user avatar
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 ...
Amanda's user avatar
  • 675
6 votes
0 answers
880 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 ...
William's user avatar
  • 249
6 votes
0 answers
177 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?
Svetlana's user avatar
  • 133
6 votes
1 answer
911 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 ...
Maas's user avatar
  • 317
6 votes
0 answers
500 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 ...
Art's user avatar
  • 61
6 votes
1 answer
288 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 ---&...
PengJ's user avatar
  • 193
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 ...
Columbo25's user avatar
6 votes
0 answers
607 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 ...
michael's user avatar
  • 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(...
Janne Jakob Fleischer's 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 ...
chemabuss's user avatar
6 votes
0 answers
310 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 ...
Alex Leith's user avatar
  • 13.4k
5 votes
0 answers
223 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 ...
Theo F's user avatar
  • 1,817
5 votes
0 answers
177 views

PostGIS Topology result still has gaps and overlap

I have been trying to build a Topology in Postgis with 4000+ polygons. They are not cleaned, with a lot of gaps and overlaps. After using ST_MakeValid, creating the topology and adding topogeom column,...
Riceiceice's user avatar
5 votes
0 answers
424 views

Retrieve properties for a feature in MVT from JSONB column?

TL;DR Check the last UPDATE section. We store all of our geometries in a single hybrid PostGIS table and corresponding properties in a single JSONB column. Properties can be anything and differ for ...
jayarjo's user avatar
  • 347
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 ...
William Chow's user avatar
5 votes
0 answers
160 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. ...
four-eyes's user avatar
  • 3,368
5 votes
0 answers
740 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....
Charlie Lefrak's user avatar
5 votes
2 answers
288 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 ...
Andrey Sirbu's user avatar
5 votes
0 answers
437 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 ...
poshest's user avatar
  • 183
5 votes
0 answers
313 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):...
Muser's user avatar
  • 215
5 votes
1 answer
835 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 ...
MaartenVL's user avatar
5 votes
1 answer
340 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. ...
GNG's user avatar
  • 421
5 votes
1 answer
309 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). ...
SpatialSDE's user avatar
5 votes
0 answers
335 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 ...
tinlyx's user avatar
  • 11.1k
5 votes
0 answers
199 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 ...
Andrei's user avatar
  • 766

1
2 3 4 5
38