One type of frequently executed spatial operation is to identify the nearest feature or nearest features with respect to an existing feature or specific location. For example, find the nearest gas station based upon my current location.
Presently, the st_geometry implementation for Oracle does not provide a nearest operator to provide this information.
Instructions provided in this knowledge base article describe how a SQL statement can be written to identify the nearest feature or features with respect to an existing feature or specific location.
If the objective is to find the nearest restaurant to a specific address, the question can be answered by writing a SQL statement with the st_buffer, st_intersects operator, and Oracle rownum function.
SQL> SELECT name
3 (SELECT a.name NAME
4 FROM restaurants a, address_pnt b
5 WHERE b.id = 9482
6 AND sde.st_intersects(a.shape, sde.st_buffer(b.shape, 1000)) = 1
7 ORDER BY sde.st_distance(a.shape,b.shape))
8 WHERE ROWNUM = 1;
The query uses an in-line view to first return all restaurants that intersect the buffered address_pnt and orders all the intersecting restaurants by the distance from the address_pnt. The outer query then applies the rownum = 1 filter to the result set to return just the first row in the result set (which will be the nearest restaurant).
If the objective is to return n number of nearest features, increase the value for rownum to the number of results required.
The only variable in this approach of using st_buffer and st_intersects is determining the best distance to buffer the input geometry. A value to small may result in no intersecting rows, and a value to large may result in too many intersecting rows, which adds time for the query to execute.
Understanding the data distribution and the average distance between features is the best method for determining what value should be specified for the sde.st_buffer step.