<div dir="ltr"><div>Resolved:</div><div><br></div>Turns out that the jdbc driver does not like capital letters in the table/view name.<div>Kind regards, Michael Fene'</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Feb 11, 2019 at 12:00 PM <<a href="mailto:postgis-users-request@lists.osgeo.org">postgis-users-request@lists.osgeo.org</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">Send postgis-users mailing list submissions to<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a href="mailto:postgis-users-request@lists.osgeo.org" target="_blank">postgis-users-request@lists.osgeo.org</a><br>
<br>
You can reach the person managing the list at<br>
<a href="mailto:postgis-users-owner@lists.osgeo.org" target="_blank">postgis-users-owner@lists.osgeo.org</a><br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of postgis-users digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. Re: Attempting to Connect to POSTGIS Database Table from BIRT<br>
To Create a report from an POSTGIS attribute table - connection<br>
throws error (with only spatial enabled tables) (Phillip Ross)<br>
2. Re: Selecting as GeoJSON with Transformation (Nicolas Ribot)<br>
3. Re: Selecting as GeoJSON with Transformation (Sean Montague)<br>
4. Re: Selecting as GeoJSON with Transformation (Nicolas Ribot)<br>
5. Re: Selecting as GeoJSON with Transformation (Sean Montague)<br>
<br>
<br>
----------------------------------------------------------------------<br>
<br>
Message: 1<br>
Date: Sun, 10 Feb 2019 23:22:21 -0500<br>
From: Phillip Ross <<a href="mailto:phillip.w.g.ross%2Bpostgis-users@gmail.com" target="_blank">phillip.w.g.ross+postgis-users@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: Re: [postgis-users] Attempting to Connect to POSTGIS Database<br>
Table from BIRT To Create a report from an POSTGIS attribute table -<br>
connection throws error (with only spatial enabled tables)<br>
Message-ID:<br>
<CAKcj57AkSYKijq_rjaVJUwJOu21hpGHFHH8EyaqJaZ=<a href="mailto:jtNTP1w@mail.gmail.com" target="_blank">jtNTP1w@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="UTF-8"<br>
<br>
Hi Michael,<br>
<br>
I'm not familiar with BIRT, but the error you pasted hints that it may<br>
be tripping up on the fact that it thinks a table doesn't exist:<br>
ERROR: relation "public.tollbrotherspacifichighranchparcels" does not exist.<br>
<br>
The postgis-java jdbc extensions are only necessary for applications<br>
which want to use explicitly use java-based geometry types which<br>
general applications such as BIRT generally do not do. I would<br>
imagine BIRT would have explicit instructions on adding geometry based<br>
libraries if it did indeed have special support or handling for this<br>
type of data. But really, I don't know.<br>
<br>
To answer your question, you can download the latest version of<br>
postgis-java jdbc extensions from the github releases page<br>
(<a href="https://github.com/postgis/postgis-java/releases" rel="noreferrer" target="_blank">https://github.com/postgis/postgis-java/releases</a>). Please not that<br>
the version of the jdbc extensions does not necessarily match the<br>
PostGIS version that you might be using. The latest version of the<br>
JDBC extensions is compatible with all supported PostGIS versions.<br>
<br>
I'm not sure how you would install the jdbc extensions for BIRT. With<br>
BIRT, do you have to obtain and install the postgres jdbc driver<br>
separately or is it bundled with BIRT? Normally you would add<br>
postgresql jdbc driver jar to the classpath somehow. The PostGIS jdbc<br>
extensions work the same way. When included in the classpath, the<br>
extensions are autoregistered with the jdbc driver, but I'm not sure<br>
how BIRT would actually use the extensions once they are registered.<br>
<br>
Hope that helps!<br>
- Phillip<br>
<br>
On Sat, Feb 9, 2019 at 2:24 AM <<a href="mailto:mfene5101@gmail.com" target="_blank">mfene5101@gmail.com</a>> wrote:<br>
><br>
> This is my first use of the postgis-users list<br>
><br>
><br>
><br>
> I am currently using BIRT reporting tool to connect to Postgresql 9.6. To reporting tool works fine on NON-POSTGIS enabled tables.<br>
><br>
><br>
><br>
> I enabled full tracing on the database to capture the database log as well as the error thrown by BIRT.<br>
><br>
><br>
><br>
> BIRT uses the postgresjdbc driver to connect to Postgresql. I suspect the issue is at the jdbc driver level but I am unsure.<br>
><br>
><br>
><br>
> I have read some google entries regarding the installation of the postgisjdbc driver…but it is unclear which version I should use – or how to install it.<br>
><br>
><br>
><br>
> My current environment is: postgresql9.6 postgis2.3 centos7<br>
><br>
><br>
><br>
> Extract of the postgresql (server side) trace log: (note: I renamed table name to ‘XXX’ to maintain security):<br>
><br>
><br>
><br>
> 2019-02-08 21:49:31.951 PST,"fenem","dbplmspatial",21824,"<a href="http://192.168.1.129:50737" rel="noreferrer" target="_blank">192.168.1.129:50737</a>",5c5e69eb.5540,6,"PARSE",2019-02-08 21:49:31 PST,7/25,0,ERROR,42P01,"relation ""public.XXX"" does not exist",,,,,,"select * from public.XXX",15,,"PostgreSQL JDBC Driver"<br>
><br>
><br>
><br>
><br>
><br>
> Client side error:<br>
><br>
><br>
><br>
> A BIRT exception occurred.<br>
><br>
> Plug-in Provider:Eclipse BIRT Project<br>
><br>
> Plug-in Name:BIRT Data Engine<br>
><br>
> Plug-in ID:org.eclipse.birt.data<br>
><br>
> Version:4.7.0.v201706222054<br>
><br>
> Error Code:odaconsumer.CannotGetResultSetMetaData<br>
><br>
> Error Message:Cannot get the result set metadata.<br>
><br>
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.<br>
><br>
> SQL error #1:ERROR: relation "public.tollbrotherspacifichighranchparcels" does not exist<br>
><br>
> Position: 15<br>
><br>
> ;<br>
><br>
> org.postgresql.util.PSQLException: ERROR: relation "public.XXX" does not exist<br>
><br>
> Position: 15<br>
><br>
><br>
><br>
><br>
><br>
> Thanks in advance for your consideration.<br>
><br>
><br>
><br>
> Michael Fene’<br>
><br>
> _______________________________________________<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><br>
<br>
<br>
------------------------------<br>
<br>
Message: 2<br>
Date: Mon, 11 Feb 2019 09:59:49 +0100<br>
From: Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation<br>
Message-ID:<br>
<CAGAwT=<a href="mailto:3jHTBL64-NEK7ECnfQv33QGV9mE_XK5v8KbEMxE85c4Q@mail.gmail.com" target="_blank">3jHTBL64-NEK7ECnfQv33QGV9mE_XK5v8KbEMxE85c4Q@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
On Fri, 8 Feb 2019 at 23:48, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
<br>
> It has been many years since I last worked with postGIS, I don't do GIS<br>
> for a living anymore but I still like to have fun with it. I'm looking to<br>
> export some old data I have as GeoJSON and projecting it to display in a<br>
> Google Map Overlay. I can get the two individual queries below to work, but<br>
> I cannot figure out how to combine them. I want to transform and output to<br>
> GeoJSON with attributes stored in the DB.<br>
><br>
> SELECT<br>
> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),<br>
> <a href="http://trails_01.name" rel="noreferrer" target="_blank">trails_01.name</a>,<br>
> <a href="http://trails_01.id" rel="noreferrer" target="_blank">trails_01.id</a><br>
> FROM<br>
> topology.trails_01;<br>
><br>
> SELECT jsonb_build_object(<br>
> 'type', 'FeatureCollection',<br>
> 'features', jsonb_agg(features.feature)<br>
> )<br>
> FROM (<br>
> SELECT jsonb_build_object(<br>
> 'type', 'Feature',<br>
> 'id', gid,<br>
> 'geometry', ST_AsGeoJSON(geom)::jsonb,<br>
> 'properties', to_jsonb(inputs) - 'gid' - 'geom'<br>
> ) AS feature<br>
> FROM (SELECT * FROM topology.trails_01) inputs) features;<br>
><br>
> Any help would be appreciated as I start down this path of reteaching<br>
> myself postGIS.<br>
><br>
> Thank you,<br>
> Sean<br>
><br>
<br>
Hi,<br>
<br>
Something like:<br>
<br>
with tmp1 as (<br>
select 'Feature' as "type",<br>
ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918),<br>
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;<br>
<br>
Nicolas<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/03e69cbf/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/03e69cbf/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 3<br>
Date: Mon, 11 Feb 2019 09:59:48 -0500<br>
From: Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation<br>
Message-ID:<br>
<CAF2Du5pyPPc3WL9HOg57iRmh3dwBb2=<a href="mailto:Z3e4K-4cE8B%2Bf_GJM8w@mail.gmail.com" target="_blank">Z3e4K-4cE8B+f_GJM8w@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hi Nicolas,<br>
<br>
Thank you for the response. I have tried working with the example but I'm<br>
unable to get it to run. In the example you provided I keep getting.<br>
<br>
ERROR: missing FROM-clause entry for table "trails_01"<br>
LINE 3: ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom...<br>
<br>
which searches lead me to there is an ordering issue<br>
<br>
Thanks!<br>
Sean<br>
<br>
On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
wrote:<br>
<br>
><br>
> On Fri, 8 Feb 2019 at 23:48, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
><br>
>> It has been many years since I last worked with postGIS, I don't do GIS<br>
>> for a living anymore but I still like to have fun with it. I'm looking to<br>
>> export some old data I have as GeoJSON and projecting it to display in a<br>
>> Google Map Overlay. I can get the two individual queries below to work, but<br>
>> I cannot figure out how to combine them. I want to transform and output to<br>
>> GeoJSON with attributes stored in the DB.<br>
>><br>
>> SELECT<br>
>> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),<br>
>> <a href="http://trails_01.name" rel="noreferrer" target="_blank">trails_01.name</a>,<br>
>> <a href="http://trails_01.id" rel="noreferrer" target="_blank">trails_01.id</a><br>
>> FROM<br>
>> topology.trails_01;<br>
>><br>
>> SELECT jsonb_build_object(<br>
>> 'type', 'FeatureCollection',<br>
>> 'features', jsonb_agg(features.feature)<br>
>> )<br>
>> FROM (<br>
>> SELECT jsonb_build_object(<br>
>> 'type', 'Feature',<br>
>> 'id', gid,<br>
>> 'geometry', ST_AsGeoJSON(geom)::jsonb,<br>
>> 'properties', to_jsonb(inputs) - 'gid' - 'geom'<br>
>> ) AS feature<br>
>> FROM (SELECT * FROM topology.trails_01) inputs) features;<br>
>><br>
>> Any help would be appreciated as I start down this path of reteaching<br>
>> myself postGIS.<br>
>><br>
>> Thank you,<br>
>> Sean<br>
>><br>
><br>
> Hi,<br>
><br>
> Something like:<br>
><br>
> with tmp1 as (<br>
> select 'Feature' as "type",<br>
> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918),<br>
> 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;<br>
><br>
> Nicolas<br>
><br>
> _______________________________________________<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><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/3dd987ff/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/3dd987ff/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 4<br>
Date: Mon, 11 Feb 2019 17:29:19 +0100<br>
From: Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation<br>
Message-ID:<br>
<CAGAwT=<a href="mailto:281%2BPTccALo6RWzHFGuGMoW-cdwsu80VVu%2BV8okqNcQQ@mail.gmail.com" target="_blank">281+PTccALo6RWzHFGuGMoW-cdwsu80VVu+V8okqNcQQ@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hmm sorry.<br>
I pasted your table name without checking.<br>
topology.trails_01 is aliased as "t" in the query:<br>
<br>
with tmp1 as (<br>
select 'Feature' as "type",<br>
ST_AsGeoJSON(ST_Transform(ST_SetSRID(t.geom, 26918), 4326),<br>
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;<br>
<br>
Nicolas<br>
<br>
On Mon, 11 Feb 2019 at 16:00, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
<br>
> Hi Nicolas,<br>
><br>
> Thank you for the response. I have tried working with the example but I'm<br>
> unable to get it to run. In the example you provided I keep getting.<br>
><br>
> ERROR: missing FROM-clause entry for table "trails_01"<br>
> LINE 3: ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom...<br>
><br>
> which searches lead me to there is an ordering issue<br>
><br>
> Thanks!<br>
> Sean<br>
><br>
> On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
> wrote:<br>
><br>
>><br>
>> On Fri, 8 Feb 2019 at 23:48, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
>><br>
>>> It has been many years since I last worked with postGIS, I don't do GIS<br>
>>> for a living anymore but I still like to have fun with it. I'm looking to<br>
>>> export some old data I have as GeoJSON and projecting it to display in a<br>
>>> Google Map Overlay. I can get the two individual queries below to work, but<br>
>>> I cannot figure out how to combine them. I want to transform and output to<br>
>>> GeoJSON with attributes stored in the DB.<br>
>>><br>
>>> SELECT<br>
>>> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),<br>
>>> <a href="http://trails_01.name" rel="noreferrer" target="_blank">trails_01.name</a>,<br>
>>> <a href="http://trails_01.id" rel="noreferrer" target="_blank">trails_01.id</a><br>
>>> FROM<br>
>>> topology.trails_01;<br>
>>><br>
>>> SELECT jsonb_build_object(<br>
>>> 'type', 'FeatureCollection',<br>
>>> 'features', jsonb_agg(features.feature)<br>
>>> )<br>
>>> FROM (<br>
>>> SELECT jsonb_build_object(<br>
>>> 'type', 'Feature',<br>
>>> 'id', gid,<br>
>>> 'geometry', ST_AsGeoJSON(geom)::jsonb,<br>
>>> 'properties', to_jsonb(inputs) - 'gid' - 'geom'<br>
>>> ) AS feature<br>
>>> FROM (SELECT * FROM topology.trails_01) inputs) features;<br>
>>><br>
>>> Any help would be appreciated as I start down this path of reteaching<br>
>>> myself postGIS.<br>
>>><br>
>>> Thank you,<br>
>>> Sean<br>
>>><br>
>><br>
>> Hi,<br>
>><br>
>> Something like:<br>
>><br>
>> with tmp1 as (<br>
>> select 'Feature' as "type",<br>
>> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918),<br>
>> 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;<br>
>><br>
>> Nicolas<br>
>><br>
>> _______________________________________________<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><br>
><br>
> _______________________________________________<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><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/9b651e85/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/9b651e85/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 5<br>
Date: Mon, 11 Feb 2019 11:37:32 -0500<br>
From: Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation<br>
Message-ID:<br>
<<a href="mailto:CAF2Du5qEP_R2mFFHZTTcXKH2bVnWaMarzZNPZo8_u2%2BfmuQXNw@mail.gmail.com" target="_blank">CAF2Du5qEP_R2mFFHZTTcXKH2bVnWaMarzZNPZo8_u2+fmuQXNw@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
That worked. Thank you very much.<br>
<br>
Sean<br>
<br>
On Mon, Feb 11, 2019 at 11:29 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
wrote:<br>
<br>
> Hmm sorry.<br>
> I pasted your table name without checking.<br>
> topology.trails_01 is aliased as "t" in the query:<br>
><br>
> with tmp1 as (<br>
> select 'Feature' as "type",<br>
> ST_AsGeoJSON(ST_Transform(ST_SetSRID(t.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;<br>
><br>
> Nicolas<br>
><br>
> On Mon, 11 Feb 2019 at 16:00, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
><br>
>> Hi Nicolas,<br>
>><br>
>> Thank you for the response. I have tried working with the example but I'm<br>
>> unable to get it to run. In the example you provided I keep getting.<br>
>><br>
>> ERROR: missing FROM-clause entry for table "trails_01"<br>
>> LINE 3: ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom...<br>
>><br>
>> which searches lead me to there is an ordering issue<br>
>><br>
>> Thanks!<br>
>> Sean<br>
>><br>
>> On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
>> wrote:<br>
>><br>
>>><br>
>>> On Fri, 8 Feb 2019 at 23:48, Sean Montague <<a href="mailto:couloir007@gmail.com" target="_blank">couloir007@gmail.com</a>> wrote:<br>
>>><br>
>>>> It has been many years since I last worked with postGIS, I don't do GIS<br>
>>>> for a living anymore but I still like to have fun with it. I'm looking to<br>
>>>> export some old data I have as GeoJSON and projecting it to display in a<br>
>>>> Google Map Overlay. I can get the two individual queries below to work, but<br>
>>>> I cannot figure out how to combine them. I want to transform and output to<br>
>>>> GeoJSON with attributes stored in the DB.<br>
>>>><br>
>>>> SELECT<br>
>>>> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918), 4326)),<br>
>>>> <a href="http://trails_01.name" rel="noreferrer" target="_blank">trails_01.name</a>,<br>
>>>> <a href="http://trails_01.id" rel="noreferrer" target="_blank">trails_01.id</a><br>
>>>> FROM<br>
>>>> topology.trails_01;<br>
>>>><br>
>>>> SELECT jsonb_build_object(<br>
>>>> 'type', 'FeatureCollection',<br>
>>>> 'features', jsonb_agg(features.feature)<br>
>>>> )<br>
>>>> FROM (<br>
>>>> SELECT jsonb_build_object(<br>
>>>> 'type', 'Feature',<br>
>>>> 'id', gid,<br>
>>>> 'geometry', ST_AsGeoJSON(geom)::jsonb,<br>
>>>> 'properties', to_jsonb(inputs) - 'gid' - 'geom'<br>
>>>> ) AS feature<br>
>>>> FROM (SELECT * FROM topology.trails_01) inputs) features;<br>
>>>><br>
>>>> Any help would be appreciated as I start down this path of reteaching<br>
>>>> myself postGIS.<br>
>>>><br>
>>>> Thank you,<br>
>>>> Sean<br>
>>>><br>
>>><br>
>>> Hi,<br>
>>><br>
>>> Something like:<br>
>>><br>
>>> with tmp1 as (<br>
>>> select 'Feature' as "type",<br>
>>> ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom, 26918),<br>
>>> 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;<br>
>>><br>
>>> Nicolas<br>
>>><br>
>>> _______________________________________________<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><br>
>><br>
>> _______________________________________________<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><br>
><br>
> _______________________________________________<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><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/81df22fc/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/81df22fc/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Subject: Digest Footer<br>
<br>
_______________________________________________<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><br>
<br>
------------------------------<br>
<br>
End of postgis-users Digest, Vol 204, Issue 3<br>
*********************************************<br>
</blockquote></div>