3

I have a command

ogr2ogr -f sqlite -dsco SPATIALITE=YES " !outfile!.sqlite !infile1! -nln !outfile! -dialect sqlite -sql "SELECT ST_Intersection(A.geometry, B.geometry) AS geometry, A.name, B.name FROM !infile2! A, !infile3! B WHERE ST_Intersects(A.geometry, B.geometry)"

where infile2 and infile3 are set as the SQLite input table name variables which change with each iteration of the loop.

I am using this in ogr2ogr at command line as part of a windows batch file. From previous investigation I find that the prior loop that sets variables infile and outfile works fine with ogr2ogr outside of the sql statement (hence not displayed here). However I am having trouble finding the syntax for within the sql statement, in order to call the table name variables from the loop each time it iterates. I have experimented with "", '', %%, $ and all manner of combinations.


After afalciano's answer i found that simply inserting !infile!.!infile2! etc didn't work so I have amended my code as follows (this time including a revised loop. This still does not work.

for %%i in (*inputfile.sqlite) do (

SET "infile=%%i"
SET "infile1=!infile:inputfile.sqlite=inputtable!"
SET "infile2=!infile1:inputtable=inputfile.sqlite.!infile1!!"
SET "infile3=!infile2:inputfile.sqlite.!infile1!=inputfile.sqlite.!infile1!2!"
SET "outfile=!infile3:inputfile.sqlite.!infile1!2=outputtable!"

ogr2ogr -f sqlite -dsco SPATIALITE=YES " !outfile!.sqlite !infile! -nln !outfile! -dialect sqlite -sql "SELECT ST_Intersection(A.geometry, B.geometry) AS geometry, A.name, B.name FROM !infile2! A, !infile3! B WHERE ST_Intersects(A.geometry, B.geometry)"
)
  • !infile2! and !infile3! after FROM should be in the "other_datasource_name"."layer_name" form, where "other_datasource_name" is the sqlite filename and "layer_name" the table/layer. – Antonio Falciano Apr 13 '17 at 17:21
2

If infile* are SpatiaLite datasources and intable* the table names, try with:

ogr2ogr -f sqlite -dsco SPATIALITE=YES " !outfile!.sqlite !infile1! -nln !outfile! -dialect sqlite -sql "SELECT ST_Intersection(A.geometry, B.geometry) AS geometry, A.name, B.name FROM !infile2!.!intable2! A, !infile3!.!intable3! B WHERE ST_Intersects(A.geometry, B.geometry)"

or, alternatively, find the way to put the table names into the !infile*! variable separated by a dot. E.g. "infile2.sqlite"."intable2"

Similarly to OGRSQL, it is also possible to refer to layers of other datasources with the following syntax : "other_datasource_name"."layer_name". (Source: http://www.gdal.org/ogr_sql_sqlite.html)

  • Your thoughts on my revised question (as per your answer) appreciated – ctlgeowp Apr 13 '17 at 15:09
1

In the end this worked! I also had a rogue " in my code which is corrected below

for %%i in (*inputfile.sqlite) do (

SET "infile1=%%i"
SET "infile2=!infile1:inputfile.sqlite=inputtable!"
SET "infile3=!infile2:inputtable=inputtable2!"
SET "outfile=!infile3:inputtable2=outputtable!"

ogr2ogr -f sqlite -dsco SPATIALITE=YES !outfile!.sqlite !infile1! -nln !outfile! -dialect sqlite -sql "SELECT ST_Intersection(A.geometry, B.geometry) AS geometry, A.name, B.name FROM '!infile2!' A, '!infile3!' B WHERE ST_Intersects(A.geometry, B.geometry)"
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.