bashsqliteterminalgdalogr2ogr

envsubst with ogr2ogr external .sql file

I’m trying to combine the envsubst command with an external sql file passed as an option to ogr2ogr, but can't quite get it work. 

export STOP_NAME=Park;

ogr2ogr \
-f geojson \
/vsistdout/ \
stops.csv \
-dialect sqlite \
-sql envsubst < @stop_geo.sql

Where stop_geo.sql is:

SELECT *
FROM stops
WHERE stop_name = '$STOP_NAME'

This errors out with:

Warning 1: layer names ignored in combination with -sql.
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(envsubst):
  near "envsubst": syntax error

Is there any way to replace the environment variable in the external sql file within the ogr2ogr -sql command option?

Solution

  • The envsubst command needs to be wrapped in a quoted command substitution (i.e., "$()"). Instead of using the @ syntax as mentioned in the ogr2ogr sql documentation, it simply needs to be read in using the common < operator.

    export STOP_NAME=Park;

ogr2ogr \
-f geojson \
/vsistdout/ \
stops.csv \
-dialect sqlite \
-sql "$(envsubst < stop_geo.sql)"