Home > oracle > Using SDO_WITHIN_GEOM

Using SDO_WITHIN_GEOM

August 27th, 2008 rupert

Experiment 1: Two POIs

SELECT 
  p1.poi_id, 
  p1.en_name, 
  p1.py_fullpoiadd, 
  p1.py_rdname, 
  p2.poi_id, 
  p2.en_name, 
  p2.py_fullpoiadd, 
  p2.py_rdname,
  SDO_GEOM.SDO_DISTANCE(p1.geom, p2.geom, 0.5) AS dist
FROM poi_test p1, poi_test p2
WHERE 
	Upper(p1.en_name) LIKE '%PARKSON%'
AND	Upper(p2.en_name) LIKE '%KFC%'
AND SDO_WITHIN_DISTANCE(p1.geom, p2.geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
ORDER BY dist

Experiment 2: Road + Two POIs

SELECT
  p1.poi_id, 
  p1.en_name, 
  p1.py_fullpoiadd, 
  p1.py_rdname, 
  p2.poi_id, 
  p2.en_name, 
  p2.py_fullpoiadd, 
  p2.py_rdname,
  SDO_GEOM.SDO_DISTANCE(p1.geom, p2.geom, 0.5) AS dist
FROM poi_app p1, poi_app p2, geo_entities g
WHERE 
	Upper(p1.en_name) LIKE '%BAR BLU%'
AND	Upper(p2.en_name) LIKE '%KOKOMO%'
AND     Upper(g.meta_name) LIKE '%SANLITUN%'
AND SDO_WITHIN_DISTANCE(p1.geom, p2.geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
AND SDO_WITHIN_DISTANCE(p1.geom, g.the_geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
AND SDO_WITHIN_DISTANCE(p2.geom, g.the_geom, 'DISTANCE=500 UNIT=METER' )='TRUE'

Notes:
1. Significant improvement when Sorting is removed.
2. SDO_WITHIN_DISTANCE vs NN? The first finds the nearest geometry within a given distance while NN finds the nearest geometry regardless of the distance. NN could be costly when unused properly.

Categories: oracle Tags:
Comments are closed.