Writing PostGIS functions in Python language [Tutorial]

In this tutorial, you will learn to write a Python function for PostGIS and PostgreSQL using the PL/Python language and effective libraries like urllib2 and simplejson.

You will use Python to query the http://openweathermap.org/ web services to get the weather for a PostGIS geometry from within a PostgreSQL function.

This tutorial is an excerpt from a book written by Mayra Zurbaran,Pedro Wightman, Paolo Corti, Stephen Mather, Thomas Kraft and Bborie Park titled PostGIS Cookbook - Second Edition.

Adding Python support to database

1. Verify your PostgreSQL server installation has PL/Python support. In Windows, this should be already included, but this is not the default if you are using, for example, Ubuntu 16.04 LTS, so you will most likely need to install it:

      $ sudo apt-get install postgresql-plpython-9.1

2. Install PL/Python on the database (you could consider installing it in your template1 database; in this way, every newly created database will have PL/Python support by default):

How to do it...

Carry out the following steps:

1. In this tutorial, 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.
2. 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
              }
            }
          ]  
        }

3. 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;

4. 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)

5. 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)

6. 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;

7. 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)

8. 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)

In this tutorial, you wrote a Python function in PostGIS, using the PL/Python language. Using Python inside PostgreSQL and PostGIS functions gives you the great advantage of being able to use any Python library you wish. Therefore, you will be able to write much more powerful functions compared to those written using the standard PL/PostgreSQL language.

In fact, in this case, you used the urllib2 and simplejson Python libraries to query a web service from within a PostgreSQL function—this would be an impossible operation to do using plain PL/PostgreSQL. You have also seen how to overload functions in order to provide the function's user a different way to access the function, using input parameters in a different way.

To get armed with all the tools and instructions you need for managing entire spatial database systems, read PostGIS Cookbook - Second Edition.

Top 7 libraries for geospatial analysis

Learning R for Geospatial Analysis