How to use ogr2ogr -clipdstsql?

Question

This works (A),

$ ogrinfo -dialect SQLITE -sql
  "SELECT ST_Buffer(ST_GeomFromText('POLYGON ((120.81313 24.18337 ...
Layer name: SELECT
OGRFeature(SELECT):0
  POLYGON ((120.702569638856 24.2111342483025,120.696422835853 ...

And this also works too (B),

$ ogr2ogr zxl.0.lines.csv zxl.0.Q.csv -sql 'SELECT Name FROM "zxl.0.Q"'
--optfile zxl.gcp -lco GEOMETRY=AS_WKT -clipdst 'POLYGON ((120.8...

As last resort, using cut and paste, or Perl scripts, I can take the ogrinfo output of (A), trim everything except the POLYGON, and paste it into B.

But how can I instead combine (A) and (B) using e.g., -clipdstsql ?

Answer 1

I would write everything into the main SQL. I did not test with real data but at least this query does not throw errors

SELECT ST_Intersection (
geom,ST_Buffer(
ST_GeomFromText('POLYGON ((0 0,0 1,1 1,0 0))'),0.002))
as geom
from test;

The ogr2ogr command could look like

ogr2ogr zxl.0.lines.csv zxl.0.Q.csv -dialect SQLite -sql 'SELECT Name, ST_Intersection (geom,ST_Buffer(ST_GeomFromText('POLYGON ((0 0,0 1,1 1,0 0))'),0.002)) as geom FROM "zxl.0.Q"' ...

Answer 2

I found the key is to only go one step at a time.

$ ogr2ogr zxl.buf.csv :memory: -lco GEOMETRY=AS_WKT -sql \
"SELECT ST_Buffer(ST_GeomFromText('POLYGON ((...))'),-.001,1) AS WKT"
$ ogr2ogr zxl.0.lines.csv zxl.0.Q.csv -sql 'SELECT Name FROM "zxl.0.Q"' \
--optfile zxl.gcp -lco GEOMETRY=AS_WKT -clipdst zxl.buf.csv

I.e., we just stick with the proven working -clipdst, avoiding having to figure out if -clipdstsql actually works or not. (Too bad. Sorry, but we've got a job to do), at the small price of creating an additional intermediary file, zxl.buf.csv.