Home » Server Options » Spatial » Spatial query SDO_Contains() does not work (Oracle 11g Locator)
Spatial query SDO_Contains() does not work [message #653145] |
Tue, 28 June 2016 07:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/250ff023aa92de6d37313c67264fa70a?s=64&d=mm&r=g) |
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
|
|
|
Re: Spatial query SDO_Contains() does not work [message #653198 is a reply to message #653145] |
Tue, 28 June 2016 20:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:30:32 CDT 2024
|