SQL Developer

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.

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_srid
from 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

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

Hi Stefan!

Thank you for your answer.

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

1. select distinct a.geometry.sdo_srid 
2. 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:

UPDATE A T 
SET T.GEOMETRY.SDO_SRID = 32719
WHERE T.GEOMETRY IS NOT NULL

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.

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.

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

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

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>;  

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.htm
All 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.

Hi Sergio,

I was just about to reply that you have a serious problemw ith your database, because 40.000 rows is a very small dataset. That simple query should have returned instantly, but apparently your project manager found out himself

Good luck with your study, I still think geoinformation/GIS/Spatial is the coolest stuff to work on (even after 25 years in the field). And I also still think that Oracle has the best and most elegant way of storing and querying spatial data, so Oracle's a good place to start!

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

No, you're just waiting for the next error, because that is when it gets interesting and challenging

Regards,

Stefan