4

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.

Toby Holland
  • 1,029
  • 2
  • 14
  • 29
  • why don't you try those 2 approaches? – Vamsi Prabhala Jul 23 '15 at 01:34
  • "What would I need in the where clause to check if the geography point is in the bounds of the Lat/Long min max?" - that is my question, I don't know what the where statement would be to try it... – Toby Holland Jul 23 '15 at 01:42
  • got it..what does data in `location` column look like? – Vamsi Prabhala Jul 23 '15 at 01:44
  • It is the SQL-server Geography data type. – Toby Holland Jul 23 '15 at 01:52
  • I have no experience with `geography`, but a quick look online suggests that you need to convert your co-ordinates into a `polygon` `geography` then use `.STWithin` for the test. See https://msdn.microsoft.com/en-us/library/ff929207.aspx and also http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx – Turophile Jul 23 '15 at 05:06

1 Answers1

1

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

cha
  • 10,301
  • 1
  • 18
  • 26
  • 2
    In re: SARGability, spatial indexes support `STWithin`. From the docs: "Spatial indexes support the following set-oriented geometry methods under certain conditions: STContains(), STDistance(), STEquals(), STIntersects(), STOverlaps(), STTouches(), and STWithin()". – Ben Thul Jul 23 '15 at 16:35