CODEWITHSUNDEEP
sqlsql-serverstored-proceduressqlgeography
Toby Holland
Toby Holland

Reputation: 1069

Sql Server - How do I query a geography column based on Lat/Long min max

I have a table with a geography column that stores the location of a property.

I have a procedure something like this - 

PROCEDURE dbo.spt_sold_property_search
(
@latitude_min   Decimal(9,6),
@latitude_max   Decimal(9,6),
@longitude_max  Decimal(9,6),
@longitude_min  Decimal(9,6)
)

AS BEGIN    

SET NOCOUNT ON

SELECT * FROM [dbo].[sold_property] AS p WITH(NOLOCK)
WHERE p.location ***is in the lat/long min max bounds***

END

What would I need in the where clause to check if the geography point is in the bounds of the Lat/Long min max? It is an large dataset so performance is a critical.

Should I be creating a Geography SQL type in code from the bounds and pass that into the proc as a procedure?

I was also considering creating 2 calculated int columns (lat/long) which would be created on insert, and then simple < > as I heard that is faster than Geography queries.

Upvotes: 4

Views: 3905

Answers (1)

cha
cha

Reputation: 10411

If you only require the latitude and the longitude to be within the max/min values then use the Lat and Long properties:

SELECT * FROM [dbo].[sold_property] AS p WITH(NOLOCK)
WHERE p.location.Lat BETWEEN @latitude_min and @latitude_max
  AND p.location.Long BETWEEN @longitude_min and @longitude_max

However, in my opinion it will be correctly to construct a polygon from the provided coordinates and then use the STWithin method to check if the point in the table is within the polygon, like this:

DECLARE @g geography;
SET @g = geography::Parse('POLYGON (('+CONVERT(NVARCHAR(20),@latitude_min)+', '+
CONVERT(NVARCHAR(20),@latitude_max)+', '+CONVERT(NVARCHAR(20),@longitude_min)+', '+
CONVERT(NVARCHAR(20),@longitude_max)+'))');

SELECT * FROM [dbo].[sold_property] AS p WITH(NOLOCK)
WHERE @g.STWithin(p.location)

Please note that the latter query maybe not sargable. As Ben Thul mentioned below the spatial indexes may support the STWithin

Upvotes: 1

Related Questions