full record json output ala ST_ASGeoJSON that works against a row

[robe]

9.2 now has native json support. YES. It has a json type and I can output a whole dataset as a single json object with this SWEET command.

CREATE TABLE test(gid serial primary key, title text, geog geography(Point,4326));

INSERT INTO test(title, geog) VALUES('My neck of the woods', ST_GeogFromText('POINT(-71.057811 42.358274)'));

INSERT INTO test(title, geog) VALUES('some other neck of the woods', ST_GeogFromText('POINT(42.358274 -71.057811 )'));

SELECT array_to_json(array_agg(t))
FROM test As t

Ah but our beautiful geography which we have grown to love is not outputting using our json function. We need to rectify this.

 [{"gid":1,"title":"My neck of the woods","geog":"0101000020E61000005796E82CB3C3
51C0E98024ECDB2D4540"},{"gid":2,"title":"some other neck of the woods","geog":"0
101000020E6100000E98024ECDB2D45405796E82CB3C351C0"}]

I'm hoping its as trivial as defining a CAST for geography/geometry to json using our ST_AsGeoJSON functions. I'll test that theory out next.

[robe]

Seems it's not as trivial as that: See my whining: ​http://www.postgresonline.com/journal/archives/253-PostgreSQL-9.2-native-json-type-support.html

It's still a nice feature and has a fairly easy work around (which allows you to control the rounding). I have to verify it produces that right output, but looks right to me.

SELECT array_to_json(array_agg(t),true) As my_places
FROM (SELECT gid, title
   , ST_AsGeoJson(1, geog, 15, 0)::json As geog 
FROM test) As t;

[{"gid":1,"title":"My neck of the woods","geog":{"type":"Point","coordinates":[-71.057811000000001,42.358274000000002]}},
 {"gid":2,"title":"some other neck of the woods","geog":{"type":"Point","coordinates":[42.358274000000002,-71.057811000000001]}}]

[robe]

I think this requires changes in PostgreSQL that are not there yet. Specifically how to handle types that are not built-in. So this probably can't happen before 9.3.

[robe]

Milestone renamed

[pramsey]

I think the work on ST_AsMVT shows the way to a ST_AsGeoJSON(row) function that turns a database row into a complete geometry+properties GeoJson.

[robe]

So want something like that for 2.5? I'm not sure we can keep the name though, because a geometry can't be serviced by same function as row.

e.g. I tried ST_ASMVT with a geometry and got this error:

ERROR:  pgis_asmvt_transfn: parameter row cannot be other than a rowtype

So we might want to call it something different, especially since it will be an agg.

ST_AsGeoJSONFeatureCollection(row)

As adding FeatureCollection to it would make it clear that it resutns in FeatureCollection format suitable for Leaflet/OpenLayers etc.

[pramsey]

In 17415:

ST_AsGeoJSON(record) implementation from Joe Conway
Closes #1833

[pramsey]

In 17417:

Add missing files from previous commits
References #1833 and #3687