analytics/informix/extend/spatial.8.22.FC2/examples/sql/se_nearest.sql

-- This example demonstrates how to use SE_Nearest to perform
-- a nearest-neighbor query.
-- IMPORTANT:
--   1. This function is only available with the 9.3 version of
--      Informix Dynamic Server.
--   2. You must create an R-Tree index to do nearest-neighbor 
--      queries.

-- Step 1. Create the cities table which will contains the names 
-- and locations of world cities

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

-- Step 2. Populate this table with data from a DB-Access load file.
-- This data file is included with the Spatial DataBlade Module as 
-- part of this example.  It contains the names and locations of
-- approximately 300 world cities.

LOAD FROM cities.load INSERT INTO cities;


-- Step 3. 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);


-- Step 4. Try it!  Find the 5 cities nearest London:

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