Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
9,102
questions
3
votes
1
answer
39
views
QGIS: Why does this PostGIS materialized view show up as two separate layers?
I have a materialized view being created by this script:
WITH intersected AS (
SELECT source_layer.kwtid,
source_layer.geom,
source_layer.area,
source_layer....
- 31
4
votes
3
answers
71
views
Calculating minimum river width for a route within the river in PostGIS/Python
I have a complex ancient river system containing islands and multiple channels. I also have line segments defining a vessel's path along the river:
I use PostGIS to hold the data, the river system is ...
- 41
2
votes
1
answer
52
views
Slow query when combining st_buffer with st_contains in one query with subquery
I have this query, which is very slow (+50 seconds):
SELECT e.gid, e.geom, osm_id, name
FROM roads_vlaanderen_edges_2021_2022 e
inner join(select st_buffer(geom,30) geom from projecten where ...
- 21
0
votes
0
answers
24
views
Free and open source solutions for raster data management [closed]
What are recommended ways to manage raster geodata using free and open source software?
Let's say I have a bunch of raster data (mainly orthophotos as GeoTIFFs) on my system. I want to manage these in ...
- 115
3
votes
1
answer
64
views
How to select N points within each geometry
I have grid and point tables in my postgres db. The points are not equally distributed over the grid. The grid table is a 100k+ geometries and the points are in the 100's of millions so efficiency ...
- 1,388
2
votes
1
answer
30
views
Query to find all buildings in an area using PostGIS on OSM database
I have a PostGIS database filled using OSM data of a French region.
If I made no mistake, the zone I want to investigate is bounded to the zone of the data, so I should be able to extract it.
One of ...
- 31
1
vote
0
answers
18
views
Compile some C code to extend PostGIS extension on Windows
I have some C code that is used on a Ubuntu server to create some extension to PostGIS. Now I want to install those functions on my Windows server (Where I now want to run my PostgreSQL server from). ...
- 1,914
2
votes
1
answer
69
views
Polygon boundaries in PostGIS spatial predicates
I have a PostGIS enabled PostgreSQL database with 2 tables that contain polygon geometries called "developments" and "farm_portions".
A development is a collection of 1 or more ...
- 167
2
votes
1
answer
29
views
WFS in PostGIS OGR FDW results in "ERROR 1: No schema information loaded."
I am trying to add the WFS data of a german state to PostGIS using the PostgreSQL OGR Foreign Data Wrapper:
CREATE SERVER server_mv
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'WFS:https://www....
- 10.3k
2
votes
1
answer
24
views
Trouble calculating area consistently
I'm building a UI that lets users draw areas (polygons) on a MapboxGL element. I want to compare those areas to an existing data set I have in PostGIS, however, I cannot seem to get turf and PostGIS ...
- 121
1
vote
0
answers
18
views
Why are overviews of an out-db postgis raster not out-db?
I'm working on out-db postgis raster and have generated overviews, but why are the overviews generated of an out-db rasters stored in-db?
I have tried creating overviews both manually using ...
2
votes
1
answer
75
views
Match rows from different tables by comparing similar lat lon coordinates
I am new to PostgreSQL and PostGIS. The first project I am working on is the first I am struggeling with and the one I would like to discuss.
I have 2 stops tables I call for simplicity tbl_stops_1 ...
2
votes
0
answers
41
views
MultiPolygon - removing polygons intersections in PostGIS [closed]
Having a MultiPolygon, I would like to get a MultiPolygon where each polygon has no 'holes' and there are no polygons intersecting with each other.
How to achieve this in PostGIS?
To give you an ...
- 21
2
votes
1
answer
24
views
raster2pgsql excessive memory usage
I'm attempting to upload a raster to a PostgreSQL (PostGIS) database using raster2pgsql and I have noticed that it is using an excessive amount of RAM, over 25GB worth. The file I'm trying to upload ...
- 897
2
votes
0
answers
36
views
Projection is not found when importing PostgreSQL file with Geopandas
I need to import a couple of files from my PostgreSQL database, which has columns with the geometrical data stored in the CRS "epsg:1330". There are no problems with opening the files using ...
- 61
3
votes
1
answer
39
views
Break medial axis into a set of simple polylines using PostGIS
The medial axis for a polygon computed by ST_ApproximateMedialAxis() is usually a single MultiLineString with possibly many branches (see e.g. ST_ApproximateMedialAxis returns multilinestring instead ...
- 10.9k
3
votes
1
answer
61
views
Checking all existing SRIDs in a PostGIS database
I need to figure out if a particular SRID is used by any geometry in a database having plenty of different schemas, each of them having also plenty of tables, some having more than one geometry field.
...
- 9,363
4
votes
0
answers
89
views
Adding pictures taken by a mobile device to a WFS layer in QField
I have installed QField (2.7.6) on an Android mobile device. The QGIS project on the device is only using some WFS endpoints.
Those endpoints are served by a remote QGIS server installation which is ...
- 9,363
1
vote
0
answers
28
views
ST_Intersects failed with "ERROR: GetGenericCacheCollection: Could not find upper context"
When I run this query
SELECT ST_Intersects(ST_geomFROMTEXT('POLYGON((105.767292 35.923215,112.165502 37.269499,114.177842 33.956548,113.636058 30.975062,105.393203 30.531638,105.767292 35.923215))', ...
- 19
3
votes
0
answers
49
views
Find adjacent polygons and then sum values across all neighboring polygons [closed]
I'm working with a polygon layer that contains district level income data. For each individual district I would like to calculate the sum of the incomes from all neighboring districts. I found a post ...
- 921
2
votes
1
answer
30
views
PostGIS, creating spatial index on box2d column failes
I would like to store bounding boxes in a column of my table. Thus I wanted to use the box2d data-type for that column. However it seems like I can't create an index on that column. Here is my ...
- 133
1
vote
0
answers
40
views
DXF to PostGIS to OpenStreetMap: coordinates are still in AutoCAD Cartesian coordinate system, not lat/long
I'm trying to take a survey site created in AutoCAD Civil 3D and export the drawing to a DXF which I then load into postgres/postgis with GDAL ogr2ogr (https://gdal.org/programs/ogr2ogr.html).
I then ...
4
votes
1
answer
64
views
Removing duplicates geometry rows in PostGIS
I am maintaining a table which has geometry datatype and it's possible that the table can contain duplicate geometries.
If I want to remove duplicate rows or select only non-duplicate one, will both ...
- 75
3
votes
1
answer
40
views
Creating Linestring from GPX file with M coordinate as time
I have several GPX files that I'm trying to load into a PostGIS table, with the goal of map-matching them (I intend to use pgMapMatch but open to other services I can run on my own machine). ...
3
votes
1
answer
122
views
How to get minimum bounding box for a set of polygons using Postgis
I have a set of polygons in Postgres which I'd like to get the minimum bounding box for them:
Something like this:
I've tried ST_Union with ST_Envelope, however they yield something like this:
Any ...
2
votes
1
answer
41
views
Algorithms for finding nearest road
Having two LineStrings I want to know if they actually are the same gps path or not. I guess something like if the distance between these are lower than x for a few points I would assume that they are ...
- 131
3
votes
1
answer
29
views
Storing custom field for each Polygon within a MultiPolygon in a single PostGIS table
I have a PostgreSQL/PostGIS table containing MultiPolygons representing play areas. Each Multipolygon has several fields that are the same for all the Polygons that cointain each MultiPolygon (unique ...
- 3,728
2
votes
0
answers
53
views
Creating shortest path for a walk mode
I am new to postgis and pgrouting.
If you see the attached screenshot, I have loaded the osm data (currently I will be only talking about the walk mode).
Here we can see the two layers i.e. ...
- 121
2
votes
0
answers
18
views
Select one of polygons that intersect from a single table
I have imported points objects from dxf/dwg (exported from Inkscape) in QGis and although the stroke was set on 0, I get for each point 3 shapes made of lines. When I polygonize them with Postgis I ...
- 21
0
votes
0
answers
22
views
raster2pgsql unable to append raster to table
I was successful in loading a raster to a table "raster_files" in the "environment" schema in my gdb. However now I am trying to append the rest of the rasters to that same table.
...
- 1
2
votes
0
answers
61
views
Using UUID as feature ID for MVT tiles (PostGIS)
I have a PostgreSQL/PostGIS database with with rows identified by UUID.
I export those rows as vector tiles.
I want to identify the features within the tiles.
The documentation https://postgis.net/...
-2
votes
1
answer
37
views
PostGIS: ST_LineMerge can make LineString from MultiLineString [closed]
I am trying to convert MultiLineString to LineString with PostGIS:
This is my query:
SELECT (st_astext(ST_LineMerge( geometry ))) AS shape
FROM public."Cities"
where id = 364087;
but the ...
- 179
0
votes
0
answers
12
views
raster2pqsql error while importing raster
I am trying to import tif image into PostgreSQL using the raster2pgsql and I am getting this error
LINE 1: INSERT INTO "public"."test" ("rast") VALUES ('01000008000000...
...
- 9
2
votes
3
answers
47
views
Kosmtik: PostGIS Plugin: FATAL: Peer authentication failed for user
I like to test https://github.com/kosmtik/kosmtik.
I have set up a tile server with a small sample area following these instructions: https://switch2osm.org/serving-tiles/manually-building-a-tile-...
- 595
4
votes
1
answer
80
views
Getting the vertices of UTM grid in PostGIS
I've downloaded from the official repository the UTM 5x5 km grid of Spain and uploaded it to a PostgreSQL/PostGIS database (vv. 14 & 3.1.4).
I need to obtain the coordinates of every cell vertices,...
- 3,000
6
votes
1
answer
173
views
Wrong shortest path using pg routing
I want to find the shortest distance between two coordinates using pg routing. I am using Dijkstra algorithm but the shortest distance is incorrect. The correct value is approx. 6 km but I am getting ...
6
votes
1
answer
130
views
ogr2ogr CSV to PostGIS, cast columns as text
I'm trying to import a CSV file to PostGIS with a subset of columns and cast all columns to the correct data type:
C:\OSGeo4W\bin\ogr2ogr.exe -f "PostgreSQL" "PG: host=localhost user=...
- 63.8k
0
votes
1
answer
237
views
django.core.exceptions.ImproperlyConfigured: Could not find the GDAL library via Homebrew
Django 4.1.5 on M1 Ventura 13.3.1 (22E261) failing to detect GDAL installation even when enumerated per the documentation. Is this a version error or a pointer error? I don't really understand what ...
0
votes
1
answer
13
views
Set up a JNDI connection to handle SSL with verify-ca on GeoServer with bundled Jetty
I manage a GeoServer connected to PostGIS hosted on Google CloudSQL that must have SSL connections turned on. Since I cannot add client certificates to the native PostGIS connection on GeoServer, I ...
0
votes
0
answers
33
views
Geometry sent to PostGIS is null
I am writing simple PostGIS trigger function that takes NEW.the_geom value from GeoServer and puts it in a table. But the value in a table stays NULL.
I was wondering in what form GeoServer sends ...
0
votes
1
answer
42
views
Create Tiles for dynamically switch between simple Styles [closed]
My goal is to display a map that can dynamically switch between very simple styles that I will create myself. The styles should only differ in color and stroke width. If possible, I would like to use ...
- 3
1
vote
0
answers
55
views
Unable to load the FDO provider library Postgresqlprovider.dll on Autodesk Civil 3D
I created a PostGIS database on a server called "aisrv2" on the standard port 5432.
Connecting from QGIS the connection works using Host, Port and Databse name, so I wanted to do the same in ...
- 11
3
votes
1
answer
41
views
PostGIS ST_Relate reports polygons intersect but Google Maps says otherwise
I'm creating a Geography database that does not allow polygons to overlap/intersect (having positive common area that is). The user interaction flow is:
User creates and exports a KML document from ...
- 31
1
vote
0
answers
54
views
Import all nodes that don't have tags into a PostGIS database using osm2pgsql
I want to import an OSM file into my PostGIS database using osm2pqsql. My input .osm file has around 300,000 nodes, but the planet_osm_point table ends up with only around 50,000 records. On closer ...
2
votes
1
answer
47
views
Nodes returned by OSRM map match service not found in the PostGIS database
The call to OSRM map matching returns a list of nodes:
{"code":"Ok","matchings":[{"confidence":0.000854076,"geometry":{"coordinates":[[8....
- 235
0
votes
0
answers
53
views
Correctly merge and filter GPS coordinates from LINESTRING in R's sf package
I have an SFC object with 2508 features of LINESTRING geometry type in R's sf package.
When I extract the GPS coordinates and plot them, I notice some parts of the trace are connected in the wrong ...
- 131
0
votes
1
answer
47
views
Is there a way to get all the information back? I have no backup
By mistake I deleted the public schema and the geometry field was removed from the tables of the other schemas, now I can only see the table and not the vector information, the geom information has ...
2
votes
0
answers
31
views
PostGIS Topology with ArcGIS, is it possible? [closed]
I have been tasked with developing a specialised asset management system, based on PostgreSQL/PostGIS given that it will be multi-user reading and editing, that will be consumed by and manipulated ...
- 41
0
votes
1
answer
35
views
Geographic Component in PgAdmin 4
I have a college project involving GIS, but I am new to this field. I have created a database using PgAdmin 4 and have added alphanumeric data to it. Now I want to add a geographic component to a ...
- 1
0
votes
1
answer
37
views
Can't create GeoServer data store from RDS PostGIS
I'm trying to add a PostGIS data store to my Geoserver but it is returning the below error:
Error creating data store, check the parameters. Error message: Unable to obtain connection: Cannot create ...
- 23