The SE_Nearest() and SE_NearestBbox() functions

The SE_Nearest() function returns the nearest-neighbors to a specified geometry. The SE_NearestBBox() function returns the nearest-neighbors that are based on the distance between bounding boxes.

The SE_Nearest() function uses an R-tree index, which you must create if it does not exist. Query results are returned in order of increasing distance from the query object. The distance is measured by applying the same algorithm as used by the ST_Distance() function. For geometry values that are in a geographic coordinate system, the distance is calculated by applying the linear unit of measure of meters.

The SE_NearestBBox() function runs more quickly than the SE_Nearest() function, but might return objects in a different order, depending on the shape of the objects.

These functions can be used only in the WHERE clause of a query.

Required: You must create an R-tree index on the geometry column on which you want to perform nearest-neighbor queries.

Syntax

SE_Nearest (g1 ST_Geometry, g2 ST_Geometry) 

SE_NearestBbox (g1 ST_Geometry, g2 ST_Geometry)

Return type

BOOLEAN

Example

The cities table contains the names and locations of world cities:

CREATE TABLE cities (name varchar(255),
                     locn ST_Point);

Populate this table with data from a DB-Access load file, cities.load (this data file contains the names and locations of approximately 300 world cities), as the example shows:

LOAD FROM cities.load INSERT INTO cities;

Create an R-tree index on the locn column:

CREATE INDEX cities_idx ON cities (locn ST_Geometry_ops) USING RTREE;

UPDATE STATISTICS FOR TABLE cities (locn);

Now search for the five cities nearest London:

SELECT FIRST 5 name FROM cities
  WHERE SE_Nearest(locn, '0 point(0 51)');


name  London

name  Birmingham

name  Paris

name  Nantes

name  Amsterdam

Warning: Using a fragmented R-tree index for nearest-neighbor queries raises an error. Results are not returned in nearest distance order because the query is run on each separate index fragment, and results from each fragment are combined in an unspecified order.