Search code examples
sqlsql-serverspatialgeography

SQL Server Geography

Is there any possible way to improve the below query:

DECLARE @radiusInMeters FLOAT = 400;
DECLARE @dgeog geography = geography::Point(given_latitude, given_longitude, 4326).STBuffer(@radiusInMeters);

select [fdx].latitude, [fdx].longitude
from [dbo].[fdx]
where @dgeog.STIntersects(geography::STGeomFromText('POINT(' + convert(varchar(20), [fdx].longitude) + ' ' + convert(varchar(20), [fdx].latitude) + ')', 4326)
                         ) = 1
Solution

  • kcung and Hasan BINBOGA are correct, you need a spatial index.

    Look at your query: @dgeog.STIntersects(xxxx) = 1 This requires [xxxx] to be a geography data type. In order for [xxxx] to be a geography data type, the STGeomFromText function must be applied to the row. And because this is the only part of your WHERE clause, the function must be applied to all rows.

    If the table fdx is particularly large, this means that the CLR function will have to be applied over and over again. This is not (in SQL-Server terms) a fast process.

    Try this, if you can:

    ALTER dbo.fdx ADD Point AS (GEOGRAPHY::Point(Latitude, Longitude, 4326)) PERSISTED
GO
CREATE SPATIAL INDEX SIndex_FDX ON dbo.fdx (Point) 
USING GEOGRAPHY_GRID
WITH (
  GRIDS = (LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
  CELLS_PER_OBJECT = 1
)
GO
DECLARE @Latitude DECIMAL(15,10) = 0
DECLARE @Longitude DECIMAL(15,10) = 0
DECLARE @Radius FLOAT = 400
DECLARE @g GEOGRAPHY = GEOGRAPHY::Point(@Latitude, @Longitude, 4326).STBuffer(@Radius)
SELECT * FROM dbo.fdx WHERE Point.STIntersects(@g) = 1

    A note: You should convert your lat/long pairs into decimals before using them to compute the geography column. There is an implicit conversion from float to decimal to string when you use a float as an input that will trim your coordinates down to 4 decimal places. If you explicitly convert first, that will not be an issue.

    Also, if you have any null lat/long values in dbo.fdx, you need to filter them in the WHERE clause as a null value will cause your spatial index not to work properly.