Opened 8 years ago

Closed 7 months ago

Last modified 7 months 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:

Description

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.

Change History (8)

comment:1 Changed 8 years ago by 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]}}]

comment:2 Changed 7 years ago by robe

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 Changed 2 years ago by robe

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

comment:4 Changed 2 years ago by 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?.

comment:5 Changed 2 years ago by 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.

comment:6 Changed 2 years ago by robe

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 Changed 7 months ago by pramsey

Resolution: fixed
Status: newclosed

In 17415:

ST_AsGeoJSON(record) implementation from Joe Conway
Closes #1833

comment:8 Changed 7 months ago by pramsey

In 17417:

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

Note: See TracTickets for help on using tickets.