Database Reference
In-Depth Information
country_name | iso2 | hs_count
--------------------------------------------
Albania | AL | 66
Algeria | DZ | 361
...
Yemen | YE | 6
Zambia | ZM | 1575
Zimbabwe | ZW | 179
(103 rows)
2. Usingthesamequery,generateaCSVfileusingthePostgreSQL
COPY
com-
mandorthe
ogr2ogr
command(inthefirstcase,makesurethatthePost-
greserviceuserhasfullwritepermissiontotheoutputdirectory).Ifyouare
followingthe
COPY
approachandusingWindows,besuretoreplace
/tmp/
hs_countries.csv
with a different path:
$ ogr2ogr -f CSV hs_countries.csv
PG:"dbname='postgis_cookbook' user='me'
password='mypassword'" -lco SCHEMA=chp01
-sql "SELECT c.country_name, MIN(c.iso2)
as iso2, count(*) as hs_count FROM
chp01.hotspots as hs JOIN chp01.countries
as c ON ST_Contains(c.the_geom,
hs.the_geom) GROUP BY c.country_name
ORDER BY c.country_name"
postgis_cookbook=> COPY (SELECT
c.country_name, MIN(c.iso2) as iso2,
count(*) as hs_count
FROM chp01.hotspots as hs
JOIN chp01.countries as c
ON ST_Contains(c.the_geom, hs.the_geom)
GROUP BY c.country_name
ORDER BY c.country_name) TO '/tmp/
hs_countries.csv' WITH CSV HEADER;
3. If you are using Windows, go to step 5. With Linux, create a bash script
named
export_shapefiles.sh
thatiterateseachrecord(country)inthe
