Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Spatial

Posted on 2003-03-24
11
Medium Priority
?
8,732 Views
Last Modified: 2013-12-11
Hello,

      i was trying to insert new values into a table which has spatial data .i encountered the following problem ..

  could anyone help me out with the problem.



SQL> insert into geometry(geo_name,westbc,eastbc,id,northbc,southbc,shape)
  2  select * from europe_id;
insert into geometry(geo_name,westbc,eastbc,id,northbc,southbc,shape)
*
FEHLER in Zeile 1:
ORA-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 319
ORA-06512: in Zeile 1


???????????????????
0
Comment
Question by:libin_expert
  • 7
  • 4
11 Comments
 
LVL 2

Accepted Solution

by:
Datamonkey earned 100 total points
ID: 8197040
When creating a spatial index on a table you must specify the coordinate system that the data is stored in (this is called the Spatial Reference ID or SRID).

Each spatial index can only index data in a single coordinate system - for instance, you cannot store Lat/Long data in the same index as British National Grid.

The ORA-13365 error ("Layer SRID does not match Geometry SRID") is complaining that you are attempting to insert data that is in a different coordinate system to the data already indexed in that table.

NB: NULL SRID is treated as "valid" SRID representing Cartesian ("flat earth") data and thus you cannot load NULL SRIDs into a table which already has data indexed in a different SRID.

To check the SRIDs in both tables:
select distinct nvl(g.shape.sdo_srid,0) from geometry g ;
select distinct nvl(g.shape.sdo_srid,0) from europe_id g ;

Both queries should return the same, single number if you intend to insert them into the same index.

0
 

Author Comment

by:libin_expert
ID: 8201510
thank you ur anwser was helpful !! when i try to access these tables from mapextreme here are the errors i get ..do i need to change the srids ? how could i do that?

 to my knowledge  to change srid .first i need to change the srid in the MDSYS.SDO_GEOMETRY  and then manually in the user_sdo_geom_metadata.


   could u help me with this ??

here is the error i got while i try to access europe_admin_geometry



java.lang.RuntimeException: Error rendering: Message=ORA-13226: Oberfläche ohne räumlicher Index nicht unterstützt
ORA-06512: in "MDSYS.MD", Zeile 1723
ORA-06512: in "MDSYS.MDERR", Zeile 8
ORA-06512: ine  "MDSYS.SDO_3GL", Zeile 336
 at com.mapinfo.mapxtreme.client.MapXtremeImageRenderer.c(Unknown Source)
 at com.mapinfo.mapxtreme.client.MapXtremeImage Renderer.toImage(Unknown Source)
 at com.mapinfo.beans.vmapj.de.a(Unknown Source)
 at com.mapinfo.beans.vmapj.de.end(Unknown Source)
 at com.mapinfo.mapxtreme.client.MapXtremeImag eRenderer.render(Unknown Source)
 at com.mapinfo.graphics.AsyncRenderer.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)




And the second errors with the table geometry are:
 
java.lang.RuntimeException: Error rendering: Message=ORA-29903: Fehler bei der Ausführung von Routine ODCIIndexFetch()
ORA-13236: Interner Fehler bei Verarbeitung von R-Baum: []
ORA-13236: Interner Fehler bei Verarbeitung von R-Baum: [Recursive fetch error]
ORA-29400: Data Cartridge-Fehler
ORA-00942: Tabelle oder View nicht vorhanden
 

 at com.mapinfo.mapxtreme.client.MapXtremeImageRenderer.c(Unknown Source)
 at com.mapinfo.mapxtreme.client.MapXtremeImage Renderer.toImage(Unknown Source)
 at com.mapinfo.beans.vmapj.de.a(Unknown Source)
 at com.mapinfo.beans.vmapj.de.end(Unknown Source)
 at com.mapinfo.mapxtreme.client.MapXtremeImage Renderer.render(Unknown Source)
 at com.mapinfo.graphics.AsyncRenderer.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)


waiting for answers !!

0
 

Author Comment

by:libin_expert
ID: 8201526
one more addition

first table europe was uploaded using mapinfo and has a srid 8307


geometry table the second one has a srid NULL

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:libin_expert
ID: 8201815
does anybody know why ?

  when i select distinct SRID from MDSYS.SDO_GEOMETRY column of a table i get null but when i select the SRID from user_sdo_geom_metadata for the same table i get the value 8265

??????????


SQL> select distinct g.shape.sdo_srid from geometry g;

SHAPE.SDO_SRID
--------------



SQL> select table_name ,srid from user_sdo_geom_metadata
  2  where table_name like '%GEOMETRY%';

TABLE_NAME                             SRID
-------------------------------- ----------
GEOMETRY                               8265

????????????????????????
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8204537
What coordinate system is the geometry data actually in? Is it WGS84 (SRID=8307) or NAD83 (SRID=8265) or Cartesian (SRID=NULL)?

These are each different. You shouldn't just set the SRID arbitrarily - it has to represent the actual coordinate system of the data.

If you must, you can use update to force the SRID to whatever value you want it to be (e.g. update geometry g set g.geometry.sdo_srid=8265), I would strongly suggest ensuring the data is correct and valid NAD83 data before doing this though.

I suspect that if the geometry SRID is NULL, that means your data is Cartesian and hence incompatible with the data in the europe table.
0
 

Author Comment

by:libin_expert
ID: 8205451
The answer was what i was needing but do u know why ??

when i select distinct SRID from MDSYS.SDO_GEOMETRY column of a table i get null but when i select the SRID from user_sdo_geom_metadata for the same table i get the value 8265

??????????


SQL> select distinct g.shape.sdo_srid from geometry g;

SHAPE.SDO_SRID
--------------



SQL> select table_name ,srid from user_sdo_geom_metadata
 2  where table_name like '%GEOMETRY%';

TABLE_NAME                             SRID
-------------------------------- ----------
GEOMETRY                               8265

????????????????????????


0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8209142
The values are independently set: they are whatever you (or your application) has set them to.

The metadata isn't generated from the table data or vice-versa - you (or your application) insert them both seperately.

Similarly Oracle won't force you keep the two values in sync, although you won't be able to build a spatial index until they are the same.

You can either update them by hand in SQL if you're sure of the values they should be, or examine why your application and source data to see where the inconsitent values are coming from.
0
 

Author Comment

by:libin_expert
ID: 8211264
        I appricate for the help you are giving . well while i was trying to update the SRID of geometry table
i get the following error.. could u help me with updating srid and setting up index ?

SQL> update geometry g set g.shape.sdo_srid =8307;
update geometry g set g.shape.sdo_srid =8307
*
FEHLER in Zeile 1:
ORA-29877: Routine ODCIINDEXUPDATE nicht erfolgreich ausgef©úhrt
ORA-13037: SRIDs stimmen f©úr die beiden Geometrien nicht ©úberein
ORA-13037: SRIDs stimmen f©úr die beiden Geometrien nicht ©úberein
ORA-06512: in "MDSYS.SDO_INDEX_METHOD_9I", Zeile 319
ORA-06512: in "MDSYS.SDO_IDX", Zeile 16
ORA-06512: in "MDSYS.SDO_INDEX_METHOD_9I", Zeile 368


0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8211867
The geometry table already has a spatial index on it. You need to drop the index first before you update the SRIDs (as above, you cannot mix SRIDs in an index)

Don't forget to check your metadata SRID matches before you recreate the index though.

0
 

Author Comment

by:libin_expert
ID: 8216820
thanks a lot for all ur help !!

     well i got a srid 8307 for the table geometry.
i created a following index on it ..do u have a suggestion ??


CREATE INDEX geometry_idx
ON geometry(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;


laterz
0
 

Author Comment

by:libin_expert
ID: 8216824
thanks a lot for all ur help !!

     well i got a srid 8307 for the table geometry.
i created a following index on it ..do u have a suggestion ??


CREATE INDEX geometry_idx
ON geometry(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;


laterz
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question