CODEWITHSUNDEEP
t-sqlgeometryspatialgeography
Koen Hoebeek
Koen Hoebeek

Reputation: 13

Using STDistance with Spatial index on SQL Server 2012 is slower then using COS, SIN & ACOS Calculations and gives oval shaped results

I have a table in a SQL Server 2012 database with 3.000.000 records. Those records represent a point on a map. Al those records have x, y coordinates and geography point as fields (x, y, geo). I need to calculate all points within a distance of 10.000 meter from a certain point.

Query no. 1 I use :

DECLARE @point geography
DECLARE @rad float

SET @point = geography::STGeomFromText('POINT(51.2207099068778  4.39961050577564)', 4326);
SET @rad = 10000

SELECT count(1) 
FROM t_mailbox WITH (INDEX(SIndx_t_mailbox_geo_MHHM_512))
WHERE 
@point.STDistance(geo) <= @rad

Result : It takes 4 seconds to find 273.346 points. Drawing those points on a map results in an oval shape on the map. For sure this is wrong because not all points are included in the result.

Query no. 2 I use :

declare @radius int = 10000

DECLARE @x float = 51.2207099068778
DECLARE @y float = 4.39961050577564

SELECT count(1) 
FROM t_mailbox
WHERE 
ACOS(COS(RADIANS(90-@x))*COS(RADIANS(90-x)) +SIN(RADIANS(90-@x)) *SIN(RADIANS(90-x))*COS(RADIANS(@y-y)))*6371000 <= @radius

Result : It takes 2 seconds to find 564.547 points. Drawing those points on a map results in a perfect shaped circle.

Questions :

  1. Why is using SPATIAL INDEX and STDistance slower then the more complicated query with SIN, COS and ACOS?
  2. Why is results in a wrong oval shaped set of points?

What am I doing wrong?

Upvotes: 1

Views: 1550

Answers (2)

Jon Bellamy
Jon Bellamy

Reputation: 3373

Whilst hcaelxxam answers the "why" perfectly, you may find better performance by moving away from STDistance(). Whilst not always the case, I have generally found it better to use STIntersects() or STWithin() for distances - how you do this is pretty easy!

Try changing your query to the following. I'd be interested in the results:

DECLARE @point geography;
DECLARE @rad float = 10000;

SET @point = geography::STGeomFromText('POINT(51.2207099068778  4.39961050577564)', 4326).STBuffer(@rad); -- We're creating the "oval" here

SELECT count(1) 
FROM t_mailbox WITH (INDEX(SIndx_t_mailbox_geo_MHHM_512))
WHERE 
@point.STIntersects(geo) = 1

You may also like to try with and without the index hint. Sometimes, forcing it can generate an inefficient query plan.

Upvotes: 1

hcaelxxam
hcaelxxam

Reputation: 786

Geography data is drawn on the surface of a sphere. This means it looks different than geometry (flat) data.

Imagine taking a globe, and drawing a point on it. Then take a compass and draw a circle around that point. Now peel the skin off the globe. Notice it does not lie flat, to make it flat you have to stretch it. Now the way most people do that, is the stretch the top and bottom (north/south poles) and stretch it until it is the same length as the equator. This makes the circle you drew an oval which is bigger horizontally than vertically.

Now the formula you used is for points within a radius on flat plane. This means that you assume the distance between two lines of longitude is the same no matter what latitude you are (5 feet away from the north pole, the distance between 90 degrees and 91 degrees longitude is much smaller than at the equator).

On a mercator projection map, this formula will make a map that is a perfect circle, however on a globe, it is not. Hopefully this makes sense.

As for you speed issue: A: Apples to oranges, you are doing different calculations. and B: Without knowing how you have your index set up, it is very difficult to analyze, but geography indexing is pretty bad regardless, it works much better on very large geographies like countries.

Upvotes: 1

Related Questions