Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
9,041
questions
1
vote
2
answers
23
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 ...
- 21
1
vote
0
answers
17
views
We have lost permissions on the topology scheme
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
17
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
55
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 ...
- 585
3
votes
0
answers
60
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
0
answers
11
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 ...
4
votes
2
answers
92
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 ...
- 753
1
vote
0
answers
53
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
30
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
30
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
45
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 ...
- 487
0
votes
0
answers
50
views
Installing PostGIS for PostgreSQL 15 on Rocky Linux 9 [closed]
I installed PostgreSQL 15. After the installation, there's no option to install postgis extension. I tried to add PostGIS support in a database using CREATING EXTENSION and I got an error message ...
2
votes
1
answer
86
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 ...
- 585
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?
- 71
6
votes
1
answer
349
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
22
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
44
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
...
- 585
1
vote
1
answer
42
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 ...
- 449
1
vote
1
answer
56
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 ...
- 21
5
votes
1
answer
77
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
49
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 ...
- 412
3
votes
2
answers
135
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
7
votes
2
answers
148
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 ...
- 585
3
votes
1
answer
65
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,090
3
votes
1
answer
58
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 ...
- 599
1
vote
0
answers
36
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 ...
- 585
2
votes
1
answer
42
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 ...
- 585
2
votes
1
answer
46
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 ...
- 585
2
votes
1
answer
38
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 ...
- 53
0
votes
0
answers
35
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,535
4
votes
3
answers
259
views
How to check if two polygons have internal points in common, with geodjango and postgis
I am am using geodjango with postgis backend.
Giving 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 &...
- 163
2
votes
1
answer
35
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 ...
- 131
1
vote
1
answer
34
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,934
0
votes
0
answers
27
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
42
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
1
answer
55
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
64
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
2
votes
0
answers
36
views
How to check if blocks touch by walls and continuous?
I have a number of blocks that are lying next to each other in all directions.
So, the user can select individual and multiple blocks.
There is a "must" requirement to select multiple ...
- 41
3
votes
1
answer
85
views
Inserting polygon as GeoJSON in PostGIS with only boundry coordinates
I am trying to insert GeoJSON polygon features into a PostGIS DB. The input coordinate points are only the left-top corner and the bottom right corner. I have tried to use the ST_MakeEnvelope, but it ...
- 135
0
votes
0
answers
17
views
Error when opening GeoMondrian schema file and using MDX query in GeoMondrian Workbench
I'm having trouble using the GeoMondrian Workbench on my Ubuntu 18.04 LTS system. I've followed the installation instructions and have installed the following:
Oracle Java 8
PostgreSQL 9.5
PostGIS 2....
3
votes
0
answers
45
views
Make a QGIS project stored on postgresql read only for specific users
I would like to restrain the privileges of certain users of my QGIS project stored on my PostgreSQL database to certain users.
I would like to make it read-only for everyone but myself.
Other users ...
- 487
1
vote
1
answer
35
views
ST_Covers(envelope, point) yields different results for same point
Clearly missing a basic concept. Using ST_Covers, I'm getting "true" for a geography point that clearly exists outside of the polygon. Looking at a number of points, it appears latitudes are ...
- 121
1
vote
1
answer
59
views
Determine envelope of geometry for all zoom levels
I'm using PostGIS to serve Mapbox Vector Tiles on my NodeJS server. I'd like to speed up the process a bit by storing the x and y envelope for every zoom level. That way when a request is made it ...
- 113
1
vote
1
answer
62
views
Optimize summary stats PostGIS query for multiple polyline layers over a worldwide grid
I have two global PostGIS polyline layers, one of which is a subset of the other. The subset defines where there are issues with the system being mapped.
I am trying to map the likelihood of a ...
- 620
1
vote
1
answer
37
views
PostGIS concatenating tables with different SRIDs side-effects for area
I am quite new to Postgres/PostGIS and have an open question regarding 'mixing' tables with different SRIDs.
I know this is generally not recommended, but in my case, I will already be required to ...
- 21
2
votes
1
answer
47
views
How to DROP TABLE in PostGIS using PyQGIS
I want to drop a table from PostGIS using PyQGIS, but it doesn't seem to work even though SELECT statements work fine. This is what I currently have:
uri = QgsDataSourceUri()
uri.setConnection("...
1
vote
0
answers
30
views
ST_Contains returns false even when the point is inside polygon [closed]
I am trying to map a set of points to polygons if the polygons contain the points. However, there are some points that are not being mapped. One such point and the polygon are below in the code ...
- 11
0
votes
1
answer
41
views
ST_Boundary for geography polygon
I'm trying to calculate the intersection point between a segment and the exterior ring of a polygon. But I'm not sure how to do it correctly when dealing with geography types.
The segment is a line ...
0
votes
0
answers
12
views
GeoDjango: PROJ_DB version mismatch
PROJ and GDAL are having it out on my system; GeoDjango reports the following error message :
GDAL_ERROR 1: b'PROJ: proj_create_from_database: C:\\Program Files\\PostgreSQL\\14\\share\\contrib\\...
0
votes
0
answers
25
views
Best way to preserve area while converting curve polygon to polygon - PostGIS
I need to use ST_MakeValid for one of my queries. The issue is, I need to do this with both curve polygons and regular polygons. Since ST_MakeValid doesn't support curve polygons I need to convert the ...
- 19