Table of Contents
I authored two Python modules that make it easier to use geospatial Python from within your PostgreSQL database.
plpygis is a Python conveter to and from the PostGIS geometry type, WKB, EWKB, GeoJSON and Shapely geometries and additionally supports
plpygis is intended for use in PL/Python functions, making the entire Python geospatial ecosystem available in SQL queries.
PL/Python for PostGIS
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
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 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.
Various PostGIS-related FDWs