Questions tagged [postgis]

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

1,555 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
14
votes
0answers
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 '...
11
votes
0answers
457 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 ...
9
votes
0answers
133 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 ...
8
votes
0answers
320 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 ...
7
votes
0answers
106 views

Does the projection of a PostGIS table affect the rendering speed in QGIS?

I am interested to know whether changing the projection of a PostGIS table will affect the rending speed when viewing it in QGIS. I have two layers coming from QGIS which are exactly the same and ...
7
votes
0answers
145 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....
7
votes
1answer
388 views

How to remove overlapping features by percentage?

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 ...
7
votes
1answer
615 views

PostGIS: How to distinguish overlapping routes (i.e., LineStrings) based on their direction?

I have a set of routes which are represented by LineStrings in my PostGIS database. These routes represent journeys that people make. I want to come up with routes that (a) overlap, and (b) have the ...
6
votes
0answers
62 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 ...
6
votes
1answer
627 views

PostGIS geography returns area < 0.0

I am trying to convert a geometry column to a geography column and calculate the area using this query: SELECT dccode,SUM(CAST(St_Area(geom::geography,true) AS NUMERIC)) as Area FROM schema....
6
votes
0answers
474 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 ...
6
votes
0answers
614 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 ...
6
votes
0answers
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 ...
6
votes
1answer
847 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 ...
6
votes
1answer
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....
5
votes
1answer
1k 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 ...
5
votes
1answer
118 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) ...
5
votes
0answers
142 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 ...
5
votes
0answers
232 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 ...
5
votes
0answers
112 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 ...
5
votes
0answers
553 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 ...
5
votes
1answer
636 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 ...
5
votes
0answers
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 ...
5
votes
0answers
423 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 ...
5
votes
1answer
125 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 ...
5
votes
0answers
345 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 ...
5
votes
0answers
107 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(...
5
votes
0answers
976 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 ...
5
votes
0answers
2k views

Hibernate Spatial - 'Invalid endian flag value encountered' Exception

I'm trying to run a simple query in Hibernate Spatial 4.0 on PostgreSQL 9.3. I have a number of objects in a table with latitude/longitude values, and I'm trying to query objects that fall within a ...
4
votes
1answer
156 views

Format of SQL for IDW with multiple Pokemon targets with PostGIS

I've had some experience with inverse distance weighted interpolation based on the help from this question Spatial interpolation in PostGIS without outputting Raster How can I write the SQL for ...
4
votes
0answers
35 views

Identify U curves in route

I'm trying to identify the mountain roads in a route, and I've thought that a good approach could be get the paths with a very closed curve (like a U turn), but now I can not find any documentation ...
4
votes
0answers
176 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....
4
votes
1answer
118 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 ...
4
votes
0answers
121 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 ...
4
votes
1answer
72 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 (...
4
votes
0answers
153 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 ...
4
votes
0answers
167 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 ...
4
votes
1answer
271 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 ...
4
votes
1answer
110 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. ...
4
votes
1answer
120 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). ...
4
votes
1answer
188 views

Query in QGIS is significantly slower, than in pgAdmin

So, I've a table in postgresql, that is joined to other spatial table. If I've run a simple query in pgAdmin, let's say SELECT * FROM table1 WHERE "area" > '5000' and it takes less, than 1s to ...
4
votes
1answer
74 views

Automatically exchange QGIS layer datasources during project opening?

Do you know of any existing QGIS plugin that automatically exchanges layer datasources during opening of a Project? I would like to transparently switch our users from "PostGIS A" server to "PostGIS ...
4
votes
0answers
182 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 ...
4
votes
0answers
88 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 ...
4
votes
1answer
225 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 ...
4
votes
1answer
743 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 ...
4
votes
0answers
662 views

Investigating PostGIS table not showing in QGIS

I created a table in Postgres: create table eric_zs as select z.id, z.name, st_multi(st_union(a.geom)) as geom, count(*) from geo_as a inner join geo_zs z on a."ZS_id" = z.id group by z.id, z....
4
votes
0answers
108 views

create custom SRID from OGCWKT

I wanted to create a new SRID for use in PostGIS. My goal is to have a SRS that can cover an entire state (instead of have multiple zones as in SPCS). For example, I wanted to create a new SRS for ...
4
votes
0answers
856 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 ...
4
votes
1answer
994 views

Clipping tiled raster with polygon using PostGIS?

I have a table in postgis with a tiled raster and a table with polygons overlaying the raster. I want to get the number of pixels for each category (pixel value) that fall inside a specific polygon. ...

1
2 3 4 5
32