Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
9,233
questions
5
votes
1
answer
136
views
SRID value=0 in geometry_columns for a view that explicitly uses ST_SetSRID
PostgreSQL 15.2, PostGIS 3.3.2
I create a view which has geometry column composed of raw coordinates. I explicitly set the SRID. However, in geometry_columns view the SRID value is 0 for that view. ...
- 53
3
votes
1
answer
71
views
Leaflet draw shapes and get the overlapped/included zipcodes
I am new to the GIS world but I have experience in programming. I am trying to build an app where users draw circles on a leaflet map and it returns the zipcodes "touched", but I think the ...
- 31
2
votes
0
answers
103
views
Creating a set of grid polygons
Is there a way to get or generate a tile matrix set geometry grid in any format like shapefile, geopackage, postgis, ... ?
EDIT
I've tried to create a set of grid polygons with postgis to generate ...
- 73
1
vote
1
answer
335
views
PHP library to get center of the polygon center
I have the folloging example GeoJSON data stored in a PostgreSQL PostGIS database:
{"geoJSON":{"type":"Feature","properties":[],"geometry":{"type&...
- 123
4
votes
1
answer
106
views
How to replace polyline Z value equal to Nan is PostGIS?
In some polylines stored in PostGIS, I found Z values equal to NaN.
For example:
LINESTRING Z (30 10 0, 10 30 nan, 40 40 nan)
I want to replace the 'nan' with 0 to prevent subsequent errors.
- 387
3
votes
1
answer
71
views
pg_routing error when generating alpha shapes
When running pg_routing to generate catchment areas I got this error when running the 2 queries below:
SQL Error [XX000]: ERROR: parse error - invalid geometry
Hint: "se" <-- parse ...
- 286
0
votes
0
answers
18
views
How to add multiple shapefile into a single table in PostGIS? [duplicate]
How to add multiple same geometry shapefile (such as polygons) into a single table in PostGIS?
For example:
test_1 is a shapefile (polygon)
test_2 is a shapefile (polygon)
test_3 is a shapefile (...
- 55
2
votes
1
answer
126
views
Changes to spatial_ref_sys tables do not take affect
I made changes to the spatial_ref_sys table in my PostGIS database but using ST_SetSRID and ST_Transform doesn't seem to be affected by the changes.
For instance, I changed the proj4text because it ...
- 21
2
votes
1
answer
108
views
Select points within a buffer / radius of a point [duplicate]
When a user click on a map, I first want to get closest point (placename), then use that point to get number of points (schools) within 10km buffer / radius
...
$str = pg_query($conn, "SELECT ...
- 73
4
votes
1
answer
87
views
PostgreSQL query not working in QGIS Graphical Modeler
Edit: For further context, my table does have a unique id in the form of an integer. Through the database manager, the query executes successfully and I am able to load the query layer into QGIS. This ...
- 41
1
vote
0
answers
25
views
PostGIS: Export a buffer with shp2pgsql [closed]
I want to create a buffer around a line:
SELECT ST_Buffer(wkb_geometry, 50, 'endcap=flat join=round') FROM linien WHERE route = 'Rundweg'
and export it with shp2pgsql:
pgsql2shp -f <path to output ...
2
votes
0
answers
374
views
NetTopologySuite distance between polygon and point in meters
I want to get distance between polygon and point in meters. When I'm using Distance() method I get it in cartesian units. How can I get in meters? I tried looking for solution but I can get only how ...
- 21
4
votes
2
answers
69
views
PostGIS subquery error when trying to randomly distribute points in polygons
The following is some code from @geozelot that I'm repurposing to randomly distribute duplicate points inside the polygon they are contained within, however, I get the following subquery error:
ERROR:...
- 771
2
votes
0
answers
46
views
Coded Values/ Domain like with GeoServer and PostGIS
Coded Values in oralce SDE are exposed trough ArcGIS Server as Coded Values in the REST API.
This ArcGIS Server REST API can provide the coded values to a webmap client to create a form with a drop ...
- 1,094
3
votes
2
answers
311
views
Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?
As seen here, it is possible to use PyQGIS to load layers from PostgreSQL using an instance of QgsDataSourceUri(). You then set the connection and load a layer.
Loading PostGIS layer from SQL query ...
- 99
2
votes
0
answers
169
views
We have lost permissions on the topology schema
We have a PostgreSQL database with PostGIS, with the following versions:
PostgreSQL 10.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
...
0
votes
0
answers
46
views
PostGIS layer in ArcMap 10.7 - Can't open table - no OID recognized
I have this line feature class that FME extracts from the ESRI geodatabase in order to write (update periodically) in PostgreSQL (using PgAdmin 4). The original feature class has an objectId and it ...
- 9
3
votes
1
answer
63
views
Intersection of Linestring with Polygon causes column mismatch in PostGIS
I have the following sql code that intersects each linestring with a polygon. The problem is that when the linestring is wholly covered in the polygon the descriptor attribute of the polygon is put ...
- 771
3
votes
0
answers
87
views
Coordinates: optimizing a table that contains lng/lat coordinates for clustering (in PostGIS)
I have table in PostGIS where I store coordinates in the 4326 lng/lat SRID, to make it easy to store GPS coordinates collected from mobile devices.
I'm using the coordinates to query the table based ...
- 31
0
votes
1
answer
50
views
GeoWebCache in GeoServer with PostGIS datastore not working
I just install GeoServer 2.22.1 ->Add mapbox tile plugin->Create workspace->Create PostGIS datastore->add layer->Config Tile cache
Then I Seed cache layer from PostGIS data but have ...
5
votes
2
answers
113
views
Unmatched records missing from spatial left join
I have a polygons table and a points table.
I want to get for each polygon the number of points located in it and have zero for polygons with no points.
However with the following query I miss the ...
- 817
1
vote
0
answers
88
views
Optimising ST_Intersects query with geometry and point
I'm trying to optimise the following SQL query, which checks a list of areas to see if the user is within one of them, based on their lat/long. The database is Aurora Postgres, the areas table has one ...
- 111
1
vote
0
answers
45
views
Zonal statistics for very large point layer with buffer
I have a point layer and I need to calculate, for each point, the area of overlapping raster layer within 500m distance. This could be done fairly quick by generating a 500m-buffered vector layer from ...
- 11
2
votes
0
answers
71
views
PostGIS 3.3, ST_DWithin Query - "lwgeom_distance_spheroid returned negative!" by changing third arg? Postgres 11, Ubuntu 20.04
The unexpected error:
lwgeom_distance_spheroid returned negative!
By making a tiny change in the third argument (distance) to ST_DWithin, we get an error in one version but not another. In other words,...
- 21
2
votes
1
answer
62
views
Using a intersects spatial query as default value in PostGIS table
I have two PostgreSQL tables :
table_a (polygon) with field_1
table_b (polygon) with field_2
For field 2, I would like to get the corresponding value in field_2 of the polygons of table 2 inside or ...
- 517
2
votes
1
answer
104
views
Optimizing KNN query with multiple search conditions
I have two point tables in SRID 4326: Table 1. unjoined_pts & Table 2. midpts. I want to join the attributes from the unjoined_pts table to the closest midpts table based on a few conditions. The ...
- 771
0
votes
0
answers
20
views
Get Point coordinates on a Linestring in specific distance along the line - in PostGIS [duplicate]
I would like to get the coords of a point which lays on a linestring in a known distance from the line's starting point in PostGIS.
Any idea?
- 79
6
votes
1
answer
368
views
Documentation for <->
I found this question and am wondering, is it for PostgreSQL? Where’s the documentation for <->? Trying to find the fastest way to get the distance between a lat and long earth coordinates in ...
- 161
2
votes
0
answers
30
views
Versioned editing
Are there any solutions similar to Esri's Enterprise GeoDatabase versioned editing (and long transactions) solution for QGIS and PostGIS?
I've found QGIS plugins like pvVersion, gis_versioning, gitgig ...
- 439
1
vote
0
answers
50
views
PostGIS spatial join with many conditions [closed]
p1 Table:
geom
lic_li_no
descriptor
attribute1
attribute 2
.....
4326
123456
LSD-xx SEC-xx TWP-xxx RGE-xx MER-x
state 1
state2
stateN
p2 Table:
geom
lic_li_no
orig_licli
descriptor
ra
4326
123456
...
- 771
1
vote
1
answer
282
views
Convert SQLite3 Geometry BLOB to WKB for PostgreSQL
I have used iterdump() to create an output file from a SQLite database that includes SpatiaLite data. I need to convert the hex data to a geometry in PostgreSQL.
I have found here a summary of how the ...
- 469
1
vote
1
answer
120
views
PostGIS import error
I need to import some shapefiles into a database. PostGIS has successfully imported several small files.
But an error occurred with large files. It shows this error:
"Unable to convert data ...
- 53
5
votes
1
answer
175
views
Calculate distance between two consecutive points within the same group in PostgreSQL
I have a table with Point geometries representing GPS data of participants. I am calculating the distance between two consecutive points:
SELECT id, participant, ST_Distance(geom, lag(geom, 1) OVER (...
- 53
2
votes
1
answer
198
views
Leaflet.js transfer information of a point: latitude, longitude, feature properties and variables, to a function
I'm trying to extract some information from a L.geojson feature of the map, containing points, through a newly created button inside a BindPopup feature, pointing to a function. Such function then ...
- 486
3
votes
2
answers
205
views
GeoJSON from SQL View
I am trying to select database table features within a certain distance from a leafleat generated geosjon using an sql view in geoserver. I first used a GET function and a user here suggested POST. I ...
- 221
8
votes
2
answers
192
views
Randomly offset duplicate points along linestring X meters using PostGIS
The query below selects all duplicate points on a line, which overlay one another, then randomly assigns each point location within a certain radius. The last step assigns each point back on what I ...
- 771
3
votes
1
answer
134
views
ogr2ogr: Make polygon from points
I have a set of points in a csv file like this (ie. a square):
-77.63333333;-11.75
-77.63333333;-12.25
-77.3;-12.25
-77.3;-11.75
-77.63333333;-11.75
-77.63333333;-11.75
After getting rid of the semi-...
- 2,122
3
votes
1
answer
68
views
Getting timestamp of each time pings enter and leave a radius
I have written code which gives the last time when a ping is recieved within a radius of a point using the max function (in a given time window).
However what I want is getting the timestamp of each ...
- 789
1
vote
0
answers
37
views
Intersection of lines with polygons query returns 0 entries [closed]
I have the following query that intersects a linestring with a polygon, which used to return all the intersected and non-intersected lines, but the query returns zero entries successfully now. I'm at ...
- 771
2
votes
1
answer
72
views
Use of PostGIS ST_Intersection & ST_CoveredBy for line x polygon intersection
I am trying to improve the performance of a line/polygon intersection query where all intersected lines and 'CoveredBy' lines are produced in a table. Question: The improved version produces many ...
- 771
2
votes
1
answer
60
views
Splitting lines with polygons in PostGIS
I have a a large line network (white lines) with 33,000 features that I would like to intersect with each polygon grid cell (black lines) and join the 'description' attribute of the polygon cells to ...
- 771
2
votes
1
answer
49
views
PostGIS query on having their centroid in the source layer feature
I am trying to port the ArcMap query by location ("Have their centroid in the source layer feature") to PostGIS. The aim is to select all feature that are completely within and touches the ...
- 73
0
votes
0
answers
130
views
What are the postgis-3 other postgis_*-3 extensions?
The PostGIS Installation instructions show using CREATE EXTENSION to create PostGIS and several related extensions (see below).
After recently installing PostgreSQL 14.6 on Ubuntu,¹ I went to create ...
- 4,595
5
votes
3
answers
401
views
Checking if two polygons have internal points in common with GeoDjango and PostGIS
I am using GeoDjango with the PostGIS backend. Given two polygons I want to check if they overlap. I mean, they have interior points in common.
If we check
A.function(B)
In the following picture &...
- 185
2
votes
1
answer
53
views
PostGIS - Reading geometry from a shapefile directly in the SQL query
Is it possible to use a geometry or other atributes from a Shapefile directly in a PostGIS SQL query, without the need of manually importing the Shapefile to the geodatabase first?
I imagine something ...
- 141
1
vote
1
answer
83
views
Error inserting data into PostGIS/PostgreSQL database through postgres_fdw Foreign Data Wrapper
I'm trying to insert data (a line geometry) into a foreign table which was mapped from a view in a PostGIS/PostgreSQL database. That view in turn, check if the the line is inside a polygon before ...
- 1,974
0
votes
0
answers
47
views
Transform raster of unknown SRID using proj4 in PostGIS
In PostGIS is there any way to transform raster of unknown SRID using proj4 to EPSG:3857 without dumping polygons?
1
vote
1
answer
56
views
Create half ellipsoid on Postgis
I have a positions of half ellipsoid, these numbers represent coordinates of ellipsoid:
But when I try to save this coordinates, it throws an error like:
Geometry has Z dimension but column does not
...
- 11
1
vote
2
answers
400
views
How do I solve "django.db.utils.OperationalError: near "CASCADE": syntax error" in GeoNode install?
I have followed the Advanced Installation instructions for GeoNode Project, but at the Run GeoNode Project for the first time in DEBUG Mode step, it says to execute:
# Prepare the GeoNode Spatialite ...
2
votes
1
answer
99
views
PostGIS ST_PointInsideCircle radius units config
I have a table with a lat and a long float columns and I'm trying to use the ST_PointInsideCircle
PostGIS method which is working but the radius I enter is not in the correct units.
I'm trying to ...
- 21