95 votes

SQL query to have a complete geojson feature from PostGIS?

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: SELECT json_build_object( '...
dbaston's user avatar
  • 13.1k
75 votes

Creating geometry from lat/lon in table using PostGIS?

Newer versions of PostGIS allow the following, slightly more common, syntax: ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326); Then use ST_SetSrid and ST_MakePoint to populate the column: ...
anneb's user avatar
  • 1,371
19 votes
Accepted

How to UPDATE with LATERAL Nearest-Neighbour query?

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 ...
geozelot's user avatar
  • 30.3k
17 votes
Accepted

Spatialite spatial queries do not function on GeoPackage

The issue is with the way I am using Spatialite. After talking with the company behind the web service that provides these datasets (since I initially assumed it may have been an export bug), they ...
alphabetasoup's user avatar
17 votes
Accepted

Deleting small holes in polygons specifying the size with PostGIS

This is one of those processes which are simple in an iterative language, but trickier in set-oriented SQL. But the ST_DumpX set-returning functions in PostGIS and the array handling in Postgres make ...
dr_jts's user avatar
  • 5,068
16 votes

Getting list of distinct values from shapefile field using QGIS

QGIS now has a tool that will list unique values in one or more fields in a layer. "This algorithm generates a report with information about the unique values found in a given attribute (or attributes)...
David Bartecchi's user avatar
16 votes

raster2pgsql: Strange error when creating a table

From the PostGIS webpage : -- enable raster support (for 3+) CREATE EXTENSION postgis_raster;
Skippy le Grand Gourou's user avatar
16 votes

Creating spatial tables with PostGIS

In PostGIS 2.0+ you can create the geometry column directly using common data definition language. For example: -- points in geographic wgs84 coordinates (epsg:4326) create table mypoints (id serial,...
anneb's user avatar
  • 1,371
15 votes
Accepted

How to Fill blank Fields with other column QGIS

Welcome to the wonders of COALESCE. Try this: COALESCE( "pole_type_" , "pole_type" ) It will return the first non-null value in a list. If they are all null, it will return null.
Hayden Elza's user avatar
14 votes
Accepted

Creating Virtual layer in QGIS that shows only features in extent

The trick is to create a function that access QGIS graphical interface, and that is piped to the query of the virtual layer. Open a function editor (from anywhere, including from field calculator) ...
JGH's user avatar
  • 41.9k
14 votes
Accepted

PostGIS points along a line aren't actually falling on the line

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 ...
geozelot's user avatar
  • 30.3k
14 votes
Accepted

Removing overlaps and keeping highest priority polygon using PostGIS

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 ...
geozelot's user avatar
  • 30.3k
13 votes
Accepted

Getting the line middle point with SQL in QGIS

Let's assume there is a layer called "line" (blue), see image below. So, the vital thing, there must be a distinction between Centroid and Midpoint. There several articles available online, ...
Taras's user avatar
  • 32.9k
13 votes

Clustering points in PostGIS

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()...
geozelot's user avatar
  • 30.3k
12 votes
Accepted

Export geography field from SQL for use in GIS

You should be able to use STAsText: https://msdn.microsoft.com/en-us/library/bb933977.aspx So you could create a new table: CREATE TABLE wkt_export as SELECT *, RoadGeometry.STASText() as WKT FROM ...
HeikkiVesanto's user avatar
12 votes

How get intersection of polygons in the same table by PostGIS?

you can use ST_INTERSECTION using the source table twice. Make sure to avoid comparing the a polygon to itself. Also avoid comparing a pair of polygon twice. SELECT ST_INTERSECTION(a.geom, b.geom), '...
JGH's user avatar
  • 41.9k
12 votes
Accepted

How to create a classic sand snail using PostgreSQL/PostGIS?

Here's some simple SQL for a spiral: WITH spiralStep AS ( SELECT i, 80 AS circleSegs, -- Parameter: quantization of arc 1000 AS centerX, -- Parameter: center X ...
dr_jts's user avatar
  • 5,068
11 votes
Accepted

Automatically Snapping Points to Closest Part of Line

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.<...
geozelot's user avatar
  • 30.3k
11 votes
Accepted

In PostgreSQL, how to get the polygon id that "intersects the most" in case it intersects multiple polygons?

Good candidate for a lateral join. In a lateral join you can use the attributes from the joining table in determining the join criteria. So we are filtering the country table to select the one with ...
HeikkiVesanto's user avatar
11 votes
Accepted

Selecting multiple field values using range in QGIS

Use this expression: ("ALTURA_SOB" >=500 AND "ALTURA_SOB" <= 1200) OR ("ALTURA_SOB" >=2000 AND "ALTURA_SOB" <= 2500)
Noura's user avatar
  • 3,429
11 votes
Accepted

QGIS - Countif function

This expression gives you what you need: with_variable( 'ar', array(Attribute1, Attribute2, Attribute3, Attribute4), array_length(@ar) - array_count(@ar, 0) ) with_variable assigns the second ...
Kadir Şahbaz's user avatar
10 votes

SQL query to have a complete geojson feature from PostGIS?

@dbaston's answer has been modified lately by @John Powell aka Barça, and it produces invalid geojsons on my end. As modified, the aggregation on features returns each feature nested inside a json ...
jufaua's user avatar
  • 308
10 votes
Accepted

How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?

Try this: Download the PostGIS Addons from this link: https://github.com/pedrogit/postgisaddons Install by running the postgis_addons.sql file to get the ST_SplitAgg() function. Test by running the ...
Pierre Racine's user avatar
10 votes
Accepted

CREATE TABLE AS with geometry in SpatiaLite

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 ...
geozelot's user avatar
  • 30.3k
9 votes

SQL query to have a complete geojson feature from PostGIS?

Just a slight correction to dbaston's answer (I would comment but I don't have points) You need to cast ST_AsGeoJSON's output as json (the ::json thingie): SELECT json_build_object( 'type', '...
JavPL's user avatar
  • 91
9 votes

Bounding box for PostGIS table

Not enforcing any spatial aggregation should be way quicker: select min(ST_XMin(geom)) as _left, min(ST_YMin(geom)) as _bottom, max(ST_XMax(geom)) as _right, max(ST_YMax(geom)) as _top from x
mika666's user avatar
  • 91
9 votes

Bounding box for PostGIS table

Another possibility is to use the ST_Envelope function, which returns a geometry with SRID, ST_Envelope — Returns a geometry representing the bounding box of the supplied geometry , along with ...
tinlyx's user avatar
  • 11.1k
9 votes
Accepted

Delete from a table where the geometry intersects the geometry of another table?

You need to reference the second table with USING: DELETE FROM table_A AS a USING table_B AS b WHERE ST_Intersects(a.geom, b.geom); See the docs under 'Notes' for details.
geozelot's user avatar
  • 30.3k
9 votes
Accepted

Selecting all columns except geometry using Virtual Layer in QGIS

It does work as intended, and it is only a display issue. To make is work, just give a name to the buffered geometry and specify this name as being the spatial column. Otherwise, the Virtual Layer has ...
JGH's user avatar
  • 41.9k
9 votes
Accepted

QGIS query builder using QGIS variables

You need to use a QGIS Python Macro. QGIS Expression is not supported in the Query Builder. Go in Project properties then Macros, the code is under the screenshot : Python code to use, you need to ...
etrimaille's user avatar
  • 7,250

Only top scored, non community-wiki answers of a minimum length are eligible