4 GEONAMESXML=http://ws.geonames.org/countryInfo
10 echo Usage: ${0##*/} [options]
13 echo " " -h display this help message
14 echo " " -s be more silent \(show only warnings\)
19 args=`getopt -o sh -l silent,help -- "$@"`
23 -h|--help) usage; exit 0 ;;
24 -s|--silent) SILENT="1"; shift ;;
26 *) echo "Invalid option: $1"; exit 1 ;;
30 if [ ${#SILENT} -ne 0 ]; then
31 # we won't see all the index creation notices when creating tables
32 export PGOPTIONS='--client_min_messages=warning'
35 psql --set "ON_ERROR_STOP=1" -f - <<EOF
37 DROP TABLE IF EXISTS geonames CASCADE;
38 CREATE TABLE geonames (
39 country CHAR(2) UNIQUE PRIMARY KEY,
49 TMPFILE=`mktemp --tmpdir=$TMPDIR`
50 trap "rm -rf $TMPDIR;" EXIT
54 for line in $(cat ${GEONAMESXML##*/}); do
55 if echo $line | grep -qi '<country>'; then
56 COUNTRY=""; MINLON=""; MINLAT=""; MAXLON=""; MAXLAT=""
57 elif echo $line | grep -qi '<countryCode>.*</countryCode>'; then
58 COUNTRY=$(echo $line | sed -e 's/^\s*<countryCode>\(.*\)<\/countryCode>\s*/\1/i')
59 elif echo $line | grep -qi '<bBoxWest>.*</bBoxWest>'; then
60 MINLON=$(echo $line | sed -e 's/^\s*<bBoxWest>\(.*\)<\/bBoxWest>\s*/\1/i')
61 elif echo $line | grep -qi '<bBoxSouth>.*</bBoxSouth>'; then
62 MINLAT=$(echo $line | sed -e 's/^\s*<bBoxSouth>\(.*\)<\/bBoxSouth>\s*/\1/i')
63 elif echo $line | grep -qi '<bBoxEast>.*</bBoxEast>'; then
64 MAXLON=$(echo $line | sed -e 's/^\s*<bBoxEast>\(.*\)<\/bBoxEast>\s*/\1/i')
65 elif echo $line | grep -qi '<bBoxNorth>.*</bBoxNorth>'; then
66 MAXLAT=$(echo $line | sed -e 's/^\s*<bBoxNorth>\(.*\)<\/bBoxNorth>\s*/\1/i')
67 elif echo $line | grep -qi '<\/country>'; then
68 if [ ${#COUNTRY} -ne 0 -a ${#MINLON} -ne 0 -a ${#MINLAT} -ne 0 -a ${#MAXLON} -ne 0 -a ${#MAXLAT} -ne 0 ]; then
69 echo "INSERT INTO geonames (country, minlon, minlat, maxlon, maxlat) VALUES ('$COUNTRY', $MINLON, $MINLAT, $MAXLON, $MAXLAT);" >> $TMPFILE
73 echo "INSERT INTO geonames (country, minlon, minlat, maxlon, maxlat) VALUES ('EU', -26, 34, 40, 68);" >> $TMPFILE
74 echo "INSERT INTO geonames (country, minlon, minlat, maxlon, maxlat) VALUES ('AP', 90, -20, -140, 68);" >> $TMPFILE
75 psql --set "ON_ERROR_STOP=1" -f $TMPFILE