Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric
A common query used with spatial data is the nearest neighbor query. Nearest neighbor queries are used to find the closest spatial objects to a specific spatial object. For example, a store locator for a web site often must find the closest store locations to a customer location.
A nearest neighbor query can be written in various valid query formats, but for the nearest neighbor query to use a spatial index the following syntax must be used.
SELECT TOP ( number )
[ WITH TIES ]
[ * | expression ]
[, ...]
FROM spatial_table_reference, ...
[ WITH
(
[ INDEX ( index_ref ) ]
[ , SPATIAL_WINDOW_MAX_CELLS = <value>]
[ ,... ]
)
]
WHERE
column_ref.STDistance ( @spatial_ object )
{
[ IS NOT NULL ] | [ < const ] | [ > const ]
| [ <= const ] | [ >= const ] | [ <> const ] ]
}
[ AND { other_predicate } ]
}
ORDER BY column_ref.STDistance ( @spatial_ object ) [ ,...n ]
[ ; ]
In SQL Server, TOP
and ORDER BY
clauses are used to perform a nearest neighbor query on spatial data columns. The ORDER BY
clause contains a call to the STDistance()
method for the spatial column data type. The TOP
clause indicates the number of objects to return for the query.
The following requirements must be met for a nearest neighbor query to use a spatial index:
A spatial index must be present on one of the spatial columns and the STDistance()
method must use that column in the WHERE
and ORDER BY
clauses.
The TOP
clause cannot contain a PERCENT
statement.
The WHERE
clause must contain a STDistance()
method.
If there are multiple predicates in the WHERE
clause then the predicate containing STDistance()
method must be connected by an AND
conjunction to the other predicates. The STDistance()
method cannot be in an optional part of the WHERE
clause.
The first expression in the ORDER BY
clause must use the STDistance()
method.
Sort order for the first STDistance()
expression in the ORDER BY
clause must be ASC
.
All the rows for which STDistance
returns NULL
must be filtered out.
Warning
Methods that take geography or geometry data types as arguments will return NULL
if the SRIDs are not the same for the types.
It is recommended that the new spatial index tessellations be used for indexes used in nearest neighbor queries. For more information on spatial index tessellations, see Spatial Data.
The following code example shows a nearest neighbor query that can use a spatial index. The example uses the Person.Address
table in the AdventureWorks2022
sample database.
USE AdventureWorks2022
GO
DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City FROM Person.Address
WHERE SpatialLocation.STDistance(@g) IS NOT NULL
ORDER BY SpatialLocation.STDistance(@g);
Create a spatial index on the column SpatialLocation to see how a nearest neighbor query uses a spatial index. For more information on creating spatial indexes, see Create, Modify, and Drop Spatial Indexes.
The following code example shows a nearest neighbor query that cannot use a spatial index.
USE AdventureWorks2022
GO
DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City FROM Person.Address
ORDER BY SpatialLocation.STDistance(@g);
The query lacks a WHERE
clause that uses STDistance()
in a form specified in the syntax section so the query cannot use a spatial index.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register today