help with error "interface not supported without a spatial index"

Sergio Palma Hidalgo · Jul 31, 2018 12:50PM

I have a problem! I'm new in Oracle Spatial, but I've been working with pl/sql for a while. The thing is, I need to look for points (x,y) from geometry attribute from table B, inside geometry attribute (polygons) from table A. The issue: both tables have different data inside geometry field.

More details:

<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span>

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">CREATE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> TABLE A <br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>GEOMETRY MDSYS</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">SDO_GEOMETRY</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>MANZENT VARCHAR2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">255</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> BYTE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">...</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/><br/></span>

<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">ID_GIS NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">8</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">0</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>GEOMETRY MDSYS</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">SDO_GEOMETRY</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">...</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">CREATE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TABLE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> B <br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">  </span>

When I run the Query:

SELECT A.MANZENT, A.GEOMETRYFROM A, BWHERE MDSYS.SDO_RELATE(A.GEOMETRY, B.GEOMETRY, 'mask=touch+coveredby') = 'TRUE';

It throw an error:

ORA-13226: Interface Not Supported Without a Spatial Index

ORA-06512: at "MDSYS.MD", line 1723

ORA-06512: at "MDSYS.MDERR", line 8

ORA-06512: at "MDSYS.SDO_3GL", line 70

13226. 00000 - "interface not supported without a spatial index"

*Cause: The geometry table does not have a spatial index.

*Action: Verify that the geometry table referenced in the spatial operator

has a spatial index on it.

Well, I looked for the Index in both tables, and it seems to be ok!:

  CREATE INDEX "DS_FDOX"."A_IDX" ON "DS_FDOX"."A" ("GEOMETRY")   INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

  CREATE INDEX "DS_FDOX"."B_IDX" ON "DS_FDOX"."B" ("GEOMETRY")   INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

Both tables has their metadata (ALL_SDO_GEOM_METADATA and USER_SDO_GEOM_METADATA) but; attribute RID NULL :

The SRDI should be 32719.

* UPDATE *

Checking the Index Status i get error on table A spatial Index:

select table_name, index_name, status, ityp_name, domidx_status, domidx_opstatusfrom USER_INDEXESwhere ityp_name='SPATIAL_INDEX';

A_IDX I got "FAILED" in DOMIDX_OPSTATUS!

So, I deleted the spatial index A_IDX, but when I'm creating again the index:

CREATE INDEX A_IDX ON A (GEOMETRY)INDEXTYPE IS MDSYS.SPATIAL_INDEX;

I get this error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-13249: internal error in Spatial index: [mdidxrbd]

ORA-13249: Error in Spatial index: index build failed

ORA-13249: Error in spatial index: [mdrcrtxfergm]

ORA-13249: Error in spatial index: [mdpridxtxfergm]

ORA-13200: Internal error [ROWID:AAB64sAAEAAAGvsAAA] in spatial indexing

ORA-13206: Internal error [] while creating the spatial index

ORA-13365: Layer SRID does not match geometry SRID

ORA-06512: in "MDSYS.SDO_INDEX_METHOD_10I", line 10

The metadata from table A, in tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA; in both tables the attribute SRID is null.

But the metadata from table B in tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA is SRID 32719.

How can I determine the right SRID for table A? To update the attribute SRID at tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA.

More details:

- USER_SDO_GEOM_METADATA for table A:

Select * from USER_SDO_GEOM_METADATA WHERE table_name = 'A';

Result:

Thanks in advance!

Edited by: Sergio Palma H.

[Deleted User] · Jul 31, 2018 4:11PM

Hi Sergio,

Your update quite clearly shows the problem:

ORA-13365: Layer SRID does not match geometry SRID

Which means that the SRID in the geometries is not the same as in the user_sdo_geom_metadata. There's no need to check all_sdo_geom_metadata as well, they are both views on the same table (but user only shows the sdo metadata for the user, all shows all metadata your user has access to).

So: easy fix, update your user_sdo_geom_metadata entry with the proper srid.

BTW: what is your database version? These days it is not necessary anymore to put mdsys. in front of everything. Oh, and because you are comparing points and polygons, I'd simply use the anyinteract operator: sdo_anyinteract(geom1, geom2)='TRUE' is sufficient for what you need.

Lastly: when dealing with spatial indexes, it is also a good idea to check user_sdo_index_metadata and user_sdo_index_info, they contain specific spatial index information. And if you want to find sdo_geometry columns without a spatial index:

select table_name,        column_name from user_tab_cols  where data_type='SDO_GEOMETRY' and        table_name || '.' || column_name not in        (         select table_name || '.' || column_name          from user_sdo_index_info        );

[Deleted User] · Jul 31, 2018 4:11PM

Hi Sergio,

Your update quite clearly shows the problem:

ORA-13365: Layer SRID does not match geometry SRID

Which means that the SRID in the geometries is not the same as in the user_sdo_geom_metadata. There's no need to check all_sdo_geom_metadata as well, they are both views on the same table (but user only shows the sdo metadata for the user, all shows all metadata your user has access to).

So: easy fix, update your user_sdo_geom_metadata entry with the proper srid.

BTW: what is your database version? These days it is not necessary anymore to put mdsys. in front of everything. Oh, and because you are comparing points and polygons, I'd simply use the anyinteract operator: sdo_anyinteract(geom1, geom2)='TRUE' is sufficient for what you need.

Lastly: when dealing with spatial indexes, it is also a good idea to check user_sdo_index_metadata and user_sdo_index_info, they contain specific spatial index information. And if you want to find sdo_geometry columns without a spatial index:

select table_name,        column_name from user_tab_cols  where data_type='SDO_GEOMETRY' and        table_name || '.' || column_name not in        (         select table_name || '.' || column_name          from user_sdo_index_info        );

Sergio Palma Hidalgo · Jul 31, 2018 5:45PM

Thank you for your time and for answer Stefan!

What is your database version?

- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

First only table B had its SRID (32719), in table USER_SDO_GEOM_METADATA. But table A has a null in SRID in USER_SDO_GEOM_METADATA.

So I updated the SRID to table A, same than table B in USER_SDO_GEOM_METADATA.

Now both tables A and B has SRID 32719 in USER_SDO_GEOM_METADATA.

But wow, when I'm creating spatial index of A.. it explode :

CREATE INDEX A_IDX ON A (GEOMETRY)

INDEXTYPE IS MDSYS.SPATIAL_INDEX;

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-13249: internal error in Spatial index: [mdidxrbd]

ORA-13249: Error in Spatial index: index build failed

ORA-13249: Error in spatial index: [mdrcrtxfergm]

ORA-13249: Error in spatial index: [mdpridxtxfergm]

ORA-13200: Internal error [ROWID:AAB64sAAEAAAGvsAAA] in spatial indexing

ORA-13206: Internal error [] while creating the spatial index

ORA-13365: Layer SRID does not match geometry SRID

ORA-06512: in "MDSYS.SDO_INDEX_METHOD_10I", line 10

But the command create a 'partial' spatial index, with wrong data...

Thank you for all the tips you wrote (all are welcome), I'm new in Oracle spatial. Thank you!

(Sorry my english is not good!)

Edited: Sergio Palma Hidalgo.

[Deleted User] · Aug 1, 2018 3:03AM

Hi Sergio,

Since Oracle is still giving you the same error, table A must have some other SRID in the sdo_geometry than what is now in user_sdo_geom_metadata.

You can check to see what it is with something like this:

select distinct a.geometry.sdo_sridfrom a;

Note that, depending on how many rows you have in your table, this query might take a while.

Once the srid of the geometry and the table are exactly the same, drop the old index, and try to create a new one.

Note that version 10.2 is rather old and not supported anymore (and in 10 you still need to put mdsys. in front of everything, so ignore that comment of me earlier), so I would strongly advise to upgrade.

Regards,

Stefan

Rick Anderson-Oracle · Aug 1, 2018 3:21PM

Hi, Sergio!

Just to reiterate what Stefan said above, Oracle Spatial v10.x is not only NOT supported but it is ANCIENT!

I highly recommend upgrading to *at least* v12.2 and also note that v18.x has just become available.

FYI

Rick

Sergio Palma Hidalgo · Aug 1, 2018 3:57PM

Hi Stefan!

Thank you for your answer.

Here we go, I ran the Script you told me:

select distinct a.geometry.sdo_srid
from a;

...Boom! The a.geometry.sdo_srid attribute had a Null.

Now table B, with the same Script it had SRID 32719.

So, updated "a.geometry.sdo_srid" attribute with the value in "b.geometry.sdo_srid", It means both tables with the same SRID 32719.

Looking for how to do that, I found this script:

<span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">UPDATE</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> A T <br/></span><span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">SET</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> T</span><span class="pun" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">.</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">GEOMETRY</span><span class="pun" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">.</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">SDO_SRID </span><span class="pun" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">=</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span style="color: #7d2727; font-weight: inherit; font-family: inherit; font-style: inherit;">32719</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">WHERE</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> T</span><span class="pun" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">.</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;">GEOMETRY </span><span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">IS</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">NOT</span><span class="pln" style="color: #303336; font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="color: #101094; font-style: inherit; font-weight: inherit; font-family: inherit;">NULL</span>

But first:

1.- the Spatial index of table A (in order to run the update) must be deleted.

2.- I successfully ran the update!

3.- Recreate the spatial index for Table A.

4.- I ran the query (with fix you told me):

SELECT    A.MANZENT, A.GEOMETRY, B.GEOMETRY  FROM     A, B  WHERE     SDO_ANYINTERACT(A.GEOMETRY, B.GEOMETRY) = 'TRUE';

But nothing happens, it can be running for a lot of time without response.

Finally:

It should be noted that, the data in Geometry attribute in both tables has a different format:

Table A, data in attribute Geometry:

MDSYS.SDO_GEOMETRY(3002, 32719, NULL,

MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1),

MDSYS.SDO_ORDINATE_ARRAY(351059.668003312, 6283365.86125775, 0, 350989.900040253, 6283352.35005758, 0, 350945.450026879, 6283344.01994977, 0, 350942.789988843, 6283344.05999729, 0, 350941.519995767, 6283344.33999171, 0, 350939.949994132, 6283345.17999723, 0, 350938.640010838, 6283346.53002549, 0, 350937.800033809, 6283348.69002867, 0, 350932.586269235, 6283379.08172448, 0, 351052.837823787, 6283402.34153528, 0, 351059.668003312, 6283365.86125775, 0, 351059.668003312, 6283365.86125775, 0) --> Geometry

)

Table B, attribute Geometry:

MDSYS.SDO_GEOMETRY(2001, 32719,

MDSYS.SDO_POINT_TYPE(352761.049, 6287740.01, NULL), NULL, NULL) --> Points

Now, It seems to be that spatial index problems are gone (thnks so much).

Let's come back to the original question (I know, I know; it's another price...):

- I need to look for points (x,y) from geometry attribute from table B, inside geometry attribute (polygons) from table A. The issue: both tables have different data inside geometry field

I found this example for SDO_ANYINTERACT:

SELECT c.mkt_id, c.name  FROM cola_markets c  WHERE SDO_ANYINTERACT(c.shape,  SDO_GEOMETRY(2003, NULL, NULL,  SDO_ELEM_INFO_ARRAY(1,1003,3),  SDO_ORDINATE_ARRAY(4,6, 8,8))  ) = 'TRUE';

I don't know how to cross two tables (B in A) with this syntax (with both data attributes data differents). I don't see how to do that!

Regards,

Sergio.

Sergio Palma Hidalgo · Aug 1, 2018 4:05PM

Hi Rick,

I understand that the version is obsolete, but the version is imposed by the company. They work with that version. For now as a freelancer, I must work with that version.

Regards,

Sergio.

[Deleted User] · Aug 2, 2018 4:14AM

Hi Sergio,

First of all: I think it would be wise for you to read the Spatial documentation. I've found the link to the 10 docs again: https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/toc.htm

All of the concepts etc are explained pretty well in there (and you can always ask here if something isn't clear).

It should be noted that, the data in Geometry attribute in both tables has a different format:

When you read the docs, you'll see that that is actually as it should be. Point geometries are stored differently from lines and polygons.

But nothing happens, it can be running for a lot of time without response.

No response at all? Do you have any means of looking at the data visually, in a map? There may not be any overlap between the geometries in the two tables. How many rows do you have in table A and in Table B? You could try sdo_filter first, that will give you at least a rough idea. Or add an extra where-clause, limiting yourself to one or two polygons and see how everything works.

I don't know how to cross two tables (B in A) with this syntax (with both data attributes data differents). I don't see how to do that!

The same way as I told you. Oracle knows how to deal with the fact that points are stored differently from polygons, you don't need to worry about that. I would strongly recommend you read the documentation from the beginning, because that should make things a lot clearer for you. I just noticed that in the documentation that I linked above a lot of the links don't work anymore, so here is the 11 documentation: https://docs.oracle.com/cd/E11882_01/appdev.112/e11830/toc.htm

Try something like this, to limit the number of polygons that Oracle considers:

select a.manzent,        a.geometry,        b.geometry from a,      b where sdo_anyinteract(a.geometry, b.geometry) = 'true' and      a.manzent = <something>;

And see if that returns anything. Then, if you specifically need the x and y of the points, you can get them like this: b.geometry.SDO_POINT.X and b.geometry.SDO_POINT.Y (and also Z, if you have them).

HTH,

Stefan

[Deleted User] · Aug 2, 2018 4:16AM

user11202270 wrote:Hi Rick,I understand that the version is obsolete, but the version is imposed by the company. They work with that version. For now as a freelancer, I must work with that version.Regards,Sergio.

Ah. No choice there then I'm afraid. I'd still recommend to them that they upgrade if I were you, but that it is also their decision of course. Then you have stated your concerns, but leave the choice to them, and they can never claim you never said anything if things go wrong.

Hopefully they are bit more modern in other aspects of your work

Regards,

Stefan

_jum · Aug 2, 2018 9:59AM

There are some pitfalls in the Stefan's query:

    select a.manzent,              a.geometry,              b.geometry       from a,     b       where sdo_anyinteract(a.geometry, b.geometry) = 'true' and            a.manzent = <something>;

- you should not filter the first (indexed) table a, because ORACLE could decide not to use the spatial index on this table
- filter only the second table b
- 'TRUE' must be written in capitals

- you could try to add an INDEX-hint to force ORACLE to use your spatial index (index A_IDX on on table a with required alias atab)

    select /*+ INDEX (atab A_IDX) */           atab.manzent,              atab.geometry,              b.geometry       from a atab,            b       where sdo_anyinteract(atab.geometry, b.geometry) = 'TRUE' and            b.ID_GIS = <something>;

Sergio Palma Hidalgo · Aug 3, 2018 11:39AM

Hi Stefan.

First of all: I think it would be wise for you to read the Spatial documentation. I've found the link to the 10 docs again: https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/toc.htmAll of the concepts etc are explained pretty well in there (and you can always ask here if something isn't clear).

Thank you, and yes I'm aware about that, actually I'm already reading the reference, after that I'm going to take the Oracle Spatial: Essentials course in Oracle University (Link here). It looks like a good start to an amateur in Oracle Spatial!

Besides that, I have the Getting Started with Oracle Spatial by Tim Armitage* guide. And Oracle Spatial Developer's Guide 11g Release 2** document as well

* http://download.oracle.com/otndocs/products/spatial/pdf/au_melbourne06_start.pdf

** http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/toc.htm

No response at all?

Nop, it get freeze. I mean the Oracle application (Oracle SQL Developer v 12.2.0.183) after a while anyway.

Do you have any means of looking at the data visually, in a map?

Yes, the data it's visualized in Autodesk Mapguide 6.5 (I know, I know it's a really old Autodesk version).

There may not be any overlap between the geometries in the two tables

Maybe not, the query is looking for if there is any overlap between dots inside the polygons. But the Query has no why to get freeze. For sure I'm doing something wrong or I need to do something else.

How many rows do you have in table A and in Table B?

Table A: 9986 rows

Table B: 304873 rows

...mmm maybe I should wait more time, wait to the query to work. Wait for the response and result, be good or wrong..

You could try sdo_filter first, that will give you at least a rough idea. Or add an extra where-clause, limiting yourself to one or two polygons and see how everything works

It didn't work either

...Do you know something? Let me tell you:

The company project manager came to my desk and told me...

- "mmm you know Sergio, we've checked the errors you reported... And we found out that we create in the wrong way the Schema where you are working... It's Spatial, but it is not good set uped... Of course we're are already working on that, but it's going to take as a while".

That's why nothing what I was doing worked!

- "...But meanwhile you can keep going. doing this and that..."

So... After they fixed the errors, everything ran perfectly.

I was pretty sure that we wasn't wrong... Something wasn't good.

Again: I'm going to take the Oracle Spatial: Essentials course in Oracle University

I keep loving what I do for living.

I'm a really happy engineer again (until the next error... hahahaha)

Sorry for the bad english! I'm working on that too!

Thank you Stefan.

Regard,

Sergio.

Forum Stats

Quick Links

Categories

help with error "interface not supported without a spatial index"

* UPDATE *

Best Answer

Answers

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

Forum Stats

Categories

help with error "interface not supported without a spatial index"

*** UPDATE ***

Best Answer

Answers

* UPDATE *