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