OraFAQ Forum: Spatial » Spatial query SDO

Home » Server Options » Spatial » Spatial query SDO_Contains() does not work (Oracle 11g Locator)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Spatial query SDO_Contains() does not work [message #653145]

Tue, 28 June 2016 07:38

moehre
Messages: 43
Registered: June 2016

Member

Hi,

I will calculate which polygons inside a certain boundig box.
So because of this I wanna use the SDO_Contains Operator.
You can see the example below:

SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ
 
FROM building a,  surface_geometry c, Objectclass d
 
WHERE SDO_CONTAINS (c.GEOMETRY, SDO_GEOMETRY(2003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(3476109.091, 5372296.238, 3528350.521, 5419788.555))) = 'TRUE';

I get an empty result!

Inside the SDO_Geometry type I have two points (lower left, upper right)!
Furthermore I use 31467 for SRID in c.GEOMETRY and the bounding box.

The geometry of the table surface_geometry looks like:
MDSYS.SDO_GEOMETRY(3003,31467,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(3481797.954,5384186.137,625.799,34817 98.012,5384185.994,625.799,3481798.012,5384185.994,639.956,3481797.954,5384186.137,639.956,3481797.954,5384186.137,625.799))

The problem is that my result is empty. But I am very sure that the c.Geometry is inside the bounding box.
When I use FALSE instead of TRUE this error occurs: 29902. 00000 - "error in executing ODCIIndexStart() routine"

Is my SDO_GEOMETRY definition wrong?

Hope somebody can help me

Report message to a moderator

Re: Spatial query SDO_Contains() does not work [message #653198 is a reply to message #653145]

Tue, 28 June 2016 20:48

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I believe you should be using SDO_INSIDE instead of SDO_CONTAINS. Please see the demonstration below, especially the final query.

-- test environment:

SCOTT@orcl_12.1.0.2.0> CREATE TABLE surface_geometry
  2    (id	  NUMBER,
  3  	geometry  SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO surface_geometry (id, geometry) VALUES
  2  	(1,
  3  	 MDSYS.SDO_GEOMETRY
  4  	   (3003,31467,NULL,
  5  	    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  6  	    MDSYS.SDO_ORDINATE_ARRAY
  7  	      (3481797.954,5384186.137,625.799,
  8  	       3481798.012,5384185.994,625.799,
  9  	       3481798.012,5384185.994,639.956,
 10  	       3481797.954,5384186.137,639.956,
 11  	       3481797.954,5384186.137,625.799)))
 12  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO USER_SDO_GEOM_METADATA (table_name, column_name,diminfo,srid) VALUES
  2    ('SURFACE_GEOMETRY', 'GEOMETRY',
  3  	(SELECT MDSYS.SDO_DIM_ARRAY
  4  		  (MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05),
  5  		   MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05),
  6  		   MDSYS.SDO_DIM_ELEMENT('Z', minz, maxz, 0.05))
  7  	 FROM	(SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
  8  			ROUND( MAX( v.x ) + 1,0) as maxx,
  9  			TRUNC( MIN( v.y ) - 1,0) as miny,
 10  			ROUND( MAX( v.y ) + 1,0) as maxy,
 11  			ROUND( MIN( v.z ) - 1,0) as minz,
 12  			ROUND( MAX( v.z ) + 1,0) as maxz
 13  		 FROM	(SELECT SDO_AGGR_MBR(a.geometry) as mbr
 14  			 FROM	surface_geometry a) b,
 15  			TABLE(mdsys.sdo_util.getvertices(b.mbr)) v)),
 16  	31467)
 17  /

1 row created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX surface_geometry_idx ON surface_geometry (geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX
  2  /

Index created.

-- query using sdo_contains:

SCOTT@orcl_12.1.0.2.0> SELECT c.Geometry AS geometry
  2  FROM   surface_geometry c
  3  WHERE  SDO_CONTAINS
  4  	      (c.GEOMETRY,
  5  	       SDO_GEOMETRY
  6  		 (2003, 31467, NULL,
  7  		  SDO_ELEM_INFO_ARRAY(1, 1003, 3),
  8  		  SDO_ORDINATE_ARRAY(3476109.091, 5372296.238, 3528350.521, 5419788.555))) = 'TRUE'
  9  /

no rows selected

-- query using sdo_inside:

SCOTT@orcl_12.1.0.2.0> SELECT c.Geometry AS geometry
  2  FROM   surface_geometry c
  3  WHERE  SDO_INSIDE
  4  	      (c.GEOMETRY,
  5  	       SDO_GEOMETRY
  6  		 (2003, 31467, NULL,
  7  		  SDO_ELEM_INFO_ARRAY(1, 1003, 3),
  8  		  SDO_ORDINATE_ARRAY(3476109.091, 5372296.238, 3528350.521, 5419788.555))) = 'TRUE'
  9  /

GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481797.95, 5384186.14, 625.799, 3481798.01, 5384185.99, 625.799, 3481798.01
, 5384185.99, 639.956, 3481797.95, 5384186.14, 639.956, 3481797.95, 5384186.14,
625.799))


1 row selected.

Report message to a moderator

Re: Spatial query SDO_Contains() does not work [message #653373 is a reply to message #653198]

Mon, 04 July 2016 04:44

moehre
Messages: 43
Registered: June 2016

Member

It is working know thanks Smile

!

With this statement "SDO_INSIDE" it is working...
But why is this not with SDO_Contains possible? What is the different between this two operations.

Do you know that?

Report message to a moderator

Re: Spatial query SDO_Contains() does not work [message #653407 is a reply to message #653373]

Mon, 04 July 2016 15:28

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The relationship definitions are better documented in the sdo_geom.relate function. Please see the following excerpts from the online documentation. It is a matter of which parameter is inside the other. They are opposites.

http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_objgeom.htm#SPATL1107

Quote:

CONTAINS: Returns CONTAINS if the second object is entirely within the first object and the object boundaries do not touch; otherwise, returns FALSE.

Quote:

INSIDE: Returns INSIDE if the first object is entirely within the second object and the object boundaries do not touch; otherwise, returns FALSE.

Report message to a moderator

Previous Topic:	UPDATE-SET Command does not work
Next Topic:	Convert SDO_Geometry object in GeoJSON

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 02:11:31 CST 2024