11
votes
Accepted
Converting to EPSG code gives error
PostGIS does not know EPSG 9234.
You can check this with:
select * from spatial_ref_sys where srid = 9234;
But EPSG.io provides handy code snippets to add it https://epsg.io/9234
Just run:
INSERT ...
- 16.1k
9
votes
Accepted
Randomly offset duplicate points along linestring X meters using PostGIS
Update duplicates with a randomly interpolated point on the nearest line, e.g.:
UPDATE
<points> AS pt
SET
geom = (
SELECT
ST_LineInterpolatePoint(
ln.geom,
-- ...
- 28k
9
votes
Randomly offset duplicate points along linestring X meters using PostGIS
Group by geometry to find the duplicates, join the line to the points, interpolate the duplicates at a random distance along the line, union with the non duplicates:
--Find the duplicates by grouping ...
- 63.8k
9
votes
Documentation for <->
You'll find an exhaustive overview of supported operators in the PostGIS Function reference under Operators.
The more or less canonically named spatial (K)NN operator <-> is documented under ...
- 28k
9
votes
Wrong shortest path using pg routing
The two lookup points you pass into the (K)NN sub-queries can't be referenced in EPSG:3857 - their coordinate values seem to be in degree and are likely EPSG:4326!
Assuming planet_osm_roads.way has ...
- 28k
8
votes
Accepted
How to check if list of polygons intersects with each other?
If the rectangles are all edge-adjacent, then their union will be a single polygon. If not, it will be a MultiPolygon with more than one element. So,
union the rectangles, and then test if ...
- 4,451
8
votes
Is EPSG defined separately for each geometry column?
PostGIS keeps its spatial reference system info in a table called spatial_ref_sys which you can query and is created in the source code with several megs of SQL that starts like this:
BEGIN;
INSERT ...
- 59.3k
8
votes
Accepted
Unmatched records missing from spatial left join
Try moving the where clause to a join condition:
select plg.id,p lg.geom, count(pnt.id) as num_pnt
from polygons plg
left join points pnt
on st_contains(plg.geom,st_transform(pnt.geom,3003)) and pnt....
- 63.8k
8
votes
Accepted
ogr2ogr CSV to PostGIS, cast columns as text
You are trying to cast into "char" that is not known by GDAL.
If the source data are in CSV format then the default SQL dialect is OGR SQL, and by the documentation https://gdal.org/user/...
- 60.3k
7
votes
Accepted
PostGIS Maximum Inscribed Circle
An elegant way to expand and access SETOF records (i.e. from Table Functions) is a LATERAL join:
SELECT
mic.radius,
mic.center, --already a GEOMETRY(POINT) type
mic.nearest --already a ...
- 28k
7
votes
Accepted
Classifying lines based on number of points located on them using PostGIS
Contrary to intuition, deriving a Point from a Line does not guarantee that it will be included in the vector that defines said Line - this is mostly due to floating point arithmetic and its ...
- 28k
7
votes
Simplify multipolygon removing small gaps in PostGIS
In the upcoming PostGIS 3.3 ST_ConcaveHull has been enhanced so that it respects boundaries when polygons are provided as input. So for this case you should be able to simply create a concave hull ...
- 4,451
7
votes
Simplify multipolygon removing small gaps in PostGIS
Join the layer to itself by distance, snap and union:
WITH clusters
AS(select st_clusterdbscan(geom, 0, 2) over() cluster_id, geom
from
(select a.id as aid, case when a.geom is not null and ...
- 63.8k
7
votes
Accepted
ST_Difference(A, B) returning A when A completely contained in B
This is almost certainly due to a bug in GEOS 3.9.0. The bug is fixed in GEOS 3.9.1. See this recent GEOS bug report for more details.
- 4,451
7
votes
Using PostGIS to break up large polygon into smaller segments
ST_Subdivide is a good option.
https://postgis.net/docs/ST_Subdivide.html
ST_Subdivide(geometry geom, integer max_vertices=256);
It works off vertices. So, no output polygon will have more than the ...
- 16.1k
7
votes
Accepted
SQL PostgreSQL add attribute from polygon to all points inside polygon but keep all points not just those that fall inside polygon
You are CROSS JOINing both tables on an exclusive condition - and the result set contains only pairs that satisfy this condition.
I urge you to adapt the explicit JOIN syntax; it will help you ...
- 28k
7
votes
Accepted
Remove a set of grids taking into account its overall geometry
Dissolve (with keep disjoint feature separate)
Buffer with a negative distance. My grid size is 2000*2000 m and i buffer with -1400
Extracy by location the input grid cells intersecting the buffer
- 63.8k
7
votes
Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?
Try this. I have a connection called localhost data so I search for the word data in all the PostgreSQL connections:
from PyQt5.QtCore import QSettings
qs = QSettings()
L = []
for k in sorted(qs....
- 63.8k
7
votes
Accepted
How to get minimum bounding box for a set of polygons using Postgis
A bounding box is usually defined to be aligned to the axes of the coordinate system. That is what ST_Envelope creates.
To get the geometry you drew, you can use ST_OrientedEnvelope instead.
- 10.3k
6
votes
Order of latitude and longitude in EPSG:4326
Note EPSG.io despite its name is not the official registry of EPSG. The official EPSG Geodetic Parameter Dataset is https://epsg.org/home.html.
The CRS EPSG:4326 is (and always has been) defined as ...
- 13k
6
votes
Accepted
Splitting lines by multiple polygons, taking length of particular segment, using PostGIS
The query below will:
1 - Intersect the lines and polygons,
2 - extract the startpoints of each intersect output line geometry,
3 - Use the startpoints as input to ST_LineLocatePoint to get a number ...
- 63.8k
6
votes
PostGIS query with ST_Intersects/ST_Contains is incredibly slow
The query uses
where ST_Intersects(ST_BUFFER(..),ST_SetSRID(ST_FlipCoordinates(shape), 4326))
and the index uses CREATE INDEX idx_roads on roadstable USING gist(shape);
The index will not be used, as ...
- 38.4k
6
votes
Accepted
Is EPSG defined separately for each geometry column?
I don't really understand your question, EPSG is the name of a catalog of projection, that assign a number for each projection, to simplify the handling of them. The SRID is the parameter that contain ...
- 2,377
6
votes
Accepted
Is "order by" clause in DBSCAN necessary?
It's not necessary to obtain a correct result. But because DBSCAN may have multiple valid solutions for a given set of inputs, the ordering may be necessary to obtain a stable result (i.e., the same ...
- 12.8k
6
votes
Accepted
PostgreSQL not taking full advantage of all computing resources
PostgreSQL is single-threaded by design - and necessity; RDBS aim to be ACID compliant, and cross-thread transaction safety is neigh impossible to implement¹.
Starting with PostgreSQL 10, the planner ...
- 28k
6
votes
ST_Buffer giving inaccurate results - but why?
Buffers are a bad idea in 99% of times you are thinking of them. The reasons are plentiful, but here specifically it is the fact that their shapes are
limited due to segmentation
only approximated by ...
- 28k
6
votes
Accepted
ST_Intersection EMPTY return type differs from version 3.0.7 to version 3.3.2
Both queries are returning the empty set because there is no intersection of geometries, meaning no element of the table intersects with the envelope. There is, however, an element whose bounding box ...
- 842
6
votes
PostGIS: relational value for spatial intersection
You have to create a trigger.
Test and trigger creation below :
-- DROP TABLE IF EXISTS public.nomen;
-- DROP TABLE IF EXISTS public.nomenclature;
CREATE TABLE public.nomen (nomen SMALLINT, geom ...
- 15.1k
6
votes
Accepted
PostGIS Voronoi not getting properly cut by cookie cutter polygon
From the documentation of ST_VoronoiPolygons https://postgis.net/docs/ST_VoronoiPolygons.html
Optional parameters:
'tolerance' : The distance within which vertices will be considered
equivalent. ...
- 60.3k
6
votes
Inserting polygon as GeoJSON in PostGIS with only boundry coordinates
PostGIS has no concept of GeoJSON - except for the I/O functionality, which translates between the internal and the GeoJSON representation of geometric features. Likewise does your exemplary input not ...
- 28k
Only top scored, non community-wiki answers of a minimum length are eligible