GeoJSON Features from PostGIS

Every once in a while, someone comes to me and says:

Sure, it’s handy to use ST_AsGeoJSON to convert a geometry into a JSON equivalent, but all the web clients out there like to receive full GeoJSON Features and I end up writing boilerplate to convert database rows into GeoJSON. Also, the only solution I can find on the web is scary and complex. Why don’t you have a row_to_geojson function?

And the answer (still) is that working with rows is fiddly and I don’t really feel like it.

However! It turns out that, with the tools for JSON manipulation already in PostgreSQL and a little scripting it’s possible to make a passable function to do the work.

Start with a simple table.

DROP TABLE IF EXISTS mytable ; CREATE TABLE mytable ( pk SERIAL PRIMARY KEY , name TEXT , size DOUBLE PRECISION , geom GEOMETRY ); INSERT INTO mytable ( name , size , geom ) VALUES ( 'Peter' , 1 . 0 , 'POINT(2 34)' ), ( 'Paul' , 2 . 0 , 'POINT(5 67)' );

You can convert any row into a JSON structure using the to_jsonb() function.

SELECT to_jsonb(mytable.*) FROM mytable; {"pk": 1, "geom": "010100000000000000000000400000000000004140", "name": "Peter", "size": 1} {"pk": 2, "geom": "010100000000000000000014400000000000C05040", "name": "Paul", "size": 2}

That’s actually all the information we need to create a GeoJSON feature, it just needs to be re-arranged. So let’s make a little utility function to re-arrange it.

CREATE OR REPLACE FUNCTION rowjsonb_to_geojson ( rowjsonb JSONB , geom_column TEXT DEFAULT 'geom' ) RETURNS TEXT AS $$ DECLARE json_props jsonb ; json_geom jsonb ; json_type jsonb ; BEGIN IF NOT rowjsonb ? geom_column THEN RAISE EXCEPTION 'geometry column '' % '' is missing' , geom_column ; END IF ; json_geom : = ST_AsGeoJSON (( rowjsonb ->> geom_column ):: geometry ):: jsonb ; json_geom : = jsonb_build_object ( 'geometry' , json_geom ); json_props : = jsonb_build_object ( 'properties' , rowjsonb - geom_column ); json_type : = jsonb_build_object ( 'type' , 'Feature' ); return ( json_type || json_geom || json_props ):: text ; END ; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT ;

Voila! Now we can turn any relation into a proper GeoJSON “Feature” with just one(ish) function call.

SELECT rowjsonb_to_geojson(to_jsonb(mytable.*)) FROM mytable; {"type": "Feature", "geometry": {"type": "Point", "coordinates": [2, 34]}, "properties": {"pk": 1, "name": "Peter", "size": 1}} {"type": "Feature", "geometry": {"type": "Point", "coordinates": [5, 67]}, "properties": {"pk": 2, "name": "Paul", "size": 2}}

Postscript