Carry out the following steps:
- In this recipe, as with the previous one, you will use a http://openweathermap.org/ web service to get the temperature for a point from the closest weather station. The request you need to run (test it in a browser) is http://api.openweathermap.org/data/2.5/find?lat=55&lon=37&cnt=10&appid=YOURKEY.
- You should get the following JSON output (the closest weather station's data from which you will read the temperature to the point, with the coordinates of the given longitude and latitude):
{
message: "",
cod: "200",
calctime: "",
cnt: 1,
list: [
{
id: 9191,
dt: 1369343192,
name: "100704-1",
type: 2,
coord: {
lat: 13.7408,
lon: 100.5478
},
distance: 6.244,
main: {
temp: 300.37
},
wind: {
speed: 0,
deg: 141
},
rang: 30,
rain: {
1h: 0,
24h: 3.302,
today: 0
}
}
]
}
- Create the following PostgreSQL function in Python, using the PL/Python language:
CREATE OR REPLACE FUNCTION chp08.GetWeather(lon float, lat float)
RETURNS float AS $$
import urllib2
import simplejson as json
data = urllib2.urlopen(
'http://api.openweathermap.org/data/
2.1/find/station?lat=%s&lon=%s&cnt=1'
% (lat, lon))
js_data = json.load(data)
if js_data['cod'] == '200':
# only if cod is 200 we got some effective results
if int(js_data['cnt'])>0:
# check if we have at least a weather station
station = js_data['list'][0]
print 'Data from weather station %s' % station['name']
if 'main' in station:
if 'temp' in station['main']:
temperature = station['main']['temp'] - 273.15
# we want the temperature in Celsius
else:
temperature = None
else:
temperature = None
return temperature $$ LANGUAGE plpythonu;
- Now, test your function; for example, get the temperature from the weather station closest to Wat Pho Templum in Bangkok:
postgis_cookbook=# SELECT chp08.GetWeather(100.49, 13.74);
getweather ------------ 27.22 (1 row)
- If you want to get the temperature for the point features in a PostGIS table, you can use the coordinates of each feature's geometry:
postgis_cookbook=# SELECT name, temperature,
chp08.GetWeather(ST_X(the_geom), ST_Y(the_geom))
AS temperature2 FROM chp08.cities LIMIT 5; name | temperature | temperature2 -------------+-------------+-------------- Minneapolis | 275.15 | 15 Saint Paul | 274.15 | 16 Buffalo | 274.15 | 19.44 New York | 280.93 | 19.44 Jersey City | 282.15 | 21.67 (5 rows)
- Now it would be nice if our function could accept not only the coordinates of a point, but also a true PostGIS geometry as well as an input parameter. For the temperature of a feature, you could return the temperature of the weather station closest to the centroid of the feature geometry. You can easily get this behavior using function overloading. Add a new function, with the same name, supporting a PostGIS geometry directly as an input parameter. In the body of the function, call the previous function, passing the coordinates of the centroid of the geometry. Note that in this case, you can write the function without using Python, with the PL/PostgreSQL language:
CREATE OR REPLACE FUNCTION chp08.GetWeather(geom geometry)
RETURNS float AS $$ BEGIN RETURN chp08.GetWeather(ST_X(ST_Centroid(geom)),
ST_Y(ST_Centroid(geom)));
END;
$$ LANGUAGE plpgsql;
- Now, test the function, passing a PostGIS geometry to the function:
postgis_cookbook=# SELECT chp08.GetWeather(
ST_GeomFromText('POINT(-71.064544 42.28787)')); getweather ------------ 23.89 (1 row)
- If you use the function on a PostGIS layer, you can pass the feature's geometries to the function directly, using the overloaded function written in the PL/PostgreSQL language:
postgis_cookbook=# SELECT name, temperature,
chp08.GetWeather(the_geom) AS temperature2
FROM chp08.cities LIMIT 5; name | temperature | temperature2 -------------+-------------+-------------- Minneapolis | 275.15 | 17.22 Saint Paul | 274.15 | 16 Buffalo | 274.15 | 18.89 New York | 280.93 | 19.44 Jersey City | 282.15 | 21.67 (5 rows)