How to reference piped input in ogr2ogr SQL statement

Question

I have a geojson data set that I am piping into an an ogr2ogr command using /vsistdin/. However, I am having trouble figuring out how to actually refer to this input in the SQL statement I'm running. The below simplified example has been erroring out when I run it (I'm doing some previous manipulation/cleanup on the data, not just using cat, that I have left out for brevity's sake). It's obvious what the error is, I just can't figure out what else to refer to the piped input as.

Example:

cat input.json | ogr2ogr -f geojson test.json /vsistdin/ -dialect sqlite -sql "SELECT *
FROM /vsistdin/"

Error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT *
FROM '/vsistdin/'): no such table: /vsistdin/

• geozelot: can´t test (since I can´t get piping geojson to /vsistdin/ to work on windows...) and not sure, but did you try ... FROM input? usually, you just refer to the file/input layer name...might be similar with the pipe. might just as well be bogus, though...
• m.brocks: thanks for the thought - the issue with your suggestion is the data I'm piping in isn't actually coming from a file, but instead is the output, via /vsistdout/, of a previous ogr2ogr command. So there isn't even a file name to refer to in my case.
• geozelot: yeah I thought so. you could try ... FROM OGRGeoJSON. (the geojson reader will try to resolve the layer name from 1) a property on top node level 2) the file name 3) or OGRGeoJSON as default)
• user30184: Can you make ogrinfo to show the layer name from /vsistdin/?
• m.brocks: Managed to get ogrinfo to return a response of INFO: Open of /vsistdin/ using driver 'GeoJSON' successful. 1: SELECT (Point) using ... | ogrinfo /vsistdin/

Answer 1

I ended up figuring this out by first looking at the name property in the geojson getting piped into the ogr2ogr function. Without specifying otherwise, this property was getting set to "SELECT" from the previous ogr2ogr SQL statement (not sure if this is a bug or default behavior that's working as intended, but regardless it's what was happening). I initially tried using this as is, but because of SELECT being a reserved SQL keyword, I had to change it to something else using -nln. I ended up settling on the below, which works as desired:

ogr2ogr -f geojson -nln INPUT /vsistdout/ test.json -dialect sqlite -sql 
"SELECT * FROM test" | ogr2ogr -f geojson /vsistdout/ /vsistdin/ -dialect 
sqlite -sql "SELECT * FROM INPUT"

Credit to ThingumaBob for pointing me towards the relevant documentation here

• user30184: SQL keywords can usually be used if they are correctly quoted with double quotation marks. This might work "SELECT * FROM \"SELECT\"". Notice than inner "s must be quoted with \. But renaming the layer is more readable solution.

Answer 2

I was using ogr2ogr with CURL and vsistdin and had a similar problem. The layer name I found out worked was "", even when I specified a layer anme with -nln:

curl example.com | ogr2ogr -f 'GeoJSON' test.geojson /vsistdin/ -nln foo -sql "select * from \"\""

This worked on GDAL 3.2.0dev and GDAL 2.4.1. I am not sure if this is the expected behavior so it may change in future versions.