Oracle Spatial - Query (OBIEE, )

About

Use Oracle Spatial function in the DB right in OBIEE without map

Articles Related

• OBIEE 10G/11G - Mapviewer

Example

select a.case_id, a.case_worker_id,
sdo_geom.sdo_distance(a.geometry, b.geometry, 0.05) as dist
from cases a, case_workers b
where a.case_worker_id = b.case_worker_id

Find all competitors within 2 miles

SELECT c.holding_company, c.location
FROM competitor c,
bank b
WHERE b.site_id = 1604
AND
SDO_WITHIN_DISTANCE(c.location,b.location, 'distance=2 unit=mile')= 'TRUE'

Update an employee table with the municipality data when their longitude and latitude are contains in the polygon geometry of the municipality

UPDATE employees h SET (municipality_code, municipality_naam) = 
(SELECT 
  m.code,
  m.naam
FROM municipality m
WHERE sdo_geom.relate(g.geometry, 'CONTAINS', h.latitude_longitude , 0.005) = 'CONTAINS')
WHERE h.latitude_longitude IS NOT NULL;

Documentation / Reference

Complex Spatial Queries: Examples :

• Finding All Cities Within a Distance of a Highway
• Finding All Highways Within a Distance of a City
• Finding the Cities Nearest to a Highway
• Finding the Cities Above a Specified Population Nearest to a Highway
• Performing Aggregate Union of All Counties in Texas