geozelot
  • Member for 4 years, 10 months
  • Last seen this week
Is the Geometry(4326) an alias for Geography datatype?
Accepted answer
19 votes

tl;dr: No. But see the update at the end. GEOMETRY and GEOGRAPHY are different PostgreSQL TYPES; exposed as higher level (SQL), user-defined composite types to the psql environment, but implemented ...

View answer
GeoPackage in QGIS: what are .gpkg-shm and .gpkg-wal files?
Accepted answer
16 votes

As the SQLite core of a GeoPackage is a single-file based RDB, it needs to utilize temporary support structures to guarantee atomic transaction management. SQLite supports different mechanics to ...

View answer
Why some CRS are deprecated in QGIS?
14 votes

In this case because of a Change of CRS name to indicate longitude of origin. As stated in the link you provided, this is a registry decision (see @user30184's answer) rather than a software ...

View answer
How to UPDATE with LATERAL Nearest-Neighbour query?
Accepted answer
14 votes

No need for JOIN LATERAL (or do you really just want to use it?); an UPDATE will pass each processing row to the following query, which is the same concept as using a JOIN LATERAL.[*]Try UPDATE gps ...

View answer
PostGIS points along a line aren't actually falling on the line
Accepted answer
12 votes

Better look at using ST_LineSubstring; no issues with coordinate precision, but somewhat tricky to set up... Edit: I then packed it into a more sophisticated set of functions a while ago; there's a C ...

View answer
Finding nearest neighbor for each point in the same table
12 votes

This is essentially a duplicate question of multiple others, with the sole difference being a table self-join. However, all queries currently present in this post have delicate CRS misunderstandings, ...

View answer
Clustering points in PostGIS
12 votes

I'd suggest to use the ST_ClusterDBSCAN Window function rather than the Aggregate function ST_ClusterWithin: SELECT *, ST_ClusterDBSCAN(the_geom, eps := <distance>, minpoints := 1) OVER()...

View answer
When to use GiST and when to use SP-GiST index?
Accepted answer
11 votes

An exact answer is not possible without testing on your actual data, as this is highly dependent on the data size, structure, corresponding PostgreSQL internals, and the exact queries you intent to ...

View answer
Removing overlaps and keeping highest priority polygon using PostGIS
Accepted answer
11 votes

Fortunately, with the prio dependency, this is easier to solve than a purely recursive difference aggregation. I'd prefer the LATERAL statement (here in full verbose mode), both for clarity of the ...

View answer
QGIS stopped working after updating it
Accepted answer
10 votes

This is adressed in QGIS issue #44293 and will be fixed with the 3.20.2 minor release is getting fixed live: Update as per comments: Windows builds have been fixed The suggested hotfix is to delete ...

View answer
Best practice for counting polygons in polygons
Accepted answer
10 votes

True Polygon containment is a costly operation; not only does a containment check have to run intersection computations between each pair of vertices in one polygon for each pair of vertices in the ...

View answer
Select polygons with 5 or more points
10 votes

The equivalent SQL (PostgreSQL/PostGIS & SpatiaLite [i.e. QGIS Virtual Layers]) would be: SELECT a.<id> a.<geom> FROM poly AS a JOIN pts AS b ON ST_Intersects(a.&...

View answer
SRID and Name relationship
Accepted answer
10 votes

If you want/need to have more info from within PostGIS, running something like SELECT srid, left(split_part(srtext, '"', 1), -1) AS "type", split_part(srtext, '"', 2) AS "name", ...

View answer
Automatically Snapping Points to Closest Part of Line
Accepted answer
10 votes

To permanently update the point geometries on the database level, run UPDATE <point_table> AS pt SET <geometry_column> = ( SELECT ST_ClosestPoint(ln.<geometry_column>, pt.<...

View answer
Making multipoint column with at least 2 points
Accepted answer
9 votes

Try CREATE TABLE my_table ( id GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, geom GEOMETRY(MULTIPOINTZ, 4326), CHECK (ST_NumGeometries(geom) BETWEEN 2 AND 3) ); ST_NumGeometries behaves ...

View answer
Disjoint is very slow in Virtual Layer but fast on symbology
9 votes

The issue is the cross join on non-intersection. Your query reads: for every row in g, return every row in s that does not intersect with the current g which seems in fact what you want, but about ...

View answer
QGIS categorized styling: show number of objects for a categroy
Accepted answer
9 votes

Once your classes are applied, you can activate <layer context menu> | Show feature count in the main Layer panel to see the feature count for each class:

View answer
Transforming all polygons into circles in QGIS
Accepted answer
8 votes

To get circles with equal area, run Geometry by expression with: make_circle( point_on_surface( $geometry ), -- centroid( $geometry ) sqrt( $area / pi() ) ) Here, the resulting circle ...

View answer
Invert the ST_DWithin() function
Accepted answer
8 votes

Use a correlated [NOT] EXISTS filter: SELECT * FROM foo WHERE NOT EXISTS ( SELECT 1 FROM bar WHERE ST_DWithin(bar.geom, foo.geom, <dist>) );

View answer
Reprojecting in PostGIS not working as expected
Accepted answer
8 votes

UpdateGeometrySRID only ever sets the SRID identifier (and changes the typemod accoridngly), it does not reproject! This scenario is covered by the docs: If you got the projection wrong (or brought ...

View answer
Computing concave hull from points using PostGIS and display in QGIS
Accepted answer
8 votes

The convex hull algorithm is somewhat delicate, and ST_ConvexHull makes no effort to preserve and validate topology and geometry - it is possible that parts of the constructed hull collapses into ...

View answer
Generate a geometry column from latitude and longitude columns with SQL
8 votes

Your statement looks like a random chain of SQL related commands, or a MySQL/MS SQL Server command (see @ConcreteGannet's answer), so in accordance with your questions tags, the actual solution to ...

View answer
CREATE TABLE AS with geometry in SpatiaLite
Accepted answer
8 votes

SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic ...

View answer
Splitting Lines with Points using PostGIS
Accepted answer
8 votes

The core issue with your query is that you split every line by every point individually, as a result of the table join; if one line is to be split by two points, that same original line will be split ...

View answer
Measuring / calculating curvature of linestring using QGIS?
8 votes

Some of many (somewhat hacky) solutions using the field calculator: select to add a field with type BOOLEAN use one of the expressions (returning true when line is curved) area( convex_hull( $...

View answer
Merge all shp files in a folder into one with a new field populated with the source filename
8 votes

I'd suggest you rather go with ogr2ogr in a terminal script directly. In summary (using the syntax from the linked post), to merge all .shp into merged.shp (both in CWD), with the filename (without ...

View answer
Trigger PostGIS QGIS
8 votes

Don't UPDATE the tables, simply set the value of the NEW record; the NEW (and OLD in UPDATES) record is passed to the function for each row and will hold the full set of values that is adressed by the ...

View answer
Replacing '+' with ', ' for labels in QGIS?
8 votes

Or, simply use replace("zone_nr", '+', ', ') as this doesn't expect a regexp term (it simply matches all occurences of the specified input string).

View answer
Filtering number of features in GeoServer using SLD?
Accepted answer
8 votes

Personally, I would try to separate data store access/publishing from layer styling. You could create a SQL View for the datastore, effectively adding a new layer with the query result table. The ...

View answer
PostGIS ST_Contains / ST_Intersect with one point envelope
Accepted answer
7 votes

The issue you are facing is geometric validity: the resulting Polygon geometry of an ST_MakeEnvelope of a Point (i.e. when used with the same coordinates) is invalid, and all relationship functions ...

View answer
1
2 3 4 5
16