?
Solved

Oracle Spatial

Posted on 2003-03-24
11
Medium Priority
?
8,491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 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