Select to view content in your preferred language

What ST_Geometry function replaces SDO_NN

02-19-2015 12:49 PM
New Contributor II

Does anyone know what the equivalent in ST_Geometry functions would be for SDO_NN using Oracle 11g?

I need to select the 10 closes linear assets to another linear shape.

1 Reply
Occasional Contributor III

I'm not sure there is one. What I've used in the past on SQL Server is to:

Select all within a distance threshold

Order them by distance

select rownum = 1

I'm not an expert in Oracle, but I'm in the process of migrating in to it. I'm more familiar w/ SQL Server so I don't have as much experience writing fancy queries and I still haven't learned all the functions, but something like:

select q.*, rownum from(
  select t1.objectid oid1, t2.objectid oid2, sde.ST_Distance(t1.shape, t2.shape)
  from table t1, table t2
  where t1.objectid <> t2.objectid
  order by sde.ST_Distance(t1.shape, t2.shape)
) q 
where rownum =1

A lot of spatial queries seem MUCH slower in Oracle compared to SQL Server, but I have a lot to learn.

0 Kudos