geotools-gt2-users

[Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Hi Guys,

I ingested a couple of shape files into PostGIS and after running a 
query against it, an exception gets thrown even if the query returns a 
proper response.

Here are the tables I have in my DB:
geo=# \dt;
              List of relations
  Schema |       Name        | Type  | Owner
--------+-------------------+-------+-------
  public | geometry_columns  | table | gis
  public | lga07aaust_region | table | gis
  public | spatial_ref_sys   | table | gis
  public | ssc06aaust_region | table | gis

Here's the code snippet that I'm trying to execute:
         String typeName = "ssc06aaust_region";
         FeatureSource source = dataStore.getFeatureSource(typeName);

         Filter filter = CQL
                 .toFilter("name_2006 = 'Melbourne'");
         FeatureCollection<SimpleFeatureType, SimpleFeature> features = 
source
                 .getFeatures(filter);
         System.out.println(features.size());

And the exception that it throws back to the terminal:
May 24, 2011 2:55:14 PM org.geotools.jdbc.JDBCFeatureSource buildFeatureType
WARNING: Error occured determing srid for ssc06aaust_region.wkb_geometry
org.postgresql.util.PSQLException: ERROR: permission denied for relation 
geography_columns
     at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
     at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
     at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
     at 
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
     at 
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
     at 
org.geotools.data.postgis.PostGISDialect.getGeometrySRID(PostGISDialect.java:386)
     at 
org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:291)
     at 
org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.java:767)
     at 
org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:384)
     at 
org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:349)
     at 
org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:102)
     at au.org.aurin.wfs.client.QueryLab.filterFeatures(QueryLab.java:116)
     at au.org.aurin.wfs.client.QueryLab.connect(QueryLab.java:103)
     at au.org.aurin.wfs.client.QueryLab.<init>(QueryLab.java:67)
     at au.org.aurin.wfs.client.QueryLab.main(QueryLab.java:54)

Note that I still get "1" as the output in stdout even if the exception 
gets thrown in stderr.

Any ideas?

Thanks,
Gerson

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Jody G. <jod...@gm...>]

Looks like the user/password you used when connecting via geotools does not have acess to the PostGIS book keeping tables (geometry_columns table etc...). Try checking with PgAdmin?

-- 
Jody Garnett

On Tuesday, 24 May 2011 at 4:55 PM, Gerson Galang wrote: 
> Hi Guys,
> 
> I ingested a couple of shape files into PostGIS and after running a 
> query against it, an exception gets thrown even if the query returns a 
> proper response.
> 
> Here are the tables I have in my DB:
> geo=# \dt;
>  List of relations
>  Schema | Name | Type | Owner
> --------+-------------------+-------+-------
>  public | geometry_columns | table | gis
>  public | lga07aaust_region | table | gis
>  public | spatial_ref_sys | table | gis
>  public | ssc06aaust_region | table | gis
> 
> Here's the code snippet that I'm trying to execute:
>  String typeName = "ssc06aaust_region";
>  FeatureSource source = dataStore.getFeatureSource(typeName);
> 
>  Filter filter = CQL
>  .toFilter("name_2006 = 'Melbourne'");
>  FeatureCollection<SimpleFeatureType, SimpleFeature> features = 
> source
>  .getFeatures(filter);
>  System.out.println(features.size());
> 
> And the exception that it throws back to the terminal:
> May 24, 2011 2:55:14 PM org.geotools.jdbc.JDBCFeatureSource buildFeatureType
> WARNING: Error occured determing srid for ssc06aaust_region.wkb_geometry
> org.postgresql.util.PSQLException: ERROR: permission denied for relation 
> geography_columns
>  at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
>  at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
>  at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>  at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
>  at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
>  at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
>  at 
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
>  at 
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
>  at 
> org.geotools.data.postgis.PostGISDialect.getGeometrySRID(PostGISDialect.java:386)
>  at 
> org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:291)
>  at 
> org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.java:767)
>  at 
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:384)
>  at 
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:349)
>  at 
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:102)
>  at au.org.aurin.wfs.client.QueryLab.filterFeatures(QueryLab.java:116)
>  at au.org.aurin.wfs.client.QueryLab.connect(QueryLab.java:103)
>  at au.org.aurin.wfs.client.QueryLab.<init>(QueryLab.java:67)
>  at au.org.aurin.wfs.client.QueryLab.main(QueryLab.java:54)
> 
> Note that I still get "1" as the output in stdout even if the exception 
> gets thrown in stderr.
> 
> Any ideas?
> 
> Thanks,
> Gerson
> 
> ------------------------------------------------------------------------------
> vRanger cuts backup time in half-while increasing security.
> With the market-leading solution for virtual backup and recovery, 
> you get blazing-fast, flexible, and affordable data protection.
> Download your free trial now. 
> http://p.sf.net/sfu/quest-d2dcopy1
> _______________________________________________
> Geotools-gt2-users mailing list
> Geo...@li...
> https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
> 

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

alter view geography_columns owner to gis;

You can see views with \dv (no semicolon required as this is a postgres 
command and not SQL).


On 24/05/11 14:55, Gerson Galang wrote:
> Hi Guys,
>
> I ingested a couple of shape files into PostGIS and after running a
> query against it, an exception gets thrown even if the query returns a
> proper response.
>
> Here are the tables I have in my DB:
> geo=# \dt;
>                List of relations
>    Schema |       Name        | Type  | Owner
> --------+-------------------+-------+-------
>    public | geometry_columns  | table | gis
>    public | lga07aaust_region | table | gis
>    public | spatial_ref_sys   | table | gis
>    public | ssc06aaust_region | table | gis
>
> Here's the code snippet that I'm trying to execute:
>           String typeName = "ssc06aaust_region";
>           FeatureSource source = dataStore.getFeatureSource(typeName);
>
>           Filter filter = CQL
>                   .toFilter("name_2006 = 'Melbourne'");
>           FeatureCollection<SimpleFeatureType, SimpleFeature>  features =
> source
>                   .getFeatures(filter);
>           System.out.println(features.size());
>
> And the exception that it throws back to the terminal:
> May 24, 2011 2:55:14 PM org.geotools.jdbc.JDBCFeatureSource buildFeatureType
> WARNING: Error occured determing srid for ssc06aaust_region.wkb_geometry
> org.postgresql.util.PSQLException: ERROR: permission denied for relation
> geography_columns
>       at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
>       at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
>       at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>       at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
>       at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
>       at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
>       at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
>       at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
>       at
> org.geotools.data.postgis.PostGISDialect.getGeometrySRID(PostGISDialect.java:386)
>       at
> org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:291)
>       at
> org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.java:767)
>       at
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:384)
>       at
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:349)
>       at
> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:102)
>       at au.org.aurin.wfs.client.QueryLab.filterFeatures(QueryLab.java:116)
>       at au.org.aurin.wfs.client.QueryLab.connect(QueryLab.java:103)
>       at au.org.aurin.wfs.client.QueryLab.<init>(QueryLab.java:67)
>       at au.org.aurin.wfs.client.QueryLab.main(QueryLab.java:54)
>
> Note that I still get "1" as the output in stdout even if the exception
> gets thrown in stderr.
>
> Any ideas?
>
> Thanks,
> Gerson
>
> ------------------------------------------------------------------------------
> vRanger cuts backup time in half-while increasing security.
> With the market-leading solution for virtual backup and recovery,
> you get blazing-fast, flexible, and affordable data protection.
> Download your free trial now.
> http://p.sf.net/sfu/quest-d2dcopy1
> _______________________________________________
> Geotools-gt2-users mailing list
> Geo...@li...
> https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
>

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

I think your request succeeded, but I suspect that, because of the error 
encountered while trying to determine the SRID, your geometries won't 
have a SRID until you grant at a minimum read access to 
geography_columns, and so spatial operations including reprojection will 
likely fail.

On 24/05/11 14:55, Gerson Galang wrote:
> Note that I still get "1" as the output in stdout even if the exception
> gets thrown in stderr.

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Thanks Ben and Jody for your help. :-)

The issue was really due to the permissions on the geography_columns 
view. I didn't realise that PostGIS uses a view in addition to the two 
tables it added in the database.

Cheers,
Gerson

On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
> I think your request succeeded, but I suspect that, because of the 
> error encountered while trying to determine the SRID, your geometries 
> won't have a SRID until you grant at a minimum read access to 
> geography_columns, and so spatial operations including reprojection 
> will likely fail.
>
> On 24/05/11 14:55, Gerson Galang wrote:
>> Note that I still get "1" as the output in stdout even if the exception
>> gets thrown in stderr.
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Geography support is new in PostGIS 1.5.

In my view (no pun intended), GeoTools behaviour could have been better 
in this case: if it could not read geography_columns, it should have 
used the information it could access in geometry_columns if possible. I 
have filed a Jira issue to request this change:
https://jira.codehaus.org/browse/GEOT-3603

Gerson, I'm assuming that your table was not using geographies, just 
geometries?

Kind regards,
Ben.

On 25/05/11 07:16, Gerson Galang wrote:
> Thanks Ben and Jody for your help. :-)
>
> The issue was really due to the permissions on the geography_columns
> view. I didn't realise that PostGIS uses a view in addition to the two
> tables it added in the database.
>
> Cheers,
> Gerson
>
> On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
>> I think your request succeeded, but I suspect that, because of the
>> error encountered while trying to determine the SRID, your geometries
>> won't have a SRID until you grant at a minimum read access to
>> geography_columns, and so spatial operations including reprojection
>> will likely fail.
>>
>> On 24/05/11 14:55, Gerson Galang wrote:
>>> Note that I still get "1" as the output in stdout even if the exception
>>> gets thrown in stderr.
>>
>

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Hi Ben,

Yes, our application (being Australian based) only needs to use geometries.

Oh, and thanks for filing a Jira entry for this issue.

Cheers,
Gerson


On 05/25/2011 11:38 AM, Ben Caradoc-Davies wrote:
> Geography support is new in PostGIS 1.5.
>
> In my view (no pun intended), GeoTools behaviour could have been 
> better in this case: if it could not read geography_columns, it should 
> have used the information it could access in geometry_columns if 
> possible. I have filed a Jira issue to request this change:
> https://jira.codehaus.org/browse/GEOT-3603
>
> Gerson, I'm assuming that your table was not using geographies, just 
> geometries?
>
> Kind regards,
> Ben.
>
> On 25/05/11 07:16, Gerson Galang wrote:
>> Thanks Ben and Jody for your help. :-)
>>
>> The issue was really due to the permissions on the geography_columns
>> view. I didn't realise that PostGIS uses a view in addition to the two
>> tables it added in the database.
>>
>> Cheers,
>> Gerson
>>
>> On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
>>> I think your request succeeded, but I suspect that, because of the
>>> error encountered while trying to determine the SRID, your geometries
>>> won't have a SRID until you grant at a minimum read access to
>>> geography_columns, and so spatial operations including reprojection
>>> will likely fail.
>>>
>>> On 24/05/11 14:55, Gerson Galang wrote:
>>>> Note that I still get "1" as the output in stdout even if the 
>>>> exception
>>>> gets thrown in stderr.
>>>
>>
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Gerson,

what version of GeoTools are you using?

Andrea Aime thinks this might have been fixed on 28 March (logs say both 
2.7.x and trunk):
http://jira.codehaus.org/browse/GEOT-3603

This means that this should be fixed in 27.0.1 or later or 8.0-M0. If 
not, we'd like to know.

Kind regards,
Ben.


On 25/05/11 09:57, Gerson Galang wrote:
> Hi Ben,
>
> Yes, our application (being Australian based) only needs to use geometries.
>
> Oh, and thanks for filing a Jira entry for this issue.
>
> Cheers,
> Gerson
>
>
> On 05/25/2011 11:38 AM, Ben Caradoc-Davies wrote:
>> Geography support is new in PostGIS 1.5.
>>
>> In my view (no pun intended), GeoTools behaviour could have been
>> better in this case: if it could not read geography_columns, it should
>> have used the information it could access in geometry_columns if
>> possible. I have filed a Jira issue to request this change:
>> https://jira.codehaus.org/browse/GEOT-3603
>>
>> Gerson, I'm assuming that your table was not using geographies, just
>> geometries?
>>
>> Kind regards,
>> Ben.
>>
>> On 25/05/11 07:16, Gerson Galang wrote:
>>> Thanks Ben and Jody for your help. :-)
>>>
>>> The issue was really due to the permissions on the geography_columns
>>> view. I didn't realise that PostGIS uses a view in addition to the two
>>> tables it added in the database.
>>>
>>> Cheers,
>>> Gerson
>>>
>>> On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
>>>> I think your request succeeded, but I suspect that, because of the
>>>> error encountered while trying to determine the SRID, your geometries
>>>> won't have a SRID until you grant at a minimum read access to
>>>> geography_columns, and so spatial operations including reprojection
>>>> will likely fail.
>>>>
>>>> On 24/05/11 14:55, Gerson Galang wrote:
>>>>> Note that I still get "1" as the output in stdout even if the
>>>>> exception
>>>>> gets thrown in stderr.
>>>>
>>>
>>
>

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] [ExternalEmail] Re: Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Sorry, should be "2.7.0.1 or later or 8.0-M0".

On 25/05/11 15:06, Ben Caradoc-Davies wrote:
> This means that this should be fixed in 27.0.1 or later or 8.0-M0. If
> not, we'd like to know.

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Andrea A. <and...@ge...>]

On Wed, May 25, 2011 at 9:06 AM, Ben Caradoc-Davies
<Ben...@cs...> wrote:

> Gerson,
>
> what version of GeoTools are you using?
>
> Andrea Aime thinks this might have been fixed on 28 March (logs say both
> 2.7.x and trunk):
> http://jira.codehaus.org/browse/GEOT-3603
>
> This means that this should be fixed in 27.0.1 or later or 8.0-M0. If
> not, we'd like to know.
>

It seems we're confusing a real exception with a warning.
The thing was logged, it was not thrown, it did not stop the inspection
of the geometry_columns table.

May 24, 2011 2:55:14 PM org.geotools.jdbc.JDBCFeatureSource buildFeatureType
WARNING: Error occured determing srid for ssc06aaust_region.wkb_geometry
org.postgresql.util.PSQLException: ERROR: permission denied for relation
geography_columns

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

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

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

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

I'm not so sure. If you look in the stack trace in the original report 
it includes:

org.geotools.data.postgis.PostGISDialect.getGeometrySRID(PostGISDialect.java:386)
       at
org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:291)

This implies that the exception was thrown at:

     srid = dialect.getGeometrySRID( ...

so

     crs = dialect.createCRS(srid, cx);

is never called and crs is null when

     ab.setCRS(crs);

is called. I think this might result in the geometries having no CRS.

On 25/05/11 16:11, Andrea Aime wrote:
> It seems we're confusing a real exception with a warning.
> The thing was logged, it was not thrown, it did not stop the inspection
> of the geometry_columns table.
>
> May 24, 2011 2:55:14 PM org.geotools.jdbc.JDBCFeatureSource buildFeatureType
> WARNING: Error occured determing srid for ssc06aaust_region.wkb_geometry
> org.postgresql.util.PSQLException: ERROR: permission denied for relation
> geography_columns

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Hi Ben, Andrea,

I'm using 2.7.0 taken from the maven repository at download.osgeo.org. 
I've seen that you guys have 2.7.1 in there now, I'll switch to that, 
revert the ownership of the geography view, and test it out for you.

Cheers,
Gerson

On 05/25/2011 05:06 PM, Ben Caradoc-Davies wrote:
> Gerson,
>
> what version of GeoTools are you using?
>
> Andrea Aime thinks this might have been fixed on 28 March (logs say 
> both 2.7.x and trunk):
> http://jira.codehaus.org/browse/GEOT-3603
>
> This means that this should be fixed in 27.0.1 or later or 8.0-M0. If 
> not, we'd like to know.
>
> Kind regards,
> Ben.
>
>
> On 25/05/11 09:57, Gerson Galang wrote:
>> Hi Ben,
>>
>> Yes, our application (being Australian based) only needs to use 
>> geometries.
>>
>> Oh, and thanks for filing a Jira entry for this issue.
>>
>> Cheers,
>> Gerson
>>
>>
>> On 05/25/2011 11:38 AM, Ben Caradoc-Davies wrote:
>>> Geography support is new in PostGIS 1.5.
>>>
>>> In my view (no pun intended), GeoTools behaviour could have been
>>> better in this case: if it could not read geography_columns, it should
>>> have used the information it could access in geometry_columns if
>>> possible. I have filed a Jira issue to request this change:
>>> https://jira.codehaus.org/browse/GEOT-3603
>>>
>>> Gerson, I'm assuming that your table was not using geographies, just
>>> geometries?
>>>
>>> Kind regards,
>>> Ben.
>>>
>>> On 25/05/11 07:16, Gerson Galang wrote:
>>>> Thanks Ben and Jody for your help. :-)
>>>>
>>>> The issue was really due to the permissions on the geography_columns
>>>> view. I didn't realise that PostGIS uses a view in addition to the two
>>>> tables it added in the database.
>>>>
>>>> Cheers,
>>>> Gerson
>>>>
>>>> On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
>>>>> I think your request succeeded, but I suspect that, because of the
>>>>> error encountered while trying to determine the SRID, your geometries
>>>>> won't have a SRID until you grant at a minimum read access to
>>>>> geography_columns, and so spatial operations including reprojection
>>>>> will likely fail.
>>>>>
>>>>> On 24/05/11 14:55, Gerson Galang wrote:
>>>>>> Note that I still get "1" as the output in stdout even if the
>>>>>> exception
>>>>>> gets thrown in stderr.
>>>>>
>>>>
>>>
>>
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Thanks a lot, Gerson, that would be excellent.

On 26/05/11 13:49, Gerson Galang wrote:
> Hi Ben, Andrea,
>
> I'm using 2.7.0 taken from the maven repository at download.osgeo.org.
> I've seen that you guys have 2.7.1 in there now, I'll switch to that,
> revert the ownership of the geography view, and test it out for you.
>
> Cheers,
> Gerson
>
> On 05/25/2011 05:06 PM, Ben Caradoc-Davies wrote:
>> Gerson,
>>
>> what version of GeoTools are you using?
>>
>> Andrea Aime thinks this might have been fixed on 28 March (logs say
>> both 2.7.x and trunk):
>> http://jira.codehaus.org/browse/GEOT-3603
>>
>> This means that this should be fixed in 27.0.1 or later or 8.0-M0. If
>> not, we'd like to know.
>>
>> Kind regards,
>> Ben.
>>
>>
>> On 25/05/11 09:57, Gerson Galang wrote:
>>> Hi Ben,
>>>
>>> Yes, our application (being Australian based) only needs to use
>>> geometries.
>>>
>>> Oh, and thanks for filing a Jira entry for this issue.
>>>
>>> Cheers,
>>> Gerson
>>>
>>>
>>> On 05/25/2011 11:38 AM, Ben Caradoc-Davies wrote:
>>>> Geography support is new in PostGIS 1.5.
>>>>
>>>> In my view (no pun intended), GeoTools behaviour could have been
>>>> better in this case: if it could not read geography_columns, it should
>>>> have used the information it could access in geometry_columns if
>>>> possible. I have filed a Jira issue to request this change:
>>>> https://jira.codehaus.org/browse/GEOT-3603
>>>>
>>>> Gerson, I'm assuming that your table was not using geographies, just
>>>> geometries?
>>>>
>>>> Kind regards,
>>>> Ben.
>>>>
>>>> On 25/05/11 07:16, Gerson Galang wrote:
>>>>> Thanks Ben and Jody for your help. :-)
>>>>>
>>>>> The issue was really due to the permissions on the geography_columns
>>>>> view. I didn't realise that PostGIS uses a view in addition to the two
>>>>> tables it added in the database.
>>>>>
>>>>> Cheers,
>>>>> Gerson
>>>>>
>>>>> On 05/24/2011 06:51 PM, Ben Caradoc-Davies wrote:
>>>>>> I think your request succeeded, but I suspect that, because of the
>>>>>> error encountered while trying to determine the SRID, your geometries
>>>>>> won't have a SRID until you grant at a minimum read access to
>>>>>> geography_columns, and so spatial operations including reprojection
>>>>>> will likely fail.
>>>>>>
>>>>>> On 24/05/11 14:55, Gerson Galang wrote:
>>>>>>> Note that I still get "1" as the output in stdout even if the
>>>>>>> exception
>>>>>>> gets thrown in stderr.
>>>>>>
>>>>>
>>>>
>>>
>>
>

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

[Geotools-gt2-users] SocketException being thrown after a number of consecutive PostGIS queries

[Gerson G. <ger...@gm...>]

Hi again,

I've written a portlet that needs to perform a number of PostGIS queries 
per http request. There are times when the query just wouldn't work. 
Whenever the query fails, I get this on the log file...

02:09:31,760 ERROR [jsp:154] java.net.SocketException: Socket closed
     at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:99)
     at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
     at 
java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
     at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
     at org.postgresql.core.PGStream.flush(PGStream.java:508)
     at 
org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:1107)
     at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:256)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
     at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
     at 
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
     at 
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
     at 
org.geotools.jdbc.JDBCFeatureReader.<init>(JDBCFeatureReader.java:147)
     at 
org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:557)
     at 
org.geotools.jdbc.JDBCFeatureStore.getReaderInternal(JDBCFeatureStore.java:209)
     at 
org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:483)
     at 
org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:209)
     at 
org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:62)
     at au.org.aurin.util.LocationUtil.getLGAs(LocationUtil.java:149)
     at 
au.org.aurin.portlets.LocationPickerPortlet.doView(LocationPickerPortlet.java:105)

I had a look at my postgresql logs as well and found this whenever the 
exception above is thrown..

2011-05-27 12:07:34 EST ERROR:  relation "gt_pk_metadata" does not exist 
at character 15
2011-05-27 12:07:34 EST STATEMENT:  SELECT * FROM GT_PK_METADATA
2011-05-27 12:07:42 EST ERROR:  relation "gt_pk_metadata" does not exist 
at character 15
2011-05-27 12:07:42 EST STATEMENT:  SELECT * FROM GT_PK_METADATA
2011-05-27 12:07:59 EST LOG:  unexpected EOF on client connection
2011-05-27 12:07:59 EST LOG:  unexpected EOF on client connection
2011-05-27 12:07:59 EST LOG:  unexpected EOF on client connection
2011-05-27 12:07:59 EST LOG:  unexpected EOF on client connection
2011-05-27 12:07:59 EST LOG:  unexpected EOF on client connection
2011-05-27 12:08:39 EST ERROR:  relation "gt_pk_metadata" does not exist 
at character 15
2011-05-27 12:08:39 EST STATEMENT:  SELECT * FROM GT_PK_METADATA
2011-05-27 12:08:41 EST LOG:  unexpected EOF on client connection
2011-05-27 12:09:12 EST LOG:  unexpected EOF on client connection
2011-05-27 12:09:12 EST LOG:  unexpected EOF on client connection
2011-05-27 12:10:01 EST ERROR:  relation "gt_pk_metadata" does not exist 
at character 15
2011-05-27 12:10:01 EST STATEMENT:  SELECT * FROM GT_PK_METADATA
2011-05-27 12:10:03 EST LOG:  unexpected EOF on client connection

Any ideas?

I'm not sure if I should send this message to the PostGIS mailing list 
or just geotools, but I'll try it here first.

Thanks,
Gerson

Re: [Geotools-gt2-users] SocketException being thrown after a number of consecutive PostGIS queries

[Andrea A. <and...@ge...>]

On Fri, May 27, 2011 at 4:48 AM, Gerson Galang <ger...@gm...> wrote:
> Hi again,
>
> I've written a portlet that needs to perform a number of PostGIS queries
> per http request. There are times when the query just wouldn't work.
> Whenever the query fails, I get this on the log file...

My guess is that your postgis is setup to drop the connections to the client
after a certain timeout.
The best thing would be to enable the connection validation in the params,
the connection pool with then first send a fast query (normally getting
the current date) to check if the connection is still valid before giving it
back to the code in the data store

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

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

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

Re: [Geotools-gt2-users] SocketException being thrown after a number of consecutive PostGIS queries

[Gerson G. <ger...@gm...>]

Thanks a lot for your advice Andrea,

Adding "params.put(VALIDATECONN.key, Boolean.TRUE);" seems to have done 
the trick.

Cheers,
Gerson

On 05/27/2011 04:52 PM, Andrea Aime wrote:
> On Fri, May 27, 2011 at 4:48 AM, Gerson Galang<ger...@gm...>  wrote:
>> Hi again,
>>
>> I've written a portlet that needs to perform a number of PostGIS queries
>> per http request. There are times when the query just wouldn't work.
>> Whenever the query fails, I get this on the log file...
> My guess is that your postgis is setup to drop the connections to the client
> after a certain timeout.
> The best thing would be to enable the connection validation in the params,
> the connection pool with then first send a fast query (normally getting
> the current date) to check if the connection is still valid before giving it
> back to the code in the data store
>
> Cheers
> Andrea
>
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Australia has Antarctic territory, which will benefit rather a lot from 
geographies:
http://en.wikipedia.org/wiki/Australian_Antarctic_Territory

On 25/05/11 09:57, Gerson Galang wrote:
> Yes, our application (being Australian based) only needs to use geometries.

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Good point Ben. Since the application will be mainly used for Urban 
planning, I think it'll be a long while before we start looking at 
geographic functions that geotools provide. :-)

On 05/25/2011 12:12 PM, Ben Caradoc-Davies wrote:
> Australia has Antarctic territory, which will benefit rather a lot 
> from geographies:
> http://en.wikipedia.org/wiki/Australian_Antarctic_Territory
>
> On 25/05/11 09:57, Gerson Galang wrote:
>> Yes, our application (being Australian based) only needs to use 
>> geometries.
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Hi Ben,

I had a go at 2.7.1 yesterday but it started complaining about the 
parameter I'm trying to pass to the DataStoreFinder. I just get a null 
value when I instantiate the DataStore.

         Map<String, Object> params = new HashMap<String, Object>();
         params.put(HOST.key, "myhost");
         params.put(DATABASE.key, "mydb");
         params.put(PORT.key, "5432");
         params.put(USER.key, "gis");
         params.put(PASSWD.key, "mypass");
         params.put(DBTYPE.key, "postgis");

         dataStore = DataStoreFinder.getDataStore(params);

Another weird thing I've noticed is that the CQL.toFilter doesn't throw 
a CQLException anymore on 2.7.1. Even if the documentation states that 
it throws a CQLException, Eclipse just complains about my try-catch 
block not having any statement that throws a CQLException.

 >> No exception of type CQLException can be thrown; an exception type 
must be a subclass of Throwable

I'm only using two dependencies from geotools..

<dependency>
<groupId>org.geotools.jdbc</groupId>
<artifactId>gt-jdbc-postgis</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-epsg-hsql</artifactId>
<version>${geotools.version}</version>
</dependency>

I'm also using this repository..

<repository>
<id>geotools-repo</id>
<name>GeoTools Repository</name>
<url>http://download.osgeo.org/webdav/geotools</url>
</repository>

Regards,
Gerson


On 05/26/2011 04:05 PM, Ben Caradoc-Davies wrote:
> Thanks a lot, Gerson, that would be excellent.
>
> On 26/05/11 13:49, Gerson Galang wrote:
>> Hi Ben, Andrea,
>>
>> I'm using 2.7.0 taken from the maven repository at download.osgeo.org.
>> I've seen that you guys have 2.7.1 in there now, I'll switch to that,
>> revert the ownership of the geography view, and test it out for you.
>>
>> Cheers,
>> Gerson

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Andrea A. <and...@ge...>]

On Fri, May 27, 2011 at 4:34 AM, Gerson Galang <ger...@gm...> wrote:
> Hi Ben,
>
> I had a go at 2.7.1 yesterday but it started complaining about the
> parameter I'm trying to pass to the DataStoreFinder. I just get a null
> value when I instantiate the DataStore.
>
>         Map<String, Object> params = new HashMap<String, Object>();
>         params.put(HOST.key, "myhost");
>         params.put(DATABASE.key, "mydb");
>         params.put(PORT.key, "5432");
>         params.put(USER.key, "gis");
>         params.put(PASSWD.key, "mypass");
>         params.put(DBTYPE.key, "postgis");
>
>         dataStore = DataStoreFinder.getDataStore(params);
>
> Another weird thing I've noticed is that the CQL.toFilter doesn't throw
> a CQLException anymore on 2.7.1. Even if the documentation states that
> it throws a CQLException, Eclipse just complains about my try-catch
> block not having any statement that throws a CQLException.

My guess is that there is something odd going on in your setup, the
class in the branch from which 2.7.1 was cut definitely still throws
CQLException:

http://svn.osgeo.org/geotools/branches/2.7.x/modules/library/cql/src/main/java/org/geotools/filter/text/cql2/CQL.java

as well as the one in the tag backing that release:

http://svn.osgeo.org/geotools/tags/2.7.1/modules/library/cql/src/main/java/org/geotools/filter/text/cql2/CQL.java

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

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

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

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

On 27/05/11 10:34, Gerson Galang wrote:
> I had a go at 2.7.1 yesterday but it started complaining about the
> parameter I'm trying to pass to the DataStoreFinder. I just get a null
> value when I instantiate the DataStore.

Do you have an old gt-postgis on your classpath? There is some funny 
logic in gt-jdbc-postgis that gives gt-postgis preference if both are 
present and you use dbtype=postgis (for backwards compatibility). But 
this should not happen if you are using Maven.  :-(

Perhaps the step below might help this too:

> Another weird thing I've noticed is that the CQL.toFilter doesn't throw
> a CQLException anymore on 2.7.1. Even if the documentation states that
> it throws a CQLException, Eclipse just complains about my try-catch
> block not having any statement that throws a CQLException.

Please try:
mvn eclipse:clean eclipse:eclipse
and then refresh and then clean your project in Eclipse. Sometimes 
Eclipse falls down and can't get up.  :-P

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Gerson G. <ger...@gm...>]

Hi Ben, Andrea,

I followed your suggestion of cleaning up my eclipse project workspace 
but it still didn't fix the problem I reported originally. I'm still 
getting a warning message if I change the ownership of the 
geography_columns to 'postgres'

Jun 1, 2011 9:04:20 AM org.geotools.data.postgis.PostGISDialect 
getGeometrySRID
WARNING: Failed to retrieve information about 
public.lga07aaust_region.wkb_geometry from the geometry_columns table, 
checking geometry_columns instead
org.postgresql.util.PSQLException: ERROR: permission denied for relation 
geography_columns

But the clean eclipse project workspace has fixed the issue with 2.7.1 
complaining about CQLException not being thrown by CQL.toFilter() method.

Cheers,
Gerson


On 05/27/2011 05:01 PM, Ben Caradoc-Davies wrote:
> On 27/05/11 10:34, Gerson Galang wrote:
>> I had a go at 2.7.1 yesterday but it started complaining about the
>> parameter I'm trying to pass to the DataStoreFinder. I just get a null
>> value when I instantiate the DataStore.
>
> Do you have an old gt-postgis on your classpath? There is some funny 
> logic in gt-jdbc-postgis that gives gt-postgis preference if both are 
> present and you use dbtype=postgis (for backwards compatibility). But 
> this should not happen if you are using Maven.  :-(
>
> Perhaps the step below might help this too:
>
>> Another weird thing I've noticed is that the CQL.toFilter doesn't throw
>> a CQLException anymore on 2.7.1. Even if the documentation states that
>> it throws a CQLException, Eclipse just complains about my try-catch
>> block not having any statement that throws a CQLException.
>
> Please try:
> mvn eclipse:clean eclipse:eclipse
> and then refresh and then clean your project in Eclipse. Sometimes 
> Eclipse falls down and can't get up.  :-P
>

Re: [Geotools-gt2-users] Permission Denied Error on a PostGIS Query

[Ben Caradoc-D. <Ben...@cs...>]

Thanks, Gerson. I can now confirm Andrea's advice that this is just a 
warning (with a typo) on trunk and 2.7.1. I have tested this using 
GeoServer (GeoTools trunk) and I get the same warning that you see on 
2.7.1. The warning is logged, but the plugin falls back to using 
geometry_columns and geometries are created correctly.

Thanks for taking the time to confirm this behaviour.

Kind regards,
Ben.

On 01/06/11 07:11, Gerson Galang wrote:
> Hi Ben, Andrea,
>
> I followed your suggestion of cleaning up my eclipse project workspace
> but it still didn't fix the problem I reported originally. I'm still
> getting a warning message if I change the ownership of the
> geography_columns to 'postgres'
>
> Jun 1, 2011 9:04:20 AM org.geotools.data.postgis.PostGISDialect
> getGeometrySRID
> WARNING: Failed to retrieve information about
> public.lga07aaust_region.wkb_geometry from the geometry_columns table,
> checking geometry_columns instead
> org.postgresql.util.PSQLException: ERROR: permission denied for relation
> geography_columns
>
> But the clean eclipse project workspace has fixed the issue with 2.7.1
> complaining about CQLException not being thrown by CQL.toFilter() method.
>
> Cheers,
> Gerson
>
>
> On 05/27/2011 05:01 PM, Ben Caradoc-Davies wrote:
>> On 27/05/11 10:34, Gerson Galang wrote:
>>> I had a go at 2.7.1 yesterday but it started complaining about the
>>> parameter I'm trying to pass to the DataStoreFinder. I just get a null
>>> value when I instantiate the DataStore.
>>
>> Do you have an old gt-postgis on your classpath? There is some funny
>> logic in gt-jdbc-postgis that gives gt-postgis preference if both are
>> present and you use dbtype=postgis (for backwards compatibility). But
>> this should not happen if you are using Maven.  :-(
>>
>> Perhaps the step below might help this too:
>>
>>> Another weird thing I've noticed is that the CQL.toFilter doesn't throw
>>> a CQLException anymore on 2.7.1. Even if the documentation states that
>>> it throws a CQLException, Eclipse just complains about my try-catch
>>> block not having any statement that throws a CQLException.
>>
>> Please try:
>> mvn eclipse:clean eclipse:eclipse
>> and then refresh and then clean your project in Eclipse. Sometimes
>> Eclipse falls down and can't get up.  :-P
>>
>

-- 
Ben Caradoc-Davies <Ben...@cs...>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre