Resolved: Turns out that the jdbc driver does not like capital letters in the table/view name. Kind regards, Michael Fene'
On Mon, Feb 11, 2019 at 12:00 PM <[email protected]> wrote: > Send postgis-users mailing list submissions to > [email protected] > > To subscribe or unsubscribe via the World Wide Web, visit > https://lists.osgeo.org/mailman/listinfo/postgis-users > or, via email, send a message with subject or body 'help' to > [email protected] > > You can reach the person managing the list at > [email protected] > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of postgis-users digest..." > > > Today's Topics: > > 1. Re: Attempting to Connect to POSTGIS Database Table from BIRT > To Create a report from an POSTGIS attribute table - connection > throws error (with only spatial enabled tables) (Phillip Ross) > 2. Re: Selecting as GeoJSON with Transformation (Nicolas Ribot) > 3. Re: Selecting as GeoJSON with Transformation (Sean Montague) > 4. Re: Selecting as GeoJSON with Transformation (Nicolas Ribot) > 5. Re: Selecting as GeoJSON with Transformation (Sean Montague) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 10 Feb 2019 23:22:21 -0500 > From: Phillip Ross <[email protected]> > To: PostGIS Users Discussion <[email protected]> > Subject: Re: [postgis-users] Attempting to Connect to POSTGIS Database > Table from BIRT To Create a report from an POSTGIS attribute table > - > connection throws error (with only spatial enabled tables) > Message-ID: > <CAKcj57AkSYKijq_rjaVJUwJOu21hpGHFHH8EyaqJaZ= > [email protected]> > Content-Type: text/plain; charset="UTF-8" > > Hi Michael, > > I'm not familiar with BIRT, but the error you pasted hints that it may > be tripping up on the fact that it thinks a table doesn't exist: > ERROR: relation "public.tollbrotherspacifichighranchparcels" does not > exist. > > The postgis-java jdbc extensions are only necessary for applications > which want to use explicitly use java-based geometry types which > general applications such as BIRT generally do not do. I would > imagine BIRT would have explicit instructions on adding geometry based > libraries if it did indeed have special support or handling for this > type of data. But really, I don't know. > > To answer your question, you can download the latest version of > postgis-java jdbc extensions from the github releases page > (https://github.com/postgis/postgis-java/releases). Please not that > the version of the jdbc extensions does not necessarily match the > PostGIS version that you might be using. The latest version of the > JDBC extensions is compatible with all supported PostGIS versions. > > I'm not sure how you would install the jdbc extensions for BIRT. With > BIRT, do you have to obtain and install the postgres jdbc driver > separately or is it bundled with BIRT? Normally you would add > postgresql jdbc driver jar to the classpath somehow. The PostGIS jdbc > extensions work the same way. When included in the classpath, the > extensions are autoregistered with the jdbc driver, but I'm not sure > how BIRT would actually use the extensions once they are registered. > > Hope that helps! > - Phillip > > On Sat, Feb 9, 2019 at 2:24 AM <[email protected]> wrote: > > > > This is my first use of the postgis-users list > > > > > > > > I am currently using BIRT reporting tool to connect to Postgresql 9.6. > To reporting tool works fine on NON-POSTGIS enabled tables. > > > > > > > > I enabled full tracing on the database to capture the database log as > well as the error thrown by BIRT. > > > > > > > > BIRT uses the postgresjdbc driver to connect to Postgresql. I suspect > the issue is at the jdbc driver level but I am unsure. > > > > > > > > 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. > > > > > > > > My current environment is: postgresql9.6 postgis2.3 centos7 > > > > > > > > Extract of the postgresql (server side) trace log: (note: I renamed > table name to ‘XXX’ to maintain security): > > > > > > > > 2019-02-08 21:49:31.951 PST,"fenem","dbplmspatial",21824," > 192.168.1.129:50737",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" > > > > > > > > > > > > Client side error: > > > > > > > > A BIRT exception occurred. > > > > Plug-in Provider:Eclipse BIRT Project > > > > Plug-in Name:BIRT Data Engine > > > > Plug-in ID:org.eclipse.birt.data > > > > Version:4.7.0.v201706222054 > > > > Error Code:odaconsumer.CannotGetResultSetMetaData > > > > Error Message:Cannot get the result set metadata. > > > > org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement > does not return a ResultSet object. > > > > SQL error #1:ERROR: relation > "public.tollbrotherspacifichighranchparcels" does not exist > > > > Position: 15 > > > > ; > > > > org.postgresql.util.PSQLException: ERROR: relation "public.XXX" does > not exist > > > > Position: 15 > > > > > > > > > > > > Thanks in advance for your consideration. > > > > > > > > Michael Fene’ > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > https://lists.osgeo.org/mailman/listinfo/postgis-users > > > ------------------------------ > > Message: 2 > Date: Mon, 11 Feb 2019 09:59:49 +0100 > From: Nicolas Ribot <[email protected]> > To: PostGIS Users Discussion <[email protected]> > Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation > Message-ID: > <CAGAwT= > [email protected]> > Content-Type: text/plain; charset="utf-8" > > On Fri, 8 Feb 2019 at 23:48, Sean Montague <[email protected]> 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-0001.html > > > > ------------------------------ > > Message: 3 > Date: Mon, 11 Feb 2019 09:59:48 -0500 > From: Sean Montague <[email protected]> > To: PostGIS Users Discussion <[email protected]> > Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation > Message-ID: > <CAF2Du5pyPPc3WL9HOg57iRmh3dwBb2= > [email protected]> > Content-Type: text/plain; charset="utf-8" > > 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 <[email protected]> > wrote: > > > > > On Fri, 8 Feb 2019 at 23:48, Sean Montague <[email protected]> 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 > > [email protected] > > 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/3dd987ff/attachment-0001.html > > > > ------------------------------ > > Message: 4 > Date: Mon, 11 Feb 2019 17:29:19 +0100 > From: Nicolas Ribot <[email protected]> > To: PostGIS Users Discussion <[email protected]> > Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation > Message-ID: > <CAGAwT= > [email protected]> > Content-Type: text/plain; charset="utf-8" > > 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 <[email protected]> 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 <[email protected]> > > wrote: > > > >> > >> On Fri, 8 Feb 2019 at 23:48, Sean Montague <[email protected]> > 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 > >> [email protected] > >> https://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > 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-0001.html > > > > ------------------------------ > > Message: 5 > Date: Mon, 11 Feb 2019 11:37:32 -0500 > From: Sean Montague <[email protected]> > To: PostGIS Users Discussion <[email protected]> > Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation > Message-ID: > < > caf2du5qep_r2mffhzttcxkh2bvnwamarzznpzo8_u2+fmuq...@mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > That worked. Thank you very much. > > Sean > > On Mon, Feb 11, 2019 at 11:29 AM Nicolas Ribot <[email protected]> > wrote: > > > 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 <[email protected]> > 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 <[email protected]> > >> wrote: > >> > >>> > >>> On Fri, 8 Feb 2019 at 23:48, Sean Montague <[email protected]> > 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 > >>> [email protected] > >>> https://lists.osgeo.org/mailman/listinfo/postgis-users > >> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> https://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > 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/81df22fc/attachment-0001.html > > > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users > > ------------------------------ > > End of postgis-users Digest, Vol 204, Issue 3 > ********************************************* >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
