[Geoserver-users] Problems with OracleNG Datastore after updating from Oracle

GeoServer GeoServer User
1

Hello,

After upgrading to Geoserver 1.7.3 from 1.6.3 and now using OracleNG with Oracle-Datastore, I get an error when rendering the WMS and during GetFeatureInfo.

I can see the FeatureType defined correctly.
The demo map displays an empty map and on GetFeatureInfo I get an exception:
java.lang.RuntimeException: java.io.IOException: Error occured calculating count java.io.IOException: Error occured calculating count Error occured calculating count ORA-29902: Fehler bei der Ausf�hrung von Routine ODCIIndexStart() ORA-13208: Interner Fehler beim Auswerten von Operator [window SRID does not match layer SRID] ORA-06512: in
"MDSYS.SDO_INDEX_METHOD_10I", Zeile 286

I attached the full stacktrace.

I also attached the commands to create the table and index on my Oracle 10g (version 10.2.0.1.0).
I also noticed that the featureType configuration does not display a Native CRS WKT (just "-").

Is there a know issue/bug?
As you can see I do not use WGS84, but EPSG:31285.
And as mentioned above, I got a preview map with the old Oracle datastore. It's not possible to use the old datastore, because it offers
no support for NVARCHAR2-fields.

Thanks and best regards,
Bernhard Kiselka
--

PRISMA solutions
Bernhard Kiselka
-----------------------------------------------
Telefon: +43 2236 47975 25
E-Mail: bernhard.kiselka@anonymised.com
------------------------------------------------
PRISMA solutions EDV-Dienstleistungen GmbH
Adresse: Klostergasse 18, 2340 Mödling, Austria
http://www.prisma-solutions.at

--

PRISMA solutions
Bernhard Kiselka
-----------------------------------------------
Telefon: +43 2236 47975 25
E-Mail: bernhard.kiselka@anonymised.com
------------------------------------------------
PRISMA solutions EDV-Dienstleistungen GmbH
Adresse: Klostergasse 18, 2340 Mödling, Austria
http://www.prisma-solutions.at

20090320_geoserver_oracle_connectiontest_script.txt (1.49 KB)

20090320_geoserver_oracle_connectiontest_stacktrace.txt (16 KB)

2

Bernhard Kiselka ha scritto:

Hello,

After upgrading to Geoserver 1.7.3 from 1.6.3 and now using OracleNG with Oracle-Datastore, I get an error when rendering the WMS and during GetFeatureInfo.

I can see the FeatureType defined correctly.
The demo map displays an empty map and on GetFeatureInfo I get an exception:
java.lang.RuntimeException: java.io.IOException: Error occured calculating count java.io.IOException: Error occured calculating count Error occured calculating count ORA-29902: Fehler bei der Ausf�hrung von Routine ODCIIndexStart() ORA-13208: Interner Fehler beim Auswerten von Operator [window SRID does not match layer SRID] ORA-06512: in
"MDSYS.SDO_INDEX_METHOD_10I", Zeile 286

I attached the full stacktrace.

I also attached the commands to create the table and index on my Oracle 10g (version 10.2.0.1.0).

Hi Bernhard,
I've loaded the data using your provided script but there is something
wrong with it:

insert into district_test (objectid, alive, featurename, shape) values (42, 1, 'test',
SDO_GEOMETRY(2007, 31285, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1670.0625, 341194.008, 1666.21875, 341209.484))
);

This statement inserts a multipolyon that, from the intentions, seems
to be made of a single optimized rectangle? Yet, that's not what
the SDO_ELEM_INFO_ARRAY states, you have (1, 1003, 1), which according
to the Oracle documentation means:

"Simple polygon whose vertices are connected by straight line
segments. You must specify a point for each vertex, and the
last point specified must be exactly the same point as the first
(to close the polygon), regardless of the tolerance value. For
example, for a 4-sided polygon, specify 5 points, with point 5
the same as point 1"

And in fact following the mandated interpretation GeoServer fails
to read the above geometry complaining the ring making up the
exterior of the polygon is not closed.
I've changed the SDO_ELEM_INFO_ARRAY to (1, 1003, 3) and
I can display the rectangle.

After that I click on it in the GeoServer map preview
and it works fine for me? I also tried out with a reprojected
viewer displaying data in 4326 and it worked fine in that
case as well.

Btw, I've tested with the 1.7.3 official release so, if it's
not working for you, I'm missing some detail to actually reproduce
the issue.
Do you have the exact GetFeatureInfo request that makes it fail?

Cheers
Andrea

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

3

Hello,

Am 23.03.2009 10:09 schrieb Andrea Aime:

I've loaded the data using your provided script but there is something
wrong with it:

insert into district_test (objectid, alive, featurename, shape) values (42, 1, 'test',
SDO_GEOMETRY(2007, 31285, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1670.0625, 341194.008, 1666.21875, 341209.484))
);

You are right. The insert statement was wrong. But it does not work with a corrected one either.

Do you have the exact GetFeatureInfo request that makes it fail?

http://localhost:8080/geoserver/wms?REQUEST=GetFeatureInfo&EXCEPTIONS=application%2Fvnd.ogc.se_xml&BBOX=1662.38851%2C341192.602465%2C1674.025737%2C341210.889535&X=169&Y=266&INFO_FORMAT=text%2Fhtml&QUERY_LAYERS=pmb%3ADISTRICT_TEST&FEATURE_COUNT=50&Srs=EPSG%3A31285&Layers=pmb%3ADISTRICT_TEST&Styles=&WIDTH=350&HEIGHT=550&format=image%2Fpng

As you can see in the attached stacktrace I get an error at counting because the srid is null:
[oracle.sdo] - Using NULL SRID:

I have no idea why the srid is null. I also can't see a native srid in the featureType definition.
How do I set it?

Thanks,
Bernhard

20090323_geoserver_oracle_connectiontest_stacktrace.txt (10.6 KB)

4

Bernhard Kiselka ha scritto:

Hello,

Am 23.03.2009 10:09 schrieb Andrea Aime:

I've loaded the data using your provided script but there is something
wrong with it:

insert into district_test (objectid, alive, featurename, shape) values (42, 1, 'test',
SDO_GEOMETRY(2007, 31285, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1670.0625, 341194.008, 1666.21875, 341209.484))
);

You are right. The insert statement was wrong. But it does not work with a corrected one either.

Do you have the exact GetFeatureInfo request that makes it fail?

http://localhost:8080/geoserver/wms?REQUEST=GetFeatureInfo&EXCEPTIONS=application%2Fvnd.ogc.se_xml&BBOX=1662.38851%2C341192.602465%2C1674.025737%2C341210.889535&X=169&Y=266&INFO_FORMAT=text%2Fhtml&QUERY_LAYERS=pmb%3ADISTRICT_TEST&FEATURE_COUNT=50&Srs=EPSG%3A31285&Layers=pmb%3ADISTRICT_TEST&Styles=&WIDTH=350&HEIGHT=550&format=image%2Fpng

I just tried this request against the 1.7.3 official release + Oracle
NG + data coming from your script. I get a proper output, no exceptions:

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

<html>
   <head>
     <title>Geoserver GetFeatureInfo output</title>
   </head>
   <style type="text/css">
  table.featureInfo, table.featureInfo td, table.featureInfo th {
    border:1px solid #ddd;
    border-collapse:collapse;
    margin:0;
    padding:0;
    font-size: 90%;
    padding:.2em .1em;
  }
  table.featureInfo th{
      padding:.2em .2em;
    text-transform:uppercase;
    font-weight:bold;
    background:#eee;
  }
  table.featureInfo td{
    background:#fff;
  }
  table.featureInfo tr.odd td{
    background:#eee;
  }
  table.featureInfo caption{
    text-align:left;
    font-size:100%;
    font-weight:bold;
    text-transform:uppercase;
    padding:.2em .2em;
  }
   </style>
   <body>

<table class="featureInfo">
   <caption class="featureInfo">DISTRICT_TEST</caption>

   <tr>
   <th>fid</th>
     <th >SDE_ID</th>
     <th >ALIVE</th>
     <th >FEATURENAME</th>
     <th >SDEID</th>

     <th >VALIDFROM</th>
     <th >VALIDTO</th>
     <th >ACTION_OBJECTID</th>
   </tr>

     <tr>

   <td>DISTRICT_TEST.42</td>
       <td>0</td>

       <td>1</td>
       <td>test</td>
       <td>0</td>
       <td></td>
   </tr>

</table>
<br/>

   </body>
</html>

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

Are you really using the latest Oracle NG datastore coming with the
GeoServer 1.7.3 release?

As you can see in the attached stacktrace I get an error at counting because the srid is null:
[oracle.sdo] - Using NULL SRID:

I have no idea why the srid is null. I also can't see a native srid in the featureType definition.

I see a native SRID in both.

How do I set it?

By inserting a row with the SRID in the USER_SDO_GEOM_METADATA:

INSERT INTO
    "MDSYS"."USER_SDO_GEOM_METADATA" (
   "TABLE_NAME", "COLUMN_NAME","DIMINFO", "SRID"
    ) VALUES (
    'DISTRICT_TEST','SHAPE',
    SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -3000000, 3000000, 0.01), SDO_DIM_ELEMENT('Y', -5000000, 5000000, 0.01)),
    31285 <--- This is the SRID that GeoServer will read
);

If the same entry is in your USER_SDO_GEOM_METADATA, is the user you
specified in the connection able to query that view?

Cheers
Andrea

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

5

If the same entry is in your USER_SDO_GEOM_METADATA, is the user you
specified in the connection able to query that view?

That was it. The user was able to view all the data tables, but not the USER_SDO_GEOM_METADATA.

Thanks!

Bernhard

6

Hello Andrea,

If the same entry is in your USER_SDO_GEOM_METADATA, is the user you
specified in the connection able to query that view?

As mentioned yesterday I solved the problem by using the schema user (called GIPTEST) and not another user that was only allowed to read all data schema but not the MDSYS schema (called GIP_READ).

That is why I could see the tables, but could not get the srid.

But today my colleague pointed out how it could work:
If you use ALL_SDO_GEOM_METADATA it also works with GIP_READ, because that view is not restricted to a user like the view USER_SDO_GEOM_METADATA.

Which means this does not work:
select SRID from USER_SDO_GEOM_METADATA where TABLE_NAME = 'DISTRICT_TEST';

while this works:
select SRID from ALL_SDO_GEOM_METADATA where TABLE_NAME = 'DISTRICT_TEST';

Maybe this is a thing to keep in mind, if you want to use another user than the schema user (owner)?

best regards,
Bernhard

7

Bernhard Kiselka ha scritto:
...

But today my colleague pointed out how it could work:
If you use ALL_SDO_GEOM_METADATA it also works with GIP_READ, because that view is not restricted to a user like the view USER_SDO_GEOM_METADATA.

Which means this does not work:
select SRID from USER_SDO_GEOM_METADATA where TABLE_NAME = 'DISTRICT_TEST';

while this works:
select SRID from ALL_SDO_GEOM_METADATA where TABLE_NAME = 'DISTRICT_TEST';

Maybe this is a thing to keep in mind, if you want to use another user than the schema user (owner)?

Hi Bernard,
yeah, I'm aware of that issue and also about the solution, and tracked
it here:
http://jira.codehaus.org/browse/GEOT-2386

A proper solution requires the usage of both tables, first the "user"
one, and if it fails, the "all" one. The reasoning is that the
schema parameter is optional, if the user did not provide one querying
the "all" table may result in duplicate results (same name, different
schema). A better solution would be to change the ft parsing code
so that it stores which schema each table is coming from, I guess
I'll have to check both solutions for feasibility.

So, I'll eventually work on that, I'm just not finding enough spare
time to work on it lately.

Cheers
Andrea

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