[Geoserver-users] Problems including Views from Oracle Spatial

GeoServer GeoServer User
1

Hello,

I try to integrate a View from Oracle Spatial as an FeatureType to Geoserver
1.5.0 using the Webfrondend.
This failed.
When I configure the FeatureType manually by creating an Folder with info.xml
and schema.xml everything works fine.
Has anyone an Idea how I can tell the Webfrontend to do this?

regrads
Tim

2

Tim Englich ha scritto:

Hello,

I try to integrate a View from Oracle Spatial as an FeatureType to Geoserver
1.5.0 using the Webfrondend.
This failed.
When I configure the FeatureType manually by creating an Folder with info.xml
and schema.xml everything works fine.
Has anyone an Idea how I can tell the Webfrontend to do this?

Well, usually you have to register view against some metadata table... can't remember the exact name, there should be some USER_GEOMETRY_COLUMNS updatable view in MSYS_SDO (trying to remember,
haven't checked so names may be quite a bit off the real ones).
Anyways, did you see any error in the Geoserver log when your attempt
failed?

Cheers
Andrea

3

The view is user_sdo_geom_metadata. When you insert a record into it you must be logged in as the user who owns the view you’re adding and one of the fields (diminfo) in the user_sdo_geom_meta_data view requires an Oracle object constructor so it’s usually easier to copy one from a table that’s already registered (usually the one you’re basing the view on) for example:

INSERT INTO user_sdo_geom_metadata
(table_name, column_name, diminfo, srid)
SELECT 'my_view', 'my_spatial_column', diminfo, 4326
FROM user_sdo_geom_metadata 
WHERE table_name = 'my_base_table';

Hope that makes sense!

On 5/22/07, Andrea Aime <aaime@anonymised.com> wrote:

Tim Englich ha scritto:

Hello,

I try to integrate a View from Oracle Spatial as an FeatureType to Geoserver
1.5.0 using the Webfrondend.
This failed.
When I configure the FeatureType manually by creating an Folder with info.xml
and schema.xml everything works fine.
Has anyone an Idea how I can tell the Webfrontend to do this?

Well, usually you have to register view against some metadata table…
can’t remember the exact name, there should be some
USER_GEOMETRY_COLUMNS updatable view in MSYS_SDO (trying to remember,
haven’t checked so names may be quite a bit off the real ones).
Anyways, did you see any error in the Geoserver log when your attempt
failed?

Cheers
Andrea

This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

4

Hi,

I am also having problems with Oracle spatial view and Geoserver 1.5.0. I am sure I have used views sometimes earlier, but I have no success in it now.
In order to make things as simple as possible I have created a view as a copy from a table that works as FeatureType (SELECT all fields without any WHEREs). USER_SDO_GEOM_METADATA is similar, despite TABLE_NAME, of course. The view works fine from SQL+. I can make FeatureType out of the view with administration utility and everything seems to be OK, including DescribeFeatureType, but all WFS queries are failing.

I have here whole lot of text captured from Geoserver console. If there is somebody interested in it I can send it in another mail.

Regards,

-Jukka Rahkonen-

________________________________

Lähettäjä: geoserver-users-bounces@lists.sourceforge.net puolesta: Tom (JDi Solutions)
Lähetetty: ti 22.5.2007 23:07
Vastaanottaja: Andrea Aime
Kopio: geoserver-users@lists.sourceforge.net
Aihe: Re: [Geoserver-users] Problems including Views from Oracle Spatial

The view is user_sdo_geom_metadata. When you insert a record into it you must be logged in as the user who owns the view you're adding and one of the fields (diminfo) in the user_sdo_geom_meta_data view requires an Oracle object constructor so it's usually easier to copy one from a table that's already registered (usually the one you're basing the view on) for example:

INSERT INTO user_sdo_geom_metadata
(table_name, column_name, diminfo, srid)
SELECT 'my_view', 'my_spatial_column', diminfo, 4326
FROM user_sdo_geom_metadata
WHERE table_name = 'my_base_table';

Hope that makes sense!

On 5/22/07, Andrea Aime <aaime@anonymised.com> wrote:

  Tim Englich ha scritto:
  > Hello,
  >
  > I try to integrate a View from Oracle Spatial as an FeatureType to Geoserver
  > 1.5.0 using the Webfrondend.
  > This failed.
  > When I configure the FeatureType manually by creating an Folder with info.xml
  > and schema.xml everything works fine.
  > Has anyone an Idea how I can tell the Webfrontend to do this?
  
  Well, usually you have to register view against some metadata table...
  can't remember the exact name, there should be some
  USER_GEOMETRY_COLUMNS updatable view in MSYS_SDO (trying to remember,
  haven't checked so names may be quite a bit off the real ones).
  Anyways, did you see any error in the Geoserver log when your attempt
  failed?
  
  Cheers
  Andrea
  
  -------------------------------------------------------------------------
  This SF.net email is sponsored by DB2 Express
  Download DB2 Express C - the FREE version of DB2 express and take
  control of your XML. No limits. Just data. Click to get it now.
  http://sourceforge.net/powerbar/db2/
  _______________________________________________
  Geoserver-users mailing list
  Geoserver-users@lists.sourceforge.net
  https://lists.sourceforge.net/lists/listinfo/geoserver-users
  

5

Rahkonen Jukka wrote:

Hi,

I am also having problems with Oracle spatial view and Geoserver
1.5.0. I am sure I have used views sometimes earlier, but I have no
success in it now. In order to make things as simple as possible I
have created a view as a copy from a table that works as FeatureType
(SELECT all fields without any WHEREs). USER_SDO_GEOM_METADATA is
similar, despite TABLE_NAME, of course. The view works fine from
SQL+. I can make FeatureType out of the view with administration
utility and everything seems to be OK, including DescribeFeatureType,
but all WFS queries are failing.

Have you already defined a Primary Key for such views ?

Regards,

--------------------
    Luca Morandini
www.lucamorandini.it
--------------------

6

Lähettäjä: geoserver-users-bounces@lists.sourceforge.net puolesta: Luca Morandini
Lähetetty: pe 25.5.2007 14:51

Rahkonen Jukka wrote:

Hi,

I am also having problems with Oracle spatial view and Geoserver
1.5.0. I am sure I have used views sometimes earlier, but I have no
success in it now. In order to make things as simple as possible I
have created a view as a copy from a table that works as FeatureType
(SELECT all fields without any WHEREs). USER_SDO_GEOM_METADATA is
similar, despite TABLE_NAME, of course. The view works fine from
SQL+. I can make FeatureType out of the view with administration
utility and everything seems to be OK, including DescribeFeatureType,
but all WFS queries are failing.

Have you already defined a Primary Key for such views ?

That was it. However, I faced a new problem next. Or actually it is an old problem that popped up again: Geoserver is creating invalid fids. Here is an example:
<gml:PERUSLOHKOT2 fid="PERUSLOHKOT2.1602c310:112c2aad0ac:3ffb">

Those colon characters make that fid is invalid, and my WFS client does not accept the data. My Geoserver version is 1.5.0 and that problem should be corrected in it. Obviously it is not the case in all situations.

-Jukka-

7

Rahkonen Jukka ha scritto:

Have you already defined a Primary Key for such views ?

That was it. However, I faced a new problem next. Or actually it is an old problem that popped up again: Geoserver is creating invalid fids. Here is an example:
<gml:PERUSLOHKOT2 fid="PERUSLOHKOT2.1602c310:112c2aad0ac:3ffb">

Rahkonen, can you report the primary key you declared?
It seems the Oracle data store is not recognizing it properly, and
thus defaults to the null primary key mapper, that still generates
invalid FIDs (it's a last chance mapper that jdbc data store do
use when they cannot understand the primary key, and thus it just
generates a GUID every time a FID is required -> features will
have different UID each time you retrieve them).

To have the view work properly, the Oracle data store must be
unable to distinguish it from a proper spatial table. This means
geometry columns must be registered, and primary key must be
one of the supported types.

Cheers
Andrea

8

Andrea Aime ha scritto:

Rahkonen Jukka ha scritto:

Have you already defined a Primary Key for such views ?

That was it. However, I faced a new problem next. Or actually it is an old problem that popped up again: Geoserver is creating invalid fids. Here is an example:
<gml:PERUSLOHKOT2 fid="PERUSLOHKOT2.1602c310:112c2aad0ac:3ffb">

Rahkonen, can you report the primary key you declared?

Oh, just checked the code. In Oracle, the null fid mapper
is used only if Oracle reports the table under exhamination
has no primary key at all.

Cheers
Andrea

9

Hello,

________________________________

Lähettäjä: Andrea Aime [mailto:aaime@anonymised.com]
Lähetetty: ke 30.5.2007 9:30

Andrea Aime ha scritto:

Rahkonen Jukka ha scritto:

Have you already defined a Primary Key for such views ?

That was it. However, I faced a new problem next. Or actually it is an old problem that popped up again: Geoserver is creating invalid fids. Here is an example:
<gml:PERUSLOHKOT2 fid="PERUSLOHKOT2.1602c310:112c2aad0ac:3ffb">

Rahkonen, can you report the primary key you declared?

Oh, just checked the code. In Oracle, the null fid mapper
is used only if Oracle reports the table under exhamination
has no primary key at all.

I just repeated my trial and now I really do get working fids. I must have done some error in the first time in creating the primary key for the view, or then I just forgot to load again Geoserver configuration after altering the table in Oracle. It seems to be a demand.

-Jukka-

10

Rahkonen Jukka ha scritto:

Hello,

________________________________

Lähettäjä: Andrea Aime [mailto:aaime@anonymised.com] Lähetetty: ke
30.5.2007 9:30

Andrea Aime ha scritto:

Rahkonen Jukka ha scritto:

Have you already defined a Primary Key for such views ?

That was it. However, I faced a new problem next. Or actually
it is an old problem that popped up again: Geoserver is creating
invalid fids. Here is an example: <gml:PERUSLOHKOT2
fid="PERUSLOHKOT2.1602c310:112c2aad0ac:3ffb"> Rahkonen, can you
report the primary key you declared?

Oh, just checked the code. In Oracle, the null fid mapper is used
only if Oracle reports the table under exhamination has no primary
key at all.

I just repeated my trial and now I really do get working fids. I
must have done some error in the first time in creating the primary
key for the view, or then I just forgot to load again Geoserver
configuration after altering the table in Oracle. It seems to be a
demand.

It is. We do cache feature type structure because we need it often,
but unfortunately there is no way to control the cache live, or
to purge it. As a result, the only effective way to make Geoserver
notice a change in the feature type coming from JDBC data store
is to restart it.

Cheers
Andrea