I wrote and maintain two Python modules that make it easier to use geospatial Python from within your PostgreSQL database.
plpygis#
plpygis is a pure Python module with no dependencies that can convert geometries between Well-known binary (WKB/EWKB), Well-known Text (WKT/EWKT) and GeoJSON representations. plpygis is mainly intended for use in PostgreSQL PL/Python functions to augment PostGIS’s native capabilities.
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. I also presented an example of using plpygis for some real-world analysis at FOSS4G 2023 in Prizren, and you can find a write-up here.
Documentation
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.