One new welcome feature in PostgreSQL 9.2 is the native json support and companion row_as_json and array_as_json functions. PostGIS also has a json function for outputting geographies and geometries in GeoJSON format which is almost a standard in web mapping.
Here is an example of how you'd use the new feature - create our test table
CREATE TABLE test(gid serial PRIMARY KEY, title text, geog geography(Point, 4326));
INSERT INTO test(title, geog)
VALUES('a'
, ST_GeogFromText('POINT(-71.057811 42.358274)'));
INSERT INTO test(title, geog)
VALUES('b'
, ST_GeogFromText('POINT(42.358274 -71.057811 )'));
Now with a command like this we can output all data as a single json object.
SELECT array_to_json(array_agg(t))
FROM test As t;
But there is a tincy little problem. Our geog outputs don't look anything like GeoJSON format. Our output looks like this:
[{"gid":1,"title":"a","geog":"0101000020E61000005796E82CB3C3
51C0E98024ECDB2D4540"}
,{"gid":2,"title":"b","geog":"0
101000020E6100000E98024ECDB2D45405796E82CB3C351C0"}]
To follow the GeoJSON standard, our geography object should output like this:
"geog":{"type":"Point","coordinates":[-71.057811000000001,42.358274000000002]}
We were hoping it would be a simple matter of defining a cast for geometry and geography something like this:
CREATE OR REPLACE FUNCTION json(geog geography)
RETURNS json AS
$$ SELECT _ST_AsGeoJSON(1, $1, 15, 0)::json; $$
LANGUAGE sql IMMUTABLE STRICT;
CREATE CAST (geography AS json)
WITH FUNCTION json(geography)
AS IMPLICIT;
And with the above CAST, array_to_json would be Clearly I'm missing something here. I have to apply this work-around:
SELECT array_to_json(array_agg(t)) As my_places
FROM (SELECT gid, title, geog::json As geog FROM test) As t;
[{"gid":1,"title":"a"
,"geog":{"type":"Point"
,"coordinates":[-71.057811000000001,42.358274000000002]}}
,{"gid":2,"title":"b"
,"geog":{"type":"Point"
,"coordinates":[42.358274000000002,-71.057811000000001]}}]
Which ain't bad and much better than before, but is not quite as nice as being able to just use aray_to_json without care of types of columns and have array_to_json function automatically use a json CAST if a type provides a custom json CAST. I have this ticketed in PostGIS 2.1 as a nice feature to have.
PostGIS in Action
About the Authors
Consulting
Tracked: Jun 07, 01:20
Tracked: Aug 24, 02:16