[postgis-users] Selecting as GeoJSON with Transformation

[Sean Montague]

That worked. Thank you very much.

Sean

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

> Hmm sorry.
> I pasted your table name without checking.
> topology.trails_01 is aliased as "t" in the query:
>
> with tmp1 as (
>   select 'Feature' as "type",
>          ST_AsGeoJSON(ST_Transform(ST_SetSRID(t.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
>
> On Mon, 11 Feb 2019 at 16:00, Sean Montague <couloir007 at gmail.com> wrote:
>
>> 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
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> 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/81df22fc/attachment.html>