Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
The Spatial filtering of geometries: The effect of tolerances on relationships
The knowledgeable Bryan Hall made a post over on the Oracle Technology Network’s Oracle Spatial Forum reminding users of the “hidden” issues that inform the way Oracle compare’s two geometries when using a secondary filter
Here is the relevant section of the post.
With relationship operators such as sdo_anyinteract that take two parameters for the spatial columns to use for the test. The first must be indexed (a real table), and the second can be indexed, or just a passed or created spatial object. In my cases, both are always indexed, and most spatial items have a very tight tolerance. But for one object, instead of the 0.001 meter tolerance, it had a 0.1 meter tolerance. I didn’t realize that – until I ran the query both ways (using the ORDERED hint) to see which would be quicker for the script – and then came back with a different number of results.
The difference was the order of use in sdo_anyinteract. If the first item listed has a wider tolerance, more results are returned as it is more inclusive. However, if the order is reversed, the more restrictive tolerance is used and you get fewer results.
Let’s look at this in detail.
Some data
-- Rect1 DROP TABLE rect1; CREATE TABLE rect1 ( id INTEGER, geom mdsys.sdo_geometry); INSERT INTO rect1 (ID,GEOM) VALUES (1,mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(1,1,10,10))); COMMIT; DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'RECT1' AND column_name = 'GEOM'; COMMIT; INSERT INTO user_sdo_geom_metadata (TABLE_NAME,column_name,srid,diminfo) VALUES ('RECT1','GEOM',NULL,mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',0,11,0.05),mdsys.sdo_dim_element('Y',0,11,0.05))); COMMIT; CREATE INDEX rect1_geom_spdx ON rect1(geom) INDEXTYPE IS mdsys.spatial_index PARAMETERS('sdo_indx_dims=2, layer_gtype=POLYGON'); -- Rect2 DROP TABLE rect2; CREATE TABLE rect2 ( id INTEGER, geom mdsys.sdo_geometry); INSERT INTO rect2 (ID,GEOM) VALUES (1,mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(10.01,10.01,20,20))); COMMIT; DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'RECT2' AND column_name = 'GEOM'; COMMIT; INSERT INTO user_sdo_geom_metadata (TABLE_NAME,column_name,srid,diminfo) VALUES ('RECT2','GEOM',NULL,mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',10,21,0.0005),mdsys.sdo_dim_element('Y',10,21,0.0005))); COMMIT; CREATE INDEX rect2 ON rect2(geom) INDEXTYPE IS mdsys.spatial_index PARAMETERS('sdo_indx_dims=2, layer_gtype=POLYGON');
Note that a tolerance of 0.05 is the same as 0.1 and 0.0005 the same as 0.001.
Relationship Operators/Functions and Parameters
Firstly, let’s use a relationship function that takes a tolerance input to show the differences when the tables have two different tolerances.
SELECT SDO_GEOM.RELATE(a.geom,'DETERMINE',b.geom,0.5 /* rect1 tolerance */) AS u1relate, SDO_GEOM.RELATE(a.geom,'DETERMINE',b.geom,0.005/* rect2 tolerance */) AS u2relate FROM rect1 a, rect2 b, user_sdo_geom_metadata u1, user_sdo_geom_metadata u2 WHERE ( u1.TABLE_NAME = 'RECT1' AND u1.column_name = 'GEOM' ) AND ( u2.TABLE_NAME = 'RECT1' AND u2.column_name = 'GEOM' );
Notice that at the coarser RECT1 tolerance of 0.5 the objects touch – which is correct.
But at the finer RECT2 tolerance of 0.005 the objects are disjoint – which is also correct.
So, now what happens if we search as per Bryan’s posting?
Firstly, searching for the RECT2’s geometry which has the finer tolerance:
SELECT /*+ORDERED*/ a.id, b.id FROM rect1 a, rect2 b WHERE SDO_RELATE(b.geom,a.geom,'mask=anyinteract') = 'TRUE'; -- Results -- NOTHING ie DISJOINT
And the reverse, searching for RECT1’s geometry which has the coarser tolerance:
SELECT /*+ORDERED*/ a.id, b.id FROM rect2 b, rect1 a WHERE SDO_RELATE(a.geom,b.geom,'mask=anyinteract') = 'TRUE'; -- Result ID ID -- -- 1 1
That is, they have an interaction (ie TOUCH).
Assuming that these Oracle secondary filter functions use SDO_GEOM.RELATE to compare two geometries then we seem to have duplicated Bryan’s problem.
Now, let’s repeat this test using one of the “wrapper” secondary filters.
SELECT /*+ORDERED*/ a.id, b.id FROM rect1 a, rect2 b WHERE SDO_ANYINTERACT(b.geom,a.geom) = 'TRUE'; -- Results -- NOTHING ie DISJOINT
And the reverse:
SELECT /*+ORDERED*/ a.id, b.id FROM rect2 b, rect1 a WHERE SDO_TOUCH(a.geom,b.geom) = 'TRUE'; -- Result ID ID -- -- 1 1
Which is as expected.
Trouble at Mill
However, SDO_GEOM.RELATE does not perform as expected when on uses the version of
the function with two diminfo parameters:
SELECT SDO_GEOM.RELATE(a.geom,u1.diminfo,'DETERMINE',b.geom,u1.diminfo) AS u11relate, SDO_GEOM.RELATE(a.geom,u1.diminfo,'DETERMINE',b.geom,u2.diminfo) AS u12relate, SDO_GEOM.RELATE(a.geom,u2.diminfo,'DETERMINE',b.geom,u1.diminfo) AS u21relate, SDO_GEOM.RELATE(a.geom,u2.diminfo,'DETERMINE',b.geom,u2.diminfo) AS u22relate FROM rect1 a, rect2 b, user_sdo_geom_metadata u1, user_sdo_geom_metadata u2 WHERE ( u1.TABLE_NAME = 'RECT1' AND u1.column_name = 'GEOM' ) AND ( u2.TABLE_NAME = 'RECT2' AND u2.column_name = 'GEOM' ); -- Results U11RELATE U12RELATE U21RELATE U22RELATE --------- --------- --------- --------- TOUCH TOUCH TOUCH TOUCH
Caveat Emptor! (Latin for “Let the buyer beware”)
Hope this helps someone.
Documentation
- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions