New answers tagged

2 votes

Create a raw SQL statement ready to be used in PostGIS from a geometry in QGIS

You can add a virtual field (type text) that gets the text representation of the geometry and ads the projection. Then you can copy/paste from the attribute table. 'st_setSRID(\'' || geom_to_wkt($...
JGH's user avatar
  • 41.6k
1 vote

Compare field attributes

If you are willing to try a very different approach to string matching, then you could use the fuzzywuzzy Python module, instructions are here. There are several Q&As on this site that discuss it,...
Hornbydd's user avatar
  • 43.2k
0 votes

SQL functions supported by ogr2ogr

For the non-geometry columns, one can refer to the list of SQLite functions: https://sqlite.org/lang_corefunc.html
zabop's user avatar
  • 1,810
1 vote
Accepted

How do I set the result of a SELECT query equal to a new column in a GPKG file using ogrinfo?

Using ogr2ogr sql0="ALTER TABLE example ADD COLUMN col1 TEXT;" sql1="UPDATE example SET col1 = CONCAT(col0, col0);" ogrinfo -sql "${sql0}" example.gpkg ogrinfo -sql &...
zabop's user avatar
  • 1,810
4 votes
Accepted

Joining two polygon tables based on conditions

Not 100% sure this is what you want, but generally non-intersections translate to anti-joins in SQL; there are two common syntaxes to avoid a Cartesian product, of which I would always prefer the (NOT)...
geozelot's user avatar
  • 29.9k
2 votes

How to inverse spatial query

Left join on st_intersects and select rows where the right table rows are NULL: select a.* from public.table_a as a left join public.table_b as b on st_intersects(a.geom, b.geom) where b.geom is null
BERA's user avatar
  • 71.2k
1 vote

How to inverse spatial query

This will do what you require and use spatial indexes as well: WITH transform_t2 AS (SELECT *, ST_transform("CTA_LOW_1111".the_geom, 4619) AS geom FROM "CTA_LOW_1111"), , ...
Cushen's user avatar
  • 2,918
3 votes
Accepted

ST_CLIP with one raster and one polygon

I have a raster table named nh, and a polygon table named polygon. I want to clip raster with rid=1 with polygon with id=1: CREATE TABLE test.clipped_raster AS SELECT 1 AS id, st_clip(r.rast, p.geom) ...
BERA's user avatar
  • 71.2k

Top 50 recent answers are included