shell script ogr2ogr export

Question

I'm writing a simple script to export postgis data to geojson. The ogr2ogr command works well, but I'm having problems when I'm passing the parameters.

#!/bin/bash
echo 'enter country code'
read export
echo 'exporting data from $export'
ogr2ogr -f GeoJSON export.geojson PG:'dbname=alamedaok user=postgres host=localhost' -sql "SELECT id_alameda, pais, geom FROM poblaciones_def WHERE pais = `$export`"

The problem comes in the WHERE pais = **$export"** I don't know how "insert" the variable in the sql sentence. I've tried with '$' and with `, with no luck.

Any help?

Answer 1

Bash is particular about the quote characters `, ' and ". Use double quotes to substitute the variable in a string like "wonderful $VAR". Also with Bash, the convention is that variables are upper-case, and I'd avoid using export as a variable name since it is a command for environment variables. Consider these changes:

#!/bin/bash
read -p "enter country code: " CODE
echo "exporting data from $CODE"
SRC="PG:'dbname=alamedaok user=postgres host=localhost'"
SQL="SELECT id_alameda, pais, geom FROM poblaciones_def WHERE pais=$CODE"
ogr2ogr -f GeoJSON code_$CODE.geojson $SRC -sql $SQL

You can also prefix the last command with echo to see what it is using if it needs to be debugged.

Answer 2

Try the following:

#!/bin/bash
read -p 'Enter country code: ' EXP
echo "exporting data from $EXP"
echo ogr2ogr -f GeoJSON export.geojson PG:'dbname=alamedaok user=postgres host=localhost' -sql "SELECT id_alameda, pais, geom FROM poblaciones_def WHERE pais = '$EXP'"

Notes:

• read uses the -p parameter to set the prompt
• You used backquotes (``) in your SQL string to enclose the variable, which bash interpreted as a command substitution. Use single quotes instead ('').
• Single quotes (' ') operate similarly to double quotes, but do not permit referencing variables, since the special meaning of $ is turned off (ref). The single quotes in the SQL string aren't treated as quotes as they are part of the double quoted string.