Spatial SQL -> MS-SQL. Overview of nearest neighbour search queries


-- task: find address point closest to the given coordinates

DECLARE @dist FLOAT = 200,   -- meters
@p GEOGRAPHY = GEOGRAPHY::STGeomFromText(
   'POINT(' + CONVERT(Varchar(30),45.46419026, 2)
   + ' ' + CONVERT(Varchar(30),9.19167034, 2) + ')',4326);

-- [query 1] order by Distance (A)
SELECT TOP(1) ID,[Street],[HouseNum]
FROM [dbo].points_table WITH (INDEX([geom_sidx]))
WHERE geog.STDistance(@p) < @dist
ORDER BY geog.STDistance(@p);

-- [query 2] order by Distance (B)
SELECT TOP(1) ID,[Street],[HouseNum]
FROM [dbo].points_table WITH (INDEX([geom_sidx]))
WHERE geog.STIntersects(@p.STBuffer(@dist))=1
ORDER BY geog.STDistance(@p);

-- [query 3] CTE style (uses numbers table)
WITH points_table AS
(
  SELECT TOP(1) WITH TIES *, a.geog.STDistance(@p) AS dist
  FROM [demo].dbo.num_seq AS nums
   INNER JOIN dbo.points_table AS a WITH(INDEX([geom_sidx]))
  ON a.geog.STDistance(@p) < (@dist * POWER(2, nums.n))
  ORDER BY nums.n
)
SELECT TOP(1) ID,[Street],[HouseNum] FROM points_table
ORDER BY n, dist;


Create tables script:
-- points table structure
CREATE TABLE [dbo].[points_table]
(
  [ID] [int] NOT NULL,
  [City] [varchar](60) NULL,
  [Street] [varchar](80) NULL,
  [HouseNum] INT NULL,
  [zipcode] [varchar](12) NULL,
  [geog] [geography] NULL,
  PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- create Spatial Index
CREATE SPATIAL INDEX [geom_sidx] ON [dbo].[points_table]
([geog]) USING GEOGRAPHY_GRID
WITH
(GRIDS = (LEVEL_1 = MEDIUM,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16,
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

-- -------- create sequential numbers table ----------

IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type in (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;v
CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);



sqlexamples.info