ogr2ogr sql - how to call file from a variable set in a loop in windows batch

Question

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)"
)

• Antonio Falciano: !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.

Answer 1

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)

• ctlgeowp: Your thoughts on my revised question (as per your answer) appreciated

Answer 2

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)"
)