[postgis-users] Selecting as GeoJSON with Transformation

[Nicolas Ribot]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/9b651e85/attachment.html>