[Geoserver-users] postgis SQL view - geography column not recognised

Joshua_Groom · March 2, 2012, 4:41am

Hi,
I am using

Geoserver 2.1.2 (have replicated this issue on 2.1.3 as well)
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
POSTGIS=“1.5.3” GEOS=“3.2.2-CAPI-1.6.2” PROJ=“Rel. 4.7.1, 23 September 2009” LIBXML=“2.7.8” USE_STATS
Sun Java™ SE Runtime Environment (build 1.6.0_26-b03)

I have no problem creating layers by directly publishing tables from Postgis or Oracle 11g and can create SQL Views in oracle 11g but not Postgis.

I am having issues with SQL views connecting to a Postgis datasource with a table defined like so:

CREATE TABLE lightning (
id integer primary key,
timestamp text,
amplitude real,
duration integer,
type text,
strike_location geography(POINT, 4326)
);

CREATE INDEX lightning_gix ON lightning USING GIST ( strike_location );

However when I try to create an SQL view, for example:

select * from lightning

Then the geography column strike_location is not picked up and I see the log message:

WARN [geotools.jdbc] - Could not find mapping for ‘strike_location’, ignoring the column and setting the feature type read only

The layer can be created if I manually specify SRS and bounding Box it does not appear to have a location associated with it, layer preview doesn’t work and GML contains no lat lon information.

Any help would be appreciated.

Thanks,
Josh

Ben_Caradoc-Davies · March 2, 2012, 5:09am

Joshua,

does the user you are connecting with have permission to select from the view geography_columns (introduced when support for geographies was added)?

Can you "select * from geography_columns" as this user?

For GeoServer to recognise geometry columns in views, they must be registered in geometry_columns. I suspect a similar rule may apply to geography columns.

Kind regards,
Ben.

On 02/03/12 12:41, Joshua Groom wrote:

Hi,
I am using
- Geoserver 2.1.2 (have replicated this issue on 2.1.3 as well)
- PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
- POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS
- Sun Java(TM) SE Runtime Environment (build 1.6.0_26-b03)

I have no problem creating layers by directly publishing tables from Postgis or Oracle 11g and can create SQL Views in oracle 11g but not Postgis.

I am having issues with SQL views connecting to a Postgis datasource with a table defined like so:

CREATE TABLE lightning (
   id integer primary key,
   timestamp text,
   amplitude real,
   duration integer,
   type text,
   strike_location geography(POINT, 4326)
);

CREATE INDEX lightning_gix ON lightning USING GIST ( strike_location );

However when I try to create an SQL view, for example:

select * from lightning

Then the geography column strike_location is not picked up and I see the log message:

WARN [geotools.jdbc] - Could not find mapping for 'strike_location', ignoring the column and setting the feature type read only

The layer can be created if I manually specify SRS and bounding Box it does not appear to have a location associated with it, layer preview doesn't work and GML contains no lat lon information.

Any help would be appreciated.

Thanks,
Josh

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Joshua_Groom · March 5, 2012, 9:01pm

Hi,
Yes the postgres user which geoserver is using can see the table and the contents are:

Thanks,
Josh

________________________________________
From: Ben Caradoc-Davies [Ben.Caradoc-Davies@anonymised.com]
Sent: Friday, 2 March 2012 6:09 p.m.
To: Joshua Groom
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] postgis SQL view - geography column not recognised

Joshua,

does the user you are connecting with have permission to select from the
view geography_columns (introduced when support for geographies was added)?

Can you "select * from geography_columns" as this user?

For GeoServer to recognise geometry columns in views, they must be
registered in geometry_columns. I suspect a similar rule may apply to
geography columns.

Kind regards,
Ben.

On 02/03/12 12:41, Joshua Groom wrote:

Hi,
I am using
- Geoserver 2.1.2 (have replicated this issue on 2.1.3 as well)
- PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
- POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS
- Sun Java(TM) SE Runtime Environment (build 1.6.0_26-b03)

I have no problem creating layers by directly publishing tables from Postgis or Oracle 11g and can create SQL Views in oracle 11g but not Postgis.

I am having issues with SQL views connecting to a Postgis datasource with a table defined like so:

CREATE TABLE lightning (
   id integer primary key,
   timestamp text,
   amplitude real,
   duration integer,
   type text,
   strike_location geography(POINT, 4326)
);

CREATE INDEX lightning_gix ON lightning USING GIST ( strike_location );

However when I try to create an SQL view, for example:

select * from lightning

Then the geography column strike_location is not picked up and I see the log message:

WARN [geotools.jdbc] - Could not find mapping for 'strike_location', ignoring the column and setting the feature type read only

The layer can be created if I manually specify SRS and bounding Box it does not appear to have a location associated with it, layer preview doesn't work and GML contains no lat lon information.

Any help would be appreciated.

Thanks,
Josh

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Joshua_Groom · March 6, 2012, 5:23am

Is this a geotools bug?

When creating a layer from a table the strike_location geography binding is correctly identified by the org.geotools.jdbc.JDBCFeatureSource class as com.vividsolutions.jts.geom.Point. This happens because the SQLDialect subclass org.geotools.data.postgis.PostGisDialect is called to determine the mapping

dialect.getMapping(columns, cx) is called, this method looks up the geography_columns in geography_columns table.

In the SQL view case the sqlType is 1111 but the binding is null. There is different logic to retrieve the binding it uses
dialect.registerSqlTypeNameToClassMappings(sqlTypeNameToClassMappings) but this hashmap doesn't have a mapping for the "geography" type.

This seems like a geotools bug, and from my quick look at the trunk it doesn't look like it has been fixed.

So any suggestions for known good versions of geotools/geoserver to try?

The reason I need this functionality is to allow me to publish a layer where users can do spatial and time based queries, sending the time parameter into the SQL view would let me achieve that. Open to work arounds...

Thanks,
Josh

________________________________________
From: Joshua Groom [Joshua.Groom@anonymised.com]
Sent: Tuesday, 6 March 2012 10:01 a.m.
To: Ben Caradoc-Davies
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] postgis SQL view - geography column not recognised

Hi,
Yes the postgres user which geoserver is using can see the table and the contents are:

Thanks,
Josh

________________________________________
From: Ben Caradoc-Davies [Ben.Caradoc-Davies@anonymised.com]
Sent: Friday, 2 March 2012 6:09 p.m.
To: Joshua Groom
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] postgis SQL view - geography column not recognised

Joshua,

does the user you are connecting with have permission to select from the
view geography_columns (introduced when support for geographies was added)?

Can you "select * from geography_columns" as this user?

For GeoServer to recognise geometry columns in views, they must be
registered in geometry_columns. I suspect a similar rule may apply to
geography columns.

Kind regards,
Ben.

On 02/03/12 12:41, Joshua Groom wrote:

Hi,
I am using
- Geoserver 2.1.2 (have replicated this issue on 2.1.3 as well)
- PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
- POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS
- Sun Java(TM) SE Runtime Environment (build 1.6.0_26-b03)

I have no problem creating layers by directly publishing tables from Postgis or Oracle 11g and can create SQL Views in oracle 11g but not Postgis.

I am having issues with SQL views connecting to a Postgis datasource with a table defined like so:

CREATE TABLE lightning (
   id integer primary key,
   timestamp text,
   amplitude real,
   duration integer,
   type text,
   strike_location geography(POINT, 4326)
);

CREATE INDEX lightning_gix ON lightning USING GIST ( strike_location );

However when I try to create an SQL view, for example:

select * from lightning

Then the geography column strike_location is not picked up and I see the log message:

WARN [geotools.jdbc] - Could not find mapping for 'strike_location', ignoring the column and setting the feature type read only

The layer can be created if I manually specify SRS and bounding Box it does not appear to have a location associated with it, layer preview doesn't work and GML contains no lat lon information.

Any help would be appreciated.

Thanks,
Josh

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Andrea_Aime4 · March 6, 2012, 9:14am

On Tue, Mar 6, 2012 at 6:23 AM, Joshua Groom <Joshua.Groom@…4709…> wrote:

Is this a geotools bug?

Quite likely, the test harness for geography columns has no checks for sql views

When creating a layer from a table the strike_location geography binding is correctly identified by the org.geotools.jdbc.JDBCFeatureSource class as com.vividsolutions.jts.geom.Point. This happens because the SQLDialect subclass org.geotools.data.postgis.PostGisDialect is called to determine the mapping

dialect.getMapping(columns, cx) is called, this method looks up the geography_columns in geography_columns table.

In the SQL view case the sqlType is 1111 but the binding is null. There is different logic to retrieve the binding it uses
dialect.registerSqlTypeNameToClassMappings(sqlTypeNameToClassMappings) but this hashmap doesn’t have a mapping for the “geography” type.

This seems like a geotools bug, and from my quick look at the trunk it doesn’t look like it has been fixed.

So any suggestions for known good versions of geotools/geoserver to try?

The suggestion would be to:

do a bug report
if you want to have a fix in the next release (2.1.4), do it yourself within a few days:
add a junit test, fix the bug, submit a patch to geotools to have your changes
incorporated (mind, almost no fixes get in without a test, it’s not optional)

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

Joshua_Groom · March 7, 2012, 12:14am

Ok,
Well I will aim to send a patch to geotools then. So I gather that there is an upcoming release of geoserver that will include the 2.7.5 release of geotools.

Is http://svn.osgeo.org/geotools/trunk what I should develop my fix against?

Thanks,
Josh

From: andrea.aime@anonymised.com [andrea.aime@anonymised.com84…] on behalf of Andrea Aime [andrea.aime@anonymised.com]
Sent: Tuesday, 6 March 2012 10:14 p.m.
To: Joshua Groom
Cc: Ben Caradoc-Davies; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] postgis SQL view - geography column not recognised

On Tue, Mar 6, 2012 at 6:23 AM, Joshua Groom <Joshua.Groom@anonymised.com> wrote:

Is this a geotools bug?

Quite likely, the test harness for geography columns has no checks for sql views

When creating a layer from a table the strike_location geography binding is correctly identified by the org.geotools.jdbc.JDBCFeatureSource class as com.vividsolutions.jts.geom.Point. This happens because the SQLDialect subclass org.geotools.data.postgis.PostGisDialect is called to determine the mapping

dialect.getMapping(columns, cx) is called, this method looks up the geography_columns in geography_columns table.

In the SQL view case the sqlType is 1111 but the binding is null. There is different logic to retrieve the binding it uses
dialect.registerSqlTypeNameToClassMappings(sqlTypeNameToClassMappings) but this hashmap doesn’t have a mapping for the “geography” type.

This seems like a geotools bug, and from my quick look at the trunk it doesn’t look like it has been fixed.

So any suggestions for known good versions of geotools/geoserver to try?

The suggestion would be to:

do a bug report
if you want to have a fix in the next release (2.1.4), do it yourself within a few days:
add a junit test, fix the bug, submit a patch to geotools to have your changes
incorporated (mind, almost no fixes get in without a test, it’s not optional)

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

Ben_Caradoc-Davies · March 7, 2012, 3:40am

On 07/03/12 08:14, Joshua Groom wrote:

Well I will aim to send a patch to geotools then. So I gather that there is an upcoming release of geoserver that will include the 2.7.5 release of geotools.
Is http://svn.osgeo.org/geotools/trunk what I should develop my fix against?

Yes, please, but also backport to the stable branch, from which 2.7.5 will be released (in GeoServer 2.1.4[?]):
http://svn.osgeo.org/geotools/branches/2.7.x/

Trunk will become GeoTools 8.0 (the leading "2." is being dropped) and be included with GeoServer 2.2.

Kind regards,

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Joshua_Groom · March 13, 2012, 10:20pm

Hi,
The fix to this problem for me is just adding a mapping to PostGISDialect.java however the test suite is a little harder to get running so I have not yet submitted a patch.

For anyone who has been following this:
There is a work around for this issue though. Just manually edit the featuretype.xml file in geoserver data_dir to contain the following snippet that defines the geometry for the column:

<metadata>
    <entry key="JDBC_VIRTUAL_TABLE">
      <virtualTable>
        <name>test-dialect</name>
        <sql>select * from lightning</sql>
        <geometry>
          <name>strike_location</name>
          <type>Point</type>
          <srid>4326</srid>
        </geometry>
      </virtualTable>
    </entry>
  </metadata>

When the layer is reloaded you will be able to edit it as normal.

Josh

________________________________________
From: Ben Caradoc-Davies [Ben.Caradoc-Davies@anonymised.com]
Sent: Wednesday, 7 March 2012 4:40 p.m.
To: Joshua Groom
Cc: Andrea Aime; geoserver-users@lists.sourceforge.net; geotools-devel@anonymised.coms.sourceforge.net
Subject: Re: [Geoserver-users] postgis SQL view - geography column not recognised

On 07/03/12 08:14, Joshua Groom wrote:

Well I will aim to send a patch to geotools then. So I gather that there is an upcoming release of geoserver that will include the 2.7.5 release of geotools.
Is http://svn.osgeo.org/geotools/trunk what I should develop my fix against?

Yes, please, but also backport to the stable branch, from which 2.7.5
will be released (in GeoServer 2.1.4[?]):
http://svn.osgeo.org/geotools/branches/2.7.x/

Trunk will become GeoTools 8.0 (the leading "2." is being dropped) and
be included with GeoServer 2.2.

Kind regards,

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Andrea_Aime4 · March 14, 2012, 8:55am

On Tue, Mar 13, 2012 at 11:20 PM, Joshua Groom
<Joshua.Groom@anonymised.com> wrote:

Hi,
The fix to this problem for me is just adding a mapping to PostGISDialect.java however the test suite is a little harder to get running so I have not yet submitted a patch.

In order to run the tests you need to play a postgis.properties file
in your home directory's .geotools folder (e.g.,
/home/aaime/.geotools/postgis.properties for
me, on a Linux system, on other OS the position will be different) and
fill it with enough info to connect to an empty
postgis database that the tests will run onto (creating tables and so on).
The contents of the file look like the following:

passwd=pwd
password=pwd
user=cite
port=5432
url=jdbc\:postgresql\:gttest
host=localhost
database=gttest
driver=org.postgresql.Driver

(yes, there is redundant info there, there is a jira to fix that,
which nobody has worked on so far)

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-------------------------------------------------------