Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgis]

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

10
votes
0answers
674 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
101 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
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
54 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
186 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 ...
6
votes
0answers
313 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
562 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 ...
6
votes
0answers
254 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
363 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
572 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
0answers
507 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
82 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
66 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
326 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
0answers
697 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
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
0answers
89 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
732 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
70 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
70 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
0answers
64 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
112 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 ...
4
votes
0answers
205 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
92 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
177 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
123 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
54 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
212 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
267 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
93 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
510 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
250 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
226 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: ...
4
votes
0answers
237 views

Import Large Shapefile to Postgres

I'm trying to import a large dataset to Postgres, of about 600MB, that sits on a remote AWS server. But I get the following error Not enough memory to allocate requested memory (nRecords=26923892). ...
4
votes
0answers
153 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 ...
4
votes
0answers
154 views

A* pgRouting issue with x1,y1 and x2,y2 values

I am trying to use the A* algorithm in a topology I have made in PostgreSQL. I can fill the x1,y1 and x2,y2 the latitude/longitude, but I have read that the same version has problems with ...
4
votes
0answers
2k views

Rotation by attribute in Geoserver SLD style

I have a table with field calculated by function: NEW."rotationAngle" := degrees( ST_Azimuth( ST_StartPoint(NEW.the_geom) ...
4
votes
0answers
429 views

How to set posgis default sequential value in a QGIS “editable” view?

Introduction In a QGIS project I have a VIEW, that I want to became editable (I'm trying to replicate the versioning system used DB Manager with some tweeks). Therefore I created a few rules to ...
4
votes
0answers
266 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 ...
4
votes
0answers
2k views

Clustering table on geohash index and geohash ordered table

I wonder if there could be a performance gain to re organize the rows in a table by Geohash and then CLUSTER this table on the Geohash index of the geometries. CREATE TEMPORARY TABLE tmptbl AS SELECT ...
4
votes
0answers
770 views

Extracting x,y,z values from a postgis linestring to python using ogr

I am trying to load a postgis linestring into python (using ST_DumpPoints) and extracting x,y,z coordinates, which is working but extremely slow..and if i mean slow, i really mean it (yes, it takes ...
4
votes
0answers
834 views

How to import PostGIS layer with geography type to QGIS?

I tried to import in QGIS a layer from a PostgreSQL table with a geography type column created through the extension PostGIS. I noticed that QGIS doesn't seem to like the geography column... Here ...
4
votes
0answers
911 views

How can I properly add PostGIS data into MapGuide Maestro?

I am having trouble connecting a PostGIS database to MapGuide Maestro. Actually, I have added the PostGIS data into Maestro, but when I click to validate the layer it shows an error message: ...
3
votes
0answers
103 views

Using related layers/tables in background?

I have postgresql/postgis database setup with one to many relationship. I can successfully set it up in QGIS but i would like to keep related tables hidden from Layers Panel to keep the project "clean"...
3
votes
0answers
53 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
votes
0answers
55 views

Dealing with bridges over roads in PostGIS topology?

first time posting please ask for more details if not enough provided. I am using OSM road data and have created postgres topology for them. I am now cleaning up the data before exporting it for ...