Database Reference
In-Depth Information
Writing
PostGIS
vector
data
with
Psycopg
In this recipe, you will use Python combined with Psycopg, the most popular Post-
greSQLdatabaselibraryforPython,inordertowritesomedatatoPostGISusingthe
SQL language.
You will write a procedure to import weather data for the most populated US cities.
Youwillimportsuchweatherdatafrom
OpenWeatherData.org
,
whichisawebservice
that provides free weather data and forecast API. The procedure you are going to
write will iterate each major USA city and get the actual temperature for it from the
closest weather stations using the
OpenWeatherData.org
Web service API, getting
theoutputintheJSONformat.(IncaseyouarenewtotheJSONformat,youcanfind
details about it at
http://www.json.org/
.
)
You will also generate a new PostGIS layer with the 10 closest weather stations to
each city.
Getting ready
1. Createadatabaseschemafortherecipesinthischapterusingthefollowing
command:
postgis_cookbook=# CREATE SCHEMA chp08;
2. Download the USA cities' shapefile from the
nationalatlas.gov
website at
archive is anyway included in the dataset that is available with the code
bundle), extract it to
working/chp08
, and import it in PostGIS, filtering out
cities with less than 100,000 inhabitants:
$ ogr2ogr -f PostgreSQL -s_srs EPSG:4269
-t_srs EPSG:4326 -lco
GEOMETRY_NAME=the_geom -nln chp08.cities
PG:"dbname='postgis_cookbook' user='me'
password='mypassword'" -where "POP_2000 >
100000" citiesx020.shp
