[postgis-users] Selecting as GeoJSON with Transformation

[Sean Montague]

Hi Nicolas,

Thank you for the response. I have tried working with the example but I'm
unable to get it to run. In the example you provided I keep getting.

ERROR:  missing FROM-clause entry for table "trails_01"
LINE 3:          ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom...

which searches lead me to there is an ordering issue

Thanks!
Sean

On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot <nicolas.ribot at gmail.com>
wrote:

>
> On Fri, 8 Feb 2019 at 23:48, Sean Montague <couloir007 at gmail.com> wrote:
>
>> It has been many years since I last worked with postGIS, I don't do GIS
>> for a living anymore but I still like to have fun with it. I'm looking to
>> export some old data I have as GeoJSON and projecting it to display in a
>> Google Map Overlay. I can get the two individual queries below to work, but
>> I cannot figure out how to combine them. I want to transform and output to
>> GeoJSON with attributes stored in the DB.
>>
>> SELECT
>>   ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),
>>   trails_01.name,
>>   trails_01.id
>> FROM
>>   topology.trails_01;
>>
>> SELECT jsonb_build_object(
>>     'type',     'FeatureCollection',
>>     'features', jsonb_agg(features.feature)
>> )
>> FROM (
>>   SELECT jsonb_build_object(
>>     'type',       'Feature',
>>     'id',         gid,
>>     'geometry',   ST_AsGeoJSON(geom)::jsonb,
>>     'properties', to_jsonb(inputs) - 'gid' - 'geom'
>>   ) AS feature
>>   FROM (SELECT * FROM topology.trails_01) inputs) features;
>>
>> Any help would be appreciated as I start down this path of reteaching
>> myself postGIS.
>>
>> Thank you,
>> Sean
>>
>
> Hi,
>
> Something like:
>
> with tmp1 as (
>   select 'Feature' as "type",
>          ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918),
> 4326), 6)::json as "geometry",
>          (
>            select json_strip_nulls(row_to_json(t))
>            from (select id, inputs) t
>          ) as "properties"
>   from topology.trails_01 t
> ), tmp2 as (
>          select 'FeatureCollection' as "type",
>               array_to_json(array_agg(t)) as "features"
>        from tmp1 t
> ) select row_to_json(t)
> from tmp2 t;
>
> Nicolas
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/3dd987ff/attachment.html>