2

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/
  • 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... – ThingumaBob May 8 '18 at 9:30
  • 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. – m.brocks May 8 '18 at 16:40
  • 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) – ThingumaBob May 8 '18 at 17:29
  • Can you make ogrinfo to show the layer name from /vsistdin/? – user30184 May 8 '18 at 21:05
  • @ThingumaBob - thought this one was going to work, but ended up getting the same error as I had originally gotten. @ user30184 - trying to run ogrinfo against the piped in data just gave me an error of "No datasource specified". I'm going to keep pulling on the OGRGeoJSON thread and see what I can find. – m.brocks May 9 '18 at 4:29
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

  • 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. – user30184 May 10 '18 at 15:34

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.