A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [postgis]

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

1,293 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
1answer
353 views

How do I select the longest connected lines from PostGIS ST_ApproximateMedialAxis?

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
8
votes
0answers
129 views

Does PostGIS ConcaveHull exclude points/polygons in some cases, and buffer outward in others?

In trying to generate concave bounding polygons using PostGIS ConcaveHull, I am seeing what I think is strange behavior. When running with a 0.99 tolerance, the resulting polygon shrinks tight as ...
8
votes
0answers
1k 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 '...
7
votes
0answers
100 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 ...
6
votes
0answers
226 views

QGIS projects in PostgreSQL (pyQGIS - saving proj to DB)

I'm currently working on some code to automate the creation of a QGIS project from tables in a PostgreSQL DB. For info I'm working in QGIS 3.4. I'm almost there, but I'm stuck on one thing How do I ...
6
votes
0answers
176 views

Shp2pgsql encoding issue / error. Special latin /utf characters (áéíóú) are wrong

I am not able to load accented vowels coming from a Shapefile, to a PostGis geodatabase. This is my information: Operative System: Windows 10. PostgreSql Database: postgresql-10.5-2-windows-x64.exe. ...
6
votes
0answers
363 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
313 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 ...
6
votes
0answers
478 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
667 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
216 views

Tolerance in PostGIS

I am working with spatial data in land management using Oracle. The daily task is to perform spatial analysis: defining the mutual disposition of land plots borders, territorial areas etc. The ...
6
votes
0answers
539 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
2k views

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

I'm new to postgis and raster file format. i currently working on netcdf files and i want to import it into postgis through curl command line tool but its failed for me.so i jump on to GDAL v1.9.2 ...
5
votes
0answers
93 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.geom, b.geom) as ...
5
votes
1answer
153 views

Speeding up searches on layer with many attributes?

I'm using QGIS 3 with my data in a PostGIS server on the same local network. I want to quickly search for a feature based on a certain attribute or attributes of my layer. This layer has ±2 million ...
5
votes
0answers
154 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
1answer
450 views

Geoalchemy: insert 3D elements in PostGIS database

I have shp files that I would like to insert into a PostGIS database. I'm planning to use Geoalchemy2 and geopandas to do so. However, my data have the geometry types LINESTRING Z and POINT Z. As I ...
5
votes
0answers
400 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
452 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
1answer
253 views

how to save as output layer from QGIS processing algorithm to postgis database using pyqgis

I have two layers(layer1,layer2) in postgis database where I have connect in QGIS project. my database have specific precision. now I want to use from QGIS processing some algorithms like ...
5
votes
0answers
934 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
1answer
301 views

Sampling values from a .vrt raster using PostGIS points

I have about 1 mill 2D points in a table in a PostgreSQL database. I also have a DEM (Digital Elevation Model) in raster. It’s a lot of .tiff files, bound together by a .vrt file, so they function as ...
5
votes
0answers
275 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
1k views

Importing GPX data to PostGIS table

I'm implementing a recipe from Paolo Corti's PostGIS Cookbook (by the way, a great help!) in order to import track points from multiple GPX files into a PostGIS table in two steps (pages 87-88): 1 ...
5
votes
1answer
499 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 ...
5
votes
0answers
95 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
787 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
1k 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
43 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
48 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
114 views

Cleaning GPS tracks from large jump / outliers

Can someone please explain the steps to take in order to eliminate the parts of these linestrings which jump across large distances (not along with the road network)? They were created from GPS ...
4
votes
0answers
103 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
1answer
67 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)...
4
votes
0answers
65 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
2answers
60 views

osm2pgrouting creates erroneous roads

osm2pgrouting works without giving any errors but most of the roads have lost their geometry. Result is ; What I've done so far; OSM Data downloaded from http://download.geofabrik.de/ in pbf ...
4
votes
1answer
187 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
4
votes
0answers
245 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....
4
votes
0answers
90 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 ...
4
votes
0answers
332 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
1answer
522 views

Splitting polygon in equal parts based on polygon area?

So, I came across this relevant question, but I think my problem is a bit different. I have two tables i.e. polygon poly and points pts in my Postgres db. I am on Windows 10 (x64) based machine and ...
4
votes
0answers
493 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
0answers
94 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 ...
4
votes
0answers
219 views

Enforcing maximum distance with ST_ClusterWithin?

I have a set of location points and I want to find clusters within the set. This is fine. However, I don't want to get two points in the same cluster that are farther apart than the maximum distance, ...
4
votes
0answers
107 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?
4
votes
0answers
151 views

Problem with st_clusterWithin and conditions

I need help because I am always in front of a problem in PostgreSQL since several weeks: I have the following table (extract): Id for the identifier geom for the geometry of the polygon burndate ...
4
votes
0answers
61 views

Why do I get a memory allocation error when selecting rasters by ID in PostGIS?

The Problem I have about 256000 tiles in a table of GDEM v2 at 1 arcsecond resolution. I'm on an Ubuntu server running Postgres 9.5.5 and PostGIS 2.2. The table has a GIST index on the convex hull of ...
4
votes
0answers
279 views

Chinese postman problem

Is there any way to modify the Traveling Sales Person algorithm in pgrouting to Route inspection problem (Chinese postman problem)? I need Route inspection problem and it seems that pgrouting does not ...
4
votes
0answers
293 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 ...
4
votes
0answers
131 views

Resources for creating a matrix of relationships between street segments

We are looking at examining traffic in a city using data coded to TMCs (which are long directional links, primarily major arterials and highways, where each link is the length of multiple blocks). In ...
4
votes
0answers
107 views

Improve 3D bounding box query on large set of lines (Postgres 9.5, PostGIS 2.3)

Problem: We use Postgres 9.5 and Postgis 2.3 to store about 6 million lines/edges in a 3D space and use an axis aligned bounding box to find all intersecting (and included) lines. This works well, but ...