[postgis-users] Selecting as GeoJSON with Transformation

[Sean Montague]

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