[postgis-users] postgis-users Digest, Vol 204, Issue 3
Michael Fene
mfene5101 at gmail.com
Mon Feb 11 19:52:43 PST 2019
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 <postgis-users-request at lists.osgeo.org>
wrote:
> Send postgis-users mailing list submissions to
> postgis-users at lists.osgeo.org
>
> 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
> postgis-users-request at lists.osgeo.org
>
> You can reach the person managing the list at
> postgis-users-owner at lists.osgeo.org
>
> 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 <phillip.w.g.ross+postgis-users at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> 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=
> jtNTP1w at mail.gmail.com>
> 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 <mfene5101 at gmail.com> 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
> > postgis-users at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> ------------------------------
>
> Message: 2
> Date: Mon, 11 Feb 2019 09:59:49 +0100
> From: Nicolas Ribot <nicolas.ribot at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation
> Message-ID:
> <CAGAwT=
> 3jHTBL64-NEK7ECnfQv33QGV9mE_XK5v8KbEMxE85c4Q at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> 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-0001.html
> >
>
> ------------------------------
>
> Message: 3
> Date: Mon, 11 Feb 2019 09:59:48 -0500
> From: Sean Montague <couloir007 at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation
> Message-ID:
> <CAF2Du5pyPPc3WL9HOg57iRmh3dwBb2=
> Z3e4K-4cE8B+f_GJM8w at mail.gmail.com>
> 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 <nicolas.ribot at gmail.com>
> wrote:
>
> >
> > 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
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > 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 <nicolas.ribot at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation
> Message-ID:
> <CAGAwT=
> 281+PTccALo6RWzHFGuGMoW-cdwsu80VVu+V8okqNcQQ at mail.gmail.com>
> 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 <couloir007 at gmail.com> 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 <nicolas.ribot at gmail.com>
> > wrote:
> >
> >>
> >> 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
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > 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 <couloir007 at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Selecting as GeoJSON with Transformation
> Message-ID:
> <
> CAF2Du5qEP_R2mFFHZTTcXKH2bVnWaMarzZNPZo8_u2+fmuQXNw at 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 <nicolas.ribot at gmail.com>
> 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 <couloir007 at gmail.com>
> 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 <nicolas.ribot at gmail.com>
> >> wrote:
> >>
> >>>
> >>> 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
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at lists.osgeo.org
> >>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > 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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ------------------------------
>
> End of postgis-users Digest, Vol 204, Issue 3
> *********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190211/8c6f8b5f/attachment.html>
More information about the postgis-users
mailing list