[Geoserver-users] inserting features with WFS-T into OracleSp atial data store fails because of incorrect SRID

Daniel_Goetz · August 28, 2006, 8:33am

Hi coummunity,

I am refering to a email by Paul Michael (or Michael Paul). He wrote the following:

Hi folks,

with the hints posted previously to this list, I have generated a small
patch that is working OK for my purposes. To explain, whenever a geometry
has set an SRID of 0 (which does not exist in Oracle), now the code is
assigning the NULL value for the SRID. Hence, the fix to me works fine as
the SRID column contains NULL within user_sdo_geom_metadata view in our
environment. I assume that most other people will also find a null value
there, as most tools which load data into Oracle Spatial do not assign any
SRID and leave this column NULL.

A real bug fix should obtain the correct SRID value from the
user_sdo_geom_metadata view and reproject the geometry to be
inserted/updated if necessary.

Please find attached a patched .jar file which I am currently using for
geoserver + Oracle Spatial, as well as the changed source file (the changed
line is 130).
Diff:
130c130
< NUMBER SDO_SRID = srid == SDO.SRID_NULL ? null :
---
> NUMBER SDO_SRID = (srid == SDO.SRID_NULL || srid == 0) ? null :

Regards,

Michael.

I am also using oracle spatial and want to do a WFS-T insert request. But it always fails saying:

org.geotools.data.DataSourceException: SQL Exception writing geometry columnORA-29875: Routine ODCIINDEXINSERT nicht erfolgreich ausgeführt
ORA-13365: Ebenen-SRID stimmt nicht mit Geometrie-SRID überein
ORA-06512: in "MDSYS.SDO_INDEX_METHOD_9I", Zeile 210
ORA-06512: in Zeile 1

In his mail Paul/Michael says he found the reason why it fails and he has a solution . He therefor attached a .jar file to his e-mail. But unfortunately I have no access to this email and the attachment. And I do not know his email-address.

Could anybody help me? Perhaps Michael/Paul reads this email. Would you please be so kind and contact me? madprof(at)gmx.ch. Thank you so much.

regards

Daniel
--

Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

Andrea_Aime4 · August 28, 2006, 11:40am

Daniel Goetz ha scritto:

Hi coummunity,

I am refering to a email by Paul Michael (or Michael Paul). He wrote the following:

Hi folks,
with the hints posted previously to this list, I have generated a small
patch that is working OK for my purposes. To explain, whenever a geometry
has set an SRID of 0 (which does not exist in Oracle), now the code is
assigning the NULL value for the SRID. Hence, the fix to me works fine as
the SRID column contains NULL within user_sdo_geom_metadata view in our
environment. I assume that most other people will also find a null value
there, as most tools which load data into Oracle Spatial do not assign any
SRID and leave this column NULL.
A real bug fix should obtain the correct SRID value from the
user_sdo_geom_metadata view and reproject the geometry to be
inserted/updated if necessary.

This seems to have been addressed in:

http://jira.codehaus.org/browse/GEOT-319

Please find attached a patched .jar file which I am currently using for
geoserver + Oracle Spatial, as well as the changed source file (the changed
line is 130). Diff: 130c130
< NUMBER SDO_SRID = srid == SDO.SRID_NULL ? null :
---
> NUMBER SDO_SRID = (srid == SDO.SRID_NULL || srid == 0) ? null :

I checked this one too, this is the code in "trunk" and in the 2.2.0 release:

NUMBER SDO_SRID = (srid == SDO.SRID_NULL || srid == 0) ? null :
new NUMBER( srid );

so it seems the patch have been applied. What version of geoserver are
you running against? And btw, can you translate the error message from german
to english? This could be source of insight for us.

Cheers
Andrea Aime

Daniel_Goetz · August 28, 2006, 1:57pm

Hi Andrea, hi community,

thank you for your reply. I am using geoserver 1.4-M1 (windows installer) which is based on geotools 2.2.0. Moreover I am running apache 2.0.58 (do I need to have apache? I have installed it because I am experimenting on umn-mapserver and mapbender too).

The bug in the codehouse-link (http://jira.codehaus.org/browse/GEOT-319) you posted seems already to be fixed in my version because the sql-statement where geoserver asks for the SRID in the ALL_SDO_GEOM_METADATA is valid. I have checkd this in SQL-plus:

SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME='TEST_LINE' AND COLUMN_NAME='GEOM';

The result from that sql-statement is: SRID = 82032.

The translated error message, that geoserver produces, says the following:

org.geotools.data.DataSourceException: SQL Exception writing geometry columnORA-29875: Routine ODCIINDEXINSERT is not executed successfully
ORA-13365: Layer-SRID does not correspond to the Geometry-SRID
ORA-06512: in "MDSYS.SDO_INDEX_METHOD_9I", line 210
ORA-06512: in line 1

Would be great if you or somebody else could help me further on. I have no idea what to do. Is there anybody that could do WFS-T insert requests in a oracle spatial database?

Perhaps there is one thing to mention: I try to do this WFS-T insert request by modifying a example-request in the "demo" section of the geoserver admin tool. the gml of my request look as follows:

<?xml version="1.0"?>
<wfs:Transaction service="WFS" version="1.0.0"
  xmlns:wfs="http://www.opengis.net/wfs"
  xmlns:daniel="http://localhost:8080/daniel"
  xmlns:gml="http://www.opengis.net/gml"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.0.0/WFS-transaction.xsd http://localhost:8080/daniel http://localhost:8080/geoserver/wfs/DescribeFeatureType?typename=daniel:TEST_LINE">
  <wfs:Insert>
    <daniel:TEST_LINE>
<daniel:GID>8</daniel:GID>
  <daniel:DELETED>0</daniel:DELETED>
   <daniel:GEOM>
    <gml:LineString srsName="http://www.opengis.net/gml/srs/epsg.xml#31468">
     <gml:coordinates decimal="." cs="," ts=" ">4438663.515647338,5477560.203464035 4440886.895813662,5476924.951987942
     </gml:coordinates>
    </gml:LineString>
   </daniel:GEOM>
</daniel:TEST_LINE>
  </wfs:Insert>
</wfs:Transaction>

the URL of this request is: http://127.0.0.1:8080/geoserver/wfs

Is there a better way to send a gml-WFS-request to geoserver?

Thank you for any further help. Thank you.

regards

Daniel

-------- Original-Nachricht --------
Datum: Mon, 28 Aug 2006 13:40:16 +0200
Von: Andrea Aime <andrea.aime@anonymised.com>
An: Daniel Goetz <madprof@anonymised.com>
Betreff: Re: [Geoserver-users] inserting features with WFS-T into OracleSp atial data store fails because of incorrect SRID

Daniel Goetz ha scritto:
> Hi coummunity,
>
> I am refering to a email by Paul Michael (or Michael Paul). He wrote
the following:
>
> Hi folks,
>
> with the hints posted previously to this list, I have generated a small
> patch that is working OK for my purposes. To explain, whenever a
geometry
> has set an SRID of 0 (which does not exist in Oracle), now the code is
> assigning the NULL value for the SRID. Hence, the fix to me works fine
as
> the SRID column contains NULL within user_sdo_geom_metadata view in our
> environment. I assume that most other people will also find a null
value
> there, as most tools which load data into Oracle Spatial do not assign
any
> SRID and leave this column NULL.
>
> A real bug fix should obtain the correct SRID value from the
> user_sdo_geom_metadata view and reproject the geometry to be
> inserted/updated if necessary.

This seems to have been addressed in:

http://jira.codehaus.org/browse/GEOT-319

> Please find attached a patched .jar file which I am currently using for
> geoserver + Oracle Spatial, as well as the changed source file (the
changed
> line is 130).
> Diff:
> 130c130
> < NUMBER SDO_SRID = srid == SDO.SRID_NULL ? null :
> ---
> > NUMBER SDO_SRID = (srid == SDO.SRID_NULL || srid == 0) ? null
:

I checked this one too, this is the code in "trunk" and in the 2.2.0
release:

NUMBER SDO_SRID = (srid == SDO.SRID_NULL || srid == 0) ? null :
new NUMBER( srid );

so it seems the patch have been applied. What version of geoserver are
you running against? And btw, can you translate the error message from
german
to english? This could be source of insight for us.

Cheers
Andrea Aime

--

Echte DSL-Flatrate dauerhaft für 0,- Euro*. Nur noch kurze Zeit!
"Feel free" mit GMX DSL: http://www.gmx.net/de/go/dsl