Opened 12 years ago

Closed 5 years ago

Last modified 5 years ago

#1833 closed enhancement (fixed)

full record json output ala ST_ASGeoJSON that works against a row

Reported by: robe Owned by: pramsey
Priority: medium Milestone: PostGIS Fund Me
Component: postgis Version: master
Keywords: Cc:


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

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.

Change History (8)

comment:1 by robe, 12 years ago

Seems it's not as trivial as that: See my whining:

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]}}]

comment:2 by robe, 12 years ago

Milestone: PostGIS 2.1.0PostGIS Future

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.

comment:3 by robe, 7 years ago

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

comment:4 by pramsey, 7 years ago

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.

comment:5 by robe, 7 years ago

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.


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

comment:6 by robe, 7 years ago

Summary: full record json output in 9.2 with valid geojson outputfull record json output ala ST_ASGeoJSON that works against a row
Version: 2.0.xtrunk

comment:7 by pramsey, 5 years ago

Resolution: fixed
Status: newclosed

In 17415:

ST_AsGeoJSON(record) implementation from Joe Conway
Closes #1833

comment:8 by pramsey, 5 years ago

In 17417:

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

Note: See TracTickets for help on using tickets.