Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
9,242
questions
2
votes
1
answer
54
views
Iterating over LineString and applying filter in PostGIS
I want to iterate over a LineString and apply a filter so that I create a new LineString with those points that succeed in the test. How do I do that?
My goal is to find some outliers that are too far ...
- 153
0
votes
2
answers
38
views
How to test if a PostGIS table is a raster table or vector table?
Given a postgres table, is there a reliable way to test if it is a raster table?
I am planning on importing many rasters into PostGIS and then using pgraster and geometry interoperability. As a first ...
- 11k
0
votes
1
answer
52
views
How to get the extent of a PostGIS raster table?
For vector data, it is possible to get the extent of an entire table using either ST_Extent or ST_EstimatedExtent.
How does one get the total extent of all rasters in a raster table?
- 11k
2
votes
0
answers
78
views
Intersecting multiple overlaping polygon tables in PostGIS
I have a seven linestring tables that I buffered to create polygons of each line network. I then successively intersected each table (Figure 1 below).
I use the following sql query, but it take 36 ...
- 781
1
vote
0
answers
86
views
Using a PostGIS Layer in QField
I am trying to use a PostGIS layer in QField and somehow, i can not manage it to work.
My goal is to collect pointdata via QField using a PostGIS-layer so all collected data are available in the ...
- 55
0
votes
0
answers
43
views
GeoServer 2.22.0 use JSON attribute into SLD Heatmap
I am trying to create a heatmap definition in SLD and as value I want to use a property inside a json attribute of the layer.
I have a PostGIS instance connected to the GeoServer, and the attribute ...
1
vote
0
answers
19
views
Mixed results of the intersection of a line with a polygon in PostGIS
I have a line network and a some buffers. I would like to obtain the value of the buffers that the line intersects; however, I am getting mixed results that I am unsure how to interpret and solve the ...
- 781
1
vote
1
answer
55
views
PostGIS ST_Intersects and ST_MakePoint
I have a table called constraints_layer and columns named pk_id, dataset, name, geom. Geom is polygons. Within my Leaflet map Java I have a function that triggers on mouse double click and sends an ...
- 63
0
votes
0
answers
104
views
When deploying a GeoServer via Docker the container can't see my data directory
Deploying a GeoServer via Docker (include data with PostGIS). Container can't see my data directory. I am use Ubuntu 20.04
I need the GeoServer through docker to show my data, which I copy to COPY ./...
0
votes
0
answers
33
views
PostGIS layer and SRS in GeoServer
I wanted to add layers in GeoServer via my PostGIS database, but I noticed that the native SRS is not recognized and that the declared SRS is EPSG:40400 (see screenshot).
Knowing that I can modify the ...
- 1
0
votes
1
answer
32
views
Lengthy PostGIS processing time of buffered polygon and line string intersection/difference
I have created a ring buffer of lines (streams). I then use ST_Intersect and ST_Difference/ST_Intersects to intersect a linestring (linestring_feature) with the ring buffer (MultiPolygon) and also ...
- 781
1
vote
0
answers
56
views
Discrepancy In PostGIS Transformation: 103761 to 900913 to 4326 vs 103761 to 4326
Overview
I'm using PostGIS 3.1 on PostgreSQL 12.14 and I'm facing an issue with GIS data transformation and I need some assistance in understanding the cause of a significant discrepancy between two ...
- 119
2
votes
2
answers
86
views
Undocumented PostGIS functions?
I'm migrating storage from ST_Geometry to PostGIS geometry and modifying some related SQL and Python. I noticed that the function ST_PolyFromWKB doesn't appear in the latest PostGIS documentation, but ...
- 133
0
votes
1
answer
32
views
Point data from PostgreSQL to GeoServer symbology
I'm publishing a point dataset from PostgreSQL to GeoServer. When I leave default symbology for this dataset, GeoServer works fine and I'm able to see the data in QGIS as WMTS. When I change ...
1
vote
1
answer
67
views
PostGIS Multi-Ring Buffers Around Lines
I would like to create a multi-ring buffer around a stream network and add a default floating value to each each buffer ring. I currently have the following SQL query:
CREATE TABLE public....
- 781
2
votes
2
answers
51
views
Calculating number of internal polygons with PostGIS
I have a scenario where I have a number of polygons, many of which have polygons wholly contained within them, or which overlap them. For each polygon, I wish to count all the contained and ...
- 345
1
vote
0
answers
36
views
QGIS fails to connect sslmode=verify-full consistently with Postgres DB
I made a PostGIS database (PostgreSQL v15) available for our team to share data as we explore building an application with API access. (Database has been secured with firewall filters that only allow ...
- 11
4
votes
1
answer
67
views
Deleting NULL geometries and duplicate nodes from table using PostGIS
I run the command to delete null geometry from a table:
delete from table where geom is null
And delete duplicate nodes by running the command:
update table set geom = ST_Multi(ST_Simplify(geom,0));
...
- 51
1
vote
0
answers
37
views
Error adding raster to PostGIS: relation "<schema>.<table>" does not exist
I just started using postgresql and PostGIS
This error doesn't seem to occur for anybody. I tried running the command to add a raster:
raster2pgsql -s 27700 -I -C -M -t 256x256 path/to/my/raster.tif -...
1
vote
1
answer
87
views
"Bad allocation" error when importing a large PBF file (OpenStreetMap) on PostgreSQL with osm2pgsql
I downloaded the 72 GB OpenStreetMap PBF file (planet-230619.osm.pbf) at this link https://planet.osm-hr.org/pbf/ and I am trying to import it into PostgreSQL + PostGIS with the library osm2pgsql ...
- 139
0
votes
0
answers
56
views
AddGeometryColumn returns error 'column does not exist'
I'm running PostGIS 3.3.2, and trying to create a new spatial table. I've created a table dialectpoints, which currently has nothing but an ID column, and am now trying to add geometry info to it ...
- 87
0
votes
1
answer
65
views
Geography type vs Geometry type for PostGIS intersections and other calculations
According to ChatGPT (which obviously could be very wrong) geometry type will always calculate based on Cartesian space even when the CRS is 4326 coordinates, whereas geography type will calculate ...
- 584
1
vote
1
answer
54
views
Can I use WGS 84 and geometry calculations to check if a point is inside a polygon?
I'm storing polygons in a PostGIS database. The shape of the polygons is stored in a field of type geometry(Polygon,4326).
From time to time, I need to check if a point (of type geometry(Point,4326)) ...
- 143
0
votes
0
answers
89
views
TypeError: Cannot set GeoPoint SpatialProxy (POINT) with value of type: <class 'dict'>
I am working with Django, Django Rest Framework, Django Rest Framework GIS and POSTgis database to create an endpoint that should upload a geografical point and all its related information. I have ...
- 185
1
vote
0
answers
50
views
ST_intersects returns different result for Geometry params vs Geography params
ST_Intersects returns true for geography parameters but it returns false for geometry params:
Following is the query for geometry params and it returns true which is incorrect.
select ST_Intersects (...
- 53
3
votes
1
answer
127
views
Buffer accuracy in virtual layer differs from accuracy in PostGIS view
I used virtual layer query to create a buffer for a point as this allowed me to transform its SRID using the PostGIS function ST_Transform, which is not possible with the ready-to-use Buffer algorithm ...
- 817
0
votes
0
answers
64
views
ogr2ogr PostgreSQL installation without PostgreSQL driver (Windows). Why and how to change path to OSGeo installation
I'm trying to import a shapefile into my PostGIS database with ogr2ogr on Windows.
Running the command ogr2ogr -f PostgreSQL PG:"dbname=database host=localhost user=postgres port=5432" ...
- 125
2
votes
1
answer
62
views
ST_Intersects with ST_collect
We found that ST_intersects (ST_collect(<collection of polygons>), <polygonK>) is slower than
ST_intersects (<polygon1 from collection>, <polygonK>)
union
ST_intersects (<...
- 53
2
votes
0
answers
52
views
Unexpected result from PostGIS "&&" operator
This PostGIS query is unexpectedly returning TRUE:
select ST_GeomFromText('LINESTRING(-60 -30, -60 -60)', 4326)::geography && ST_GeomFromText('LINESTRING(-30 -30, -30 -60)', 4326)::geography;
...
- 21
0
votes
1
answer
31
views
GeoTools error NumericConverterFactory and GeoServer 2.22.2 with Java 17
We've upgraded to GeoServer 2.22.2 (GeoTools 28.2, Java 17, Postgres 12.7 RDS, PostGIS 3.0.3, Tomcat 9.0.10). Everything runs well except that we get a GeoTools error for a WFS request with a Filter ...
0
votes
0
answers
37
views
Enabling outdb rasters in PostgreSQL
I have an AWS RDS server connected to PostgreSQL, and I also have some rasters in the cloud (s3 bucket). I want to remotely query them using outdb rasters but I'm not sure how to set ...
0
votes
1
answer
74
views
Exporting from PostGIS views to gpkg with GDAL (ogr2ogr)
I have successfully exported more than 11.000 PostGIS views to geopackages. I use the GDAL command line for this with ogr2ogr along with GNU parallel for parallel processing. However, for some reason, ...
- 805
2
votes
1
answer
44
views
How to only sum the bits of buildings that are actually in the given zone?
I have this query:
select sum(way_area) from planet_osm_polygon
where building<>'no' and building<>''
and way && st_transform(
st_setsrid(st_makebox2d(
...
- 103
2
votes
2
answers
138
views
Create and access a permanent connection in PostgreSQL using PyQGIS
I'm trying to create a automatic connection from a QGIS Plugin to a PostgreSQL database and do manipulations like importing and exporting from the schemas.
I've found that you can use ...
- 61
1
vote
1
answer
60
views
Why would && return true for non-intersecting bounding boxes?
select ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[37.75074414,-57.54978713],[37.748904483,-57.558230569],[37.744033941,-57.566318698],[37.736317969,-57....
- 53
1
vote
1
answer
47
views
How does && operator work with Point geometry as a parameter
I am new to PostGIS. I am trying to understand && operator. Documentation says
boolean &&( geometry A , geometry B );
The && operator returns TRUE if the 2D bounding box of ...
- 53
1
vote
0
answers
31
views
Intersect between PostGIS layer and ArcGIS/GeoServer service using ST_Intersection spatial SQL in PostGIS
Is it possible to make intersection - ST_Intersection (or other spatial operations) on-the-fly between PostGIS polygon layer stored in local/cloud PostGIS DB and ArcGIS Map Service polygon layer (with ...
- 141
6
votes
2
answers
200
views
Jitter sensitive points in a PostGIS view
I have sensitive location data that we want to visualize without giving away location. Clients wants points jittered everytime they are accessed. What I would like to do is store actual location, but ...
- 81
1
vote
1
answer
36
views
How to get entity coordinates that are located inside an OSM administrative boundary?
I downloaded the administrative boundary of Germany from a website as *.geojson file.
I imported this *.geojson file into PostgreSQL like this.
./apps/ogr2ogr -f "PostgreSQL" PG:"host=&...
2
votes
0
answers
29
views
Use ST_AsMVTGeom for create tile of shape crossing 180 meridian
I have a polygon that crosses the 180th meridian and a function in postgis that generates a vector tile.
SQL:
ST_ASMVTGEOM(ST_Transform(polygon, 3857), ST_MakeEnvelope(xmin, ymin, xmax, ymax, 3857), ...
- 21
1
vote
0
answers
22
views
pgr_withinPointsCost not calculating cost
I had the following SQL query working, but I can't seem to get it working again. In summary, I have a set of points along a stream network that I would like to know the distance of along the stream.
...
- 781
0
votes
0
answers
33
views
What kind of storage for my webgis app
I'm making a WebGIS app where users will be able to upload and visualize the data on a 3D map (with Cesium). It will be DEM, DSM raster files. For this webGIS app I have a Nodejs server for all the ...
2
votes
0
answers
24
views
Transpose rows into columns at the same time as finding the nearest point of a polygon using PostgreSQL
I want to create a materialized view from two tables using PostgreSQL/PostGIS. Both tables contain no NULL values.
schemaA.tableA: contains the boundaries of municipalities in the form of polygons, ...
- 630
3
votes
1
answer
99
views
Calculate the surface having a natural tag different from null
I have a PostGIS database filled with OSM data.
I would like to calculate the surface of impervious cover on a given subfraction of my database.
I have tried a more complex version of this query :
...
- 103
1
vote
1
answer
40
views
XYZ points for terrain models in different resolutions from a PostGIS server
I have a grid of points in a PostGIS DB. This grid is in the resolution 1m and its around 100.000 Points large. Getting these points from the db works for me.
For example like this:
SELECT *
FROM ...
- 111
2
votes
1
answer
28
views
Cannot add layers to map canvas from spatial queries against a PostGIS database connection in QGIS
I am having trouble adding the results of spatial queries against a PostGIS connection to the map canvas in QGIS.
GIVEN:
I am able to add the results of queries against my PostgreSQL/PostGIS ...
- 41
0
votes
0
answers
48
views
New error: could not form projection (PJ) from 'srid=3424' to 'srid=4326'
I am trying to convert a New Jersey sourced dataset to 4326 to append the records to another table. The data source is: https://hub.arcgis.com/datasets/njdep::dry-cleaners-of-new-jersey/about
I am ...
- 425
1
vote
1
answer
80
views
OpenLayers - Displaying Multipolygons with holes
So I've been using the Draw interaction in OpenLayers version 7.4.2 and also 16.15.1 to create mostly Polygons and Multipolygons. I have also used the library ol-ext and it's DrawHole interaction to ...
- 15
0
votes
1
answer
93
views
Grouping Polygons by Attributes and by Distance Using GeoPandas and networkx
I am working on creating a unique group ID for a parcel dataset I have containing 115k records. I have been attempting to do this with PostGIS (Grouping Parcels with an ID based on conditions, Query ...
- 27
0
votes
0
answers
65
views
Calculate area/length and buffer point using GDAL golang
Using GDAL calc area and perimeter
sr4326 := gdal.CreateSpatialReference("")
sr4326.FromEPSG(4326)
sr3857 := gdal.CreateSpatialReference("")
sr3857.FromEPSG(3857)
sr4326_to3857 := gdal....
