[Geoserver-users] Uppercase featureTypes with Postgis database

GeoServer GeoServer User
1

Hi,

In our development environment we want to use postgis as our db opposed to oracle spatial in testing/production.

For the oracle db we have a data-dir setup with uppercase names for our feature types.

When using this data-dir and only changing the datastore in the catalog.xml I get several errors like:

org.geotools.data.SchemaNotFoundException: Feature type could not be found for

I traced it down to the DataBaseMetaData class methods like getTableName and getColumns where in the postgres case a lowercase typname must be provided to make it work.

Is there a possibility to use the same data-dir (and thus not having to rewrite all featuretype/info.xml’s to use a lowercase name (including changing the openlayers javascript)

I have already checked out a copy of geoserver and geotools sourcecode so if changes need to be made I can do that if pointed in the right direction (where to change and how to prevent mashing up other db configurations)

Cheers

Kris Geusebroek

Consultant



cid:image001.jpg@anonymised.com

Email: kgeusebroek@anonymised.com



Tel: +31 (0)35 538 1921



Fax: +31 (0)35 538 1922



Mobile: +31 (0)6 30 697 223



http://www.xebia.com

Utrechtseweg 49



1213 TL Hilversum



The Netherlands

Xebia Blog ! http://blog.xebia.com/

Xebia Podcast! http://podcast.xebia.com/

2

Kris Geusebroek ha scritto:

Hi,

In our development environment we want to use postgis as our db opposed to oracle spatial in testing/production.

For the oracle db we have a data-dir setup with uppercase names for our feature types.

When using this data-dir and only changing the datastore in the catalog.xml I get several errors like:

org.geotools.data.SchemaNotFoundException: Feature type could not be found for <typeName>

I traced it down to the DataBaseMetaData class methods like getTableName and getColumns where in the postgres case a lowercase typname must be provided to make it work.

Is there a possibility to use the same data-dir (and thus not having to rewrite all featuretype/info.xml’s to use a lowercase name (including changing the openlayers javascript)

I have already checked out a copy of geoserver and geotools sourcecode so if changes need to be made I can do that if pointed in the right direction (where to change and how to prevent mashing up other db configurations)

Hmmm.... even if you fixed the table name lookup in the metadata you'd
end up with different names for attributes (they would be lower case,
again), and this would change the GML output, and make SLD using
attribute names non portable between the two installations.

As far as I know Postgres is case preserving, and defaults to lowercase
if no one specified otherwise. In order to have the same case as Oracle
you just have to create your table forcefully specifying a uppercase
convention, that is, something like:

create table "MYTABLE" (
   "ID" serial,
   "NAME" varchar(200),
   ...
)

and then it should work (my guess, never actually tried).

GeoServer actively relies on this case preserving setup to pass
the CITE tests, where OGC requires one to have types and attributes
with camel cased names (e.g., "GenericEntity").

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Yep,
Postgres has case sensivity. Just recently bang my head against a wall with that. See e.g.:

http://codesnippets.joyent.com/posts/show/1701

The worst thing IMO, when migrating data with different databases, is to mix lower and upper case in names. For example:

http://webhelp.esri.com/arcgisserver/9.3/dotNet/index.htm#geodatabases/insta
l-1427030965.htm

"Be aware that although PostgreSQL allows you to store mixed case or all
uppercase database or user names, these cannot be used with ArcSDE
geodatabases."

- mika -

Andrea Aime kirjoitti:

Kris Geusebroek ha scritto:
  

Hi,

In our development environment we want to use postgis as our db opposed to oracle spatial in testing/production.

For the oracle db we have a data-dir setup with uppercase names for our feature types.

When using this data-dir and only changing the datastore in the catalog.xml I get several errors like:

org.geotools.data.SchemaNotFoundException: Feature type could not be found for <typeName>

I traced it down to the DataBaseMetaData class methods like getTableName and getColumns where in the postgres case a lowercase typname must be provided to make it work.

Is there a possibility to use the same data-dir (and thus not having to rewrite all featuretype/info.xml’s to use a lowercase name (including changing the openlayers javascript)

I have already checked out a copy of geoserver and geotools sourcecode so if changes need to be made I can do that if pointed in the right direction (where to change and how to prevent mashing up other db configurations)
    
Hmmm.... even if you fixed the table name lookup in the metadata you'd
end up with different names for attributes (they would be lower case,
again), and this would change the GML output, and make SLD using
attribute names non portable between the two installations.

As far as I know Postgres is case preserving, and defaults to lowercase
if no one specified otherwise. In order to have the same case as Oracle
you just have to create your table forcefully specifying a uppercase
convention, that is, something like:

create table "MYTABLE" (
   "ID" serial,
   "NAME" varchar(200),
   ...
)

and then it should work (my guess, never actually tried).

GeoServer actively relies on this case preserving setup to pass
the CITE tests, where OGC requires one to have types and attributes
with camel cased names (e.g., "GenericEntity").

Cheers
Andrea

4

Hi Andrea,

Creating those tables with quotes surrounding the tableName and column
Names did the trick.

Thanks a lot

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Wednesday, January 28, 2009 9:18 AM
To: Kris Geusebroek
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Uppercase featureTypes with Postgis
database

Kris Geusebroek ha scritto:

Hi,

In our development environment we want to use postgis as our db

opposed

to oracle spatial in testing/production.

For the oracle db we have a data-dir setup with uppercase names for

our

feature types.

When using this data-dir and only changing the datastore in the
catalog.xml I get several errors like:

org.geotools.data.SchemaNotFoundException: Feature type could not be
found for <typeName>

I traced it down to the DataBaseMetaData class methods like

getTableName

and getColumns where in the postgres case a lowercase typname must be
provided to make it work.

Is there a possibility to use the same data-dir (and thus not having

to

rewrite all featuretype/info.xml's to use a lowercase name (including
changing the openlayers javascript)

I have already checked out a copy of geoserver and geotools

sourcecode

so if changes need to be made I can do that if pointed in the right
direction (where to change and how to prevent mashing up other db
configurations)

Hmmm.... even if you fixed the table name lookup in the metadata you'd
end up with different names for attributes (they would be lower case,
again), and this would change the GML output, and make SLD using
attribute names non portable between the two installations.

As far as I know Postgres is case preserving, and defaults to lowercase
if no one specified otherwise. In order to have the same case as Oracle
you just have to create your table forcefully specifying a uppercase
convention, that is, something like:

create table "MYTABLE" (
   "ID" serial,
   "NAME" varchar(200),
   ...
)

and then it should work (my guess, never actually tried).

GeoServer actively relies on this case preserving setup to pass
the CITE tests, where OGC requires one to have types and attributes
with camel cased names (e.g., "GenericEntity").

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.