THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

SQL Spatial: Getting “nearest” calculations working properly

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/

Published Thursday, August 14, 2014 10:16 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Milan Stojic, SQL Engineer said:

Hi Rob, we tried to make it as intuitive as possible, without trying to explain all constraints and requirements in order to have correct behavior in all cases.

NULL values in SQL Server are on top with ORDER BY clause and that would ruin your TOP k nearest neighbor results but more importantly would kill performance benefit of using NN query plan. As you mentioned NULLs can be produced by STDistance if the two objects have different SRID. Unfortunately SQL Server does not support ORDER BY NULLS LAST and we don't keep index on SRID value to know if you are using multiple SRIDs. We would need a table scan to figure it out. That's why the easiest solution is to require proper query semantic to make sure that NULL values for STDistance are filtered out.

I'd like to hear if there are any suggestions on how to make docs more intuitive.

August 20, 2014 3:11 AM
 

Rob Farley said:

Hi Milan! Thanks for responding. We should talk about PDW more too...

Anyway - I think the docs aren't bad, but the keys are the bits around points 3 and 7, plus the fact that the example doesn't actually use the indexes on my machine!

So I wouldn't mind an explicit explanation in the page saying "The query must include an explicit predicate to eliminate the possibility of NULL results which could occur in certain situations even if the columns disallow NULL values. For further information, see the pages regarding STDistance(), etc"

This kind of statement is going to mean that I don't just dismiss the requirement as needless in my situation.

As for the example not working, a query hint of OPTION (MAXDOP 1) could help.

Of course, as a fan of execution plans, it would be nice to show what people should be looking for as evidence that the index is being used effectively...

...or maybe just provide a link to this post to help people. ;)

August 20, 2014 7:48 AM
 

chris said:

hey rob - great post. any suggestions on where to get a list of lat/long for intersections in NYC?

December 8, 2015 1:23 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement