Python for PostGIS

I authored two Python modules that make it easier to use geospatial Python from within your PostgreSQL database.

plpygis #

plpygis is a Python conveter to and from the PostGIS geometry type, WKB, EWKB, GeoJSON and Shapely geometries and additionally supports __geo_interface__. plpygis is intended for use in PL/Python functions, making the entire Python geospatial ecosystem available in SQL queries.

A simple Python function using plpygis would be:

CREATE OR REPLACE FUNCTION swap(geom geometry)
  RETURNS geometry
AS $$
  # swaps the x and y coordinates of a point
  from plpygis import Geometry, Point
  old_point = Geometry(geom)
  new_point = Point([old_point.y, old_point.x])
  return new_point
$$ LANGUAGE plpython3u;

The magic happens with Geometry(geom), which is automatically converts from PostGIS’s geometry type, and the return statement, which automatically converts back to a PostGIS geometry.

The function above can be called with a normal SQL statement:

SELECT swap(geom) FROM city;

I spoke about plpygis at FOSS4G 2017 in Boston. The slides are here and a video of the talk is also available.

geofdw #

geofdw is a collection of PostGIS-related foreign data wrappers for PostgreSQL written in Python using the multicorn extension. By using a FDW, you can access spatial data through Postgres tables without having to import the data first, which can be useful for dynamic or non-tabular data available through web services.