[postgis-users] Selecting as GeoJSON with Transformation

[Nicolas Ribot]

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