CODEWITHSUNDEEP
oracle-databaseviewspatial-indexoracle-spatial
Akarius
Akarius

Reputation: 398

Oracle Spatial Index on a View of multiple tables

I have three big tables with same structure (BATHY_SHC_1, BATHY_SHC_2 and BATHY_SHC_3), each with a SDO_GEOMETRY column “POINT_PP”, the spatial index of each is VALID. I have made a view on these tables that includes this geometry column (V_BATHY_SHC). I can make spatial request on the view to find all point within a rectangle like this with correct results:

SELECT PT_ID, POINT_PP from V_BATHY_SHC
WHERE SDO_RELATE(POINT_PP, MDSYS.SDO_GEOMETRY(2003, 32618, null, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(635267,5037808, 635277,5037818)), 'mask=anyinteract') = 'TRUE';

I have upload a polygon MASK in a table MNE_MASK, added a line in metadata and created a spatial index (as usual). It has a valid spatial index. The geometry is in the same SRID (32618). Then I want to get all points from the view that are within the polygon from the MNE_MASK table. If I made the query on one of the table, I get correct results:

SELECT A.PT_ID, A.POINT_PP
FROM BATHY_SHC_1 A, MNE_MASK B
WHERE B.MNE_ID = 1
AND SDO_RELATE(A.POINT_PP, B.MASK, 'mask=ANYINTERACT ') = 'TRUE';

But if I made it on the view like this:

SELECT A.PT_ID, A.POINT_PP
FROM V_BATHY_SHC A, MNE_MASK B
WHERE B.MNE_ID = 1
AND SDO_RELATE(A.POINT_PP, B.MASK, 'mask=ANYINTERACT ') = 'TRUE';

I got this 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 94

In the past, I’ve always made queries on spatial index of views of multiple tables without problem.
I can make spatial query on both without issue but I can’t make a SDO_RELATE between them… Why is this one any different? Thanks a lot for your insight and help!

Edit: I've found a quick workaround but it doesn't explain why. If I swap the two first params in the SDO_RELATE function, the request works.

SELECT A.PT_ID, A.POINT_PP
FROM V_BATHY_SHC A, MNE_MASK B
WHERE B.MNE_ID = 1
AND SDO_RELATE(B.MASK, A.POINT_PP, 'mask=ANYINTERACT ') = 'TRUE';

Upvotes: 0

Views: 1048

Answers (1)

JvdLinden
JvdLinden

Reputation: 1

In SDO_RELATE, the first parameter is a geometry column in a table, whereas the second is a single geometry. This said, I wonder why your original query works. There you have the view column as the first parameter, just like in the failing query.

Upvotes: 0

Related Questions