Questions tagged [postgis]

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

1,225 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
0answers
702 views

Simplify the straight sections of (multi)Linestring in PostgreSQL/PostGIS leaving the curved sections untouched

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGIS. The (multi)linestrings ...
7
votes
0answers
205 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 ...
7
votes
0answers
103 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 ...
7
votes
2answers
696 views

Using QField with Multi-User Access also Offline?

I got 10-20 QGIS-Users which edit all one Project online and offline. Sometimes they do it at the same time. Until now we only used QGisDesktop with a PostGis Database stored in QGIS Cloud(50MB ...
7
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 '...
6
votes
0answers
69 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
329 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
290 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
421 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
615 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
862 views

Collapse dual carriageway lines in PostGIS

I have OpenStreetMap data, which represents larger roads (e.g. dual carriageways, aka divided highways) as parallel lines. The question is answered for ArcGIS or Skeletron here, for example using ...
6
votes
0answers
520 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
1k 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
89 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
0answers
97 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. ...
5
votes
0answers
133 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
387 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
360 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
407 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
226 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
774 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
269 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
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
441 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
91 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
1answer
182 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 ...
5
votes
0answers
754 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
0answers
86 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
64 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
109 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 ...
4
votes
1answer
77 views

Searches on layers with many attributes are slow despite having indexes on the attributes I want to search. How can I speed this up?

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

Add Line Layer from PostGIS to Leaflet

I have a line layer in PostGIS that I would like to add to a Leaflet map. I have the PHP setup, but I can't seem to get the Javascript correct. I know my issue is converting the line to geoJSON. The ...
4
votes
1answer
155 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
170 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
74 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
233 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
398 views

How do I split 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
93 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
199 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
102 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
138 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
58 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
240 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
279 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
130 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
96 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 ...
4
votes
0answers
540 views

Query a different density of points based on zoom level in PostGIS

I have a database (using PostGIS) of a few million points that I load onto an interactive map. Obviously the map can't handle this many points, so I currently restrict it by limiting to the points ...
4
votes
0answers
255 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 ...
4
votes
0answers
238 views

Counting nodata and data pixels using PostGIS

I'm using PostgreSQL 9.2.4 and PostGIS 2.0.3. I have some raster layer and I'm trying to get the count of all pixels (including nodata), cliped by polygon. Raster was imported by raster2sql: ...