<div dir="ltr"><div dir="ltr">Hi Nicolas,<div><br></div><div>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.</div><div><br></div><div><div>ERROR: missing FROM-clause entry for table "trails_01"</div><div>LINE 3: ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom...</div></div><div><br></div><div>which searches lead me to there is an ordering issue </div><div><br></div><div>Thanks!</div><div>Sean</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_quote"><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" class="gmail_attr">On Fri, 8 Feb 2019 at 23:48, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">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.<div><br></div><div><div>SELECT</div><div> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),</div><div> <a href="http://trails_01.name" target="_blank">trails_01.name</a>,</div><div> <a href="http://trails_01.id" target="_blank">trails_01.id</a></div><div>FROM</div><div> topology.trails_01;</div><div><br></div><div>SELECT jsonb_build_object(</div><div> 'type', 'FeatureCollection',</div><div> 'features', jsonb_agg(features.feature)</div><div>)</div><div>FROM (</div><div> SELECT jsonb_build_object(</div><div> 'type', 'Feature',</div><div> 'id', gid,</div><div> 'geometry', ST_AsGeoJSON(geom)::jsonb,</div><div> 'properties', to_jsonb(inputs) - 'gid' - 'geom'</div><div> ) AS feature</div><div> FROM (SELECT * FROM topology.trails_01) inputs) features;</div></div><div><br></div><div>Any help would be appreciated as I start down this path of reteaching myself postGIS.</div><div><br></div><div>Thank you,</div><div>Sean</div></div></div></blockquote><div><br></div><div><div dir="ltr"><div dir="ltr"><div>Hi,</div><div><br></div><div>Something like:</div><div><br></div><div><span style="font-family:monospace,monospace">with tmp1 as (<br> select 'Feature' as "type",<br> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326), 6)::json as "geometry",<br> (<br> select json_strip_nulls(row_to_json(t))<br> from (select id, inputs) t<br> ) as "properties"<br> from topology.trails_01 t<br>), tmp2 as (<br> select 'FeatureCollection' as "type",<br> array_to_json(array_agg(t)) as "features"<br> from tmp1 t<br>) select row_to_json(t)<br>from tmp2 t;</span><br></div></div></div><br><div class="gmail_quote"><div class="gmail_attr">Nicolas</div></div> </div></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>