Questions tagged [postgis]

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

1,384 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
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
186 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
111 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 ...
6
votes
0answers
336 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
403 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
1answer
327 views

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 ...
6
votes
0answers
350 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
549 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
777 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
563 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
1answer
85 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
174 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
104 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
1answer
547 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
467 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
523 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
1k 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
303 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
1answer
606 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
101 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
842 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

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 ...
4
votes
0answers
82 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
117 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
0answers
584 views

How to setup QGIS Server with PostGIS?

I need setup QGIS Server + PostGIS (+ Lizmap) in Docker for our GIS users. I spent a lot of time reading documentation and searching for information on that matter but it's unclear for me as for ...
4
votes
1answer
73 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
65 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
0answers
814 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 ...
4
votes
0answers
133 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
69 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
85 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
0answers
373 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
110 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
1answer
574 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
488 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
619 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
237 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
111 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
170 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

Implementing PostgreSQL extension using PostGIS types?

I would like to create a new data type for PostgreSQL/PostGIS, but I have some difficulties. Here's what I've done so far: /* PostgreSQL */ #include <postgres.h> #include <liblwgeom.h> #...
4
votes
0answers
66 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
338 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
311 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
114 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
700 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
288 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
304 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). ...

1
2 3 4 5
28