Examples¶
Conversion¶
Some functions that analyze or manipulate geometries are possible in SQL but are easier to model in a procedural language. The following example will use Shapely to find the largest component polygon of a multipolygon.
CREATE OR REPLACE FUNCTION largest_poly(geom geometry)
RETURNS geometry
AS $$
from plpygis import Geometry
polygons = Geometry(geom)
if polygons.type == 'Polygon':
return polygons
elif polygons.type == 'MultiPolygon':
largest = max(polygons.shapely, key=lambda polygon: polygon.area)
return Geometry.from_shapely(largest)
else:
return None
$$ LANGUAGE plpython3u;
A pure PL/pgSQL function will have significantly better performance:
CREATE OR REPLACE FUNCTION largest_poly_fast(polygons geometry)
RETURNS geometry
AS $$
WITH geoms AS (
SELECT (ST_Dump(polygons)).geom AS geom
)
SELECT geom
FROM geoms
ORDER BY ST_Area(geom) DESC LIMIT 1;
$$ LANGUAGE sql;
External services¶
Another application of plpygis is accessing external services or commands directly from PostgreSQL.
CREATE OR REPLACE FUNCTION geocode(geom geometry)
RETURNS text
AS $$
from geopy import Nominatim
from plpygis import Geometry
shape = Geometry(geom).shapely
centroid = shape.centroid
lon = centroid.x
lat = centroid.y
nominatim = Nominatim()
location = nominatim.reverse((lat, lon))
return location.address
$$ LANGUAGE plpython3u;
db=# SELECT name, geocode(geom) FROM countries LIMIT 5;
name | geocode
-------------------------+-----------------------------------------------------------
Angola | Ringoma, Bié, Angola
Anguilla | Eric Reid Road, The Valley, Anguilla
Albania | Bradashesh, Elbasan, Qarku i Elbasanit, 3001, Shqipëria
American Samoa | Aunu u, Sa'Ole County, Eastern District, American Samoa
Andorra | Bordes de Rigoder, les Bons, Encamp, AD200, Andorra
(5 rows)
Rendering output¶
The gj2ascii project allows geometries to be easily rendered with a PL/Python function.
CREATE FUNCTION show(geom geometry)
RETURNS text
AS $$
from gj2ascii import render
from plpygis import Geometry
g = Geometry(geom)
return render(g)
$$ LANGUAGE plpython3u
db=# SELECT show(geom) FROM countries WHERE name = 'Malta';
show
-------------------------------------------------------------
+ + + + + +
+ + + + + + + + + +
+ + + + + + + + + + + +
+ + + + + + + + + + +
+ + + + + +
+
+ +
+ + +
+ + + + + + + +
+ + + + + + + + + +
+ + + + + + + + + + + +
+ + + + + + + + + + + + + +
+ + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + ++
+ + + + + + + + + + + + + + ++
+ + + + + + + + + + + + + ++
+ + + + + + + + + + +
+ + + + +
(1 row)
Spatial aggregate function¶
Normally, the function show as defined above would print the geometries of individual rows, one each per line.
db=# SELECT show(geom) FROM countries WHERE continent = 'Africa';
An aggregate version of show would take all the geometries and print them as a single map.
db=# SELECT showall(geom) FROM countries WHERE continent = 'Africa';
showall
-------------------------------------------------------------
1 1 1 Q +
= = 1 1 1 1 Q +
= = = 1 1 1 1 1 ; ; ; ; ; - - - +
= = 1 1 1 1 1 1 1 ; ; ; ; ; ; - - - +
= @ @ 1 1 1 1 1 1 1 ; ; ; ; ; ; - - - +
G @ @ > > 1 1 1 1 1 C ; ; ; ; ; - - - - +
@ @ @ @ @ > > 1 1 1 C C C O O ; ; H H H H +
@ @ @ @ > > > > C C C C O O O H H H H H H +
F F @ @ @ > > > C C C C O O O O H H H H H . +
F F > > > $ $ C C D C D ( O O H H H H H H 2 2 . +
0 0 0 > $ 4 # D D D D ( O O & H H H I 2 2 2 2 K L+
J 0 ' ' 4 P D D D D ( O & & & I I I 2 2 2 2 2 2 +
: ' ' 4 D ( ( ( & & & & & I I I 2 2 2 2 L +
( ( * ) ) ) ) ) S 8 8 8 L L +
M 3 * * ) ) ) ) ) S 8 8 8 L +
3 * ) ) ) ) ) E R R 8 8 +
) ) ) ) ) ) R R R R +
! ! ) ) ) ) ) R R R +
! ! ! ! ) ) U U R R +
! ! ! ! U U ) U ? ? ? +
! ! ! U U U U ? A ? ? 9 9+
B B B ! ! B U V V ? 9 9 +
B B B % % % V V 9 9 +
B B % % % T ? ? 9 9 +
B B % % T T ? 9 +
B B T T T T T +
T T T T T +
T T T T +
+
+
(1 row)
The aggregate function is defined with the following properties:
CREATE AGGREGATE showall(geometry) (
STYPE=geometry[],
INITCOND='{}',
SFUNC=array_append,
FINALFUNC=_final_geom_show
);
The STYPE of geometry[] indicates that after each individual geometry has been processed, there will be a PostgreSQL list of individual geometry objects as a result. INITCOND is used to ensure that list starts empty and can be added to incrementally by the native PostgreSQL function array_append.
The function _final_geom_show will take the STYPE as the single parameter:
CREATE OR REPLACE FUNCTION _final_geom_show(geoms geometry[])
RETURNS text
AS $$
from gj2ascii import render_multiple
from plpygis import Geometry
from itertools import cycle
# assign an ascii character sequentially to each geometry
chars = [chr(i) for i in range(33,126)]
geojsons = [Geometry(g) for g in geoms]
layers = zip(geojsons, chars)
return render_multiple(layers, width)
$$ LANGUAGE plpython3u
PL/Python automatically maps lists to Python arrays, so plpygis is only responsible for converting each elment of the list (in the example, above this is done using list comprehension: [Geometry(g) for g in geoms]).