Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SEFL
Starting Member
8 Posts |
 Posted - 2011-11-25 : 16:48:56
|
Hi guys,I'm trying to write a function that will gather all of the companies within a certain distance in KM (which may vary) of a geographic point (which may also vary). I've never actually worked with the Geography data type before, so I'm sure I'm missing something either really stupid or way over my head.The problem is that the query works, but takes about 13 seconds to check through about 4000 records. This particular function is called via another recursive function and would make approximately 25 calls total, so this is too long. I have read that this query is computationally intensive, but I'm actually running it on a dedicated server with only the SQL server running (for all practical intents and purposes) right now. Here's the function I've written:ALTER FUNCTION [dbo].[FindCompanies]( @Coords geography, @Distance integer = 20)RETURNS @Company_Table TABLE (Company_ID integer, Distance Float)ASBEGINset @Distance = @Distance * 1000insert into @Company_Table Select Company_ID, @Coords.STDistance(Companies.Coords) from Companies with (Index (Coordinates)) where @Coords.STDistance(Companies.Coords) <= @Distance order by @Coords.STDistance(Companies.Coords)RETURN END As you can see, I'm trying to use the Coordinates index, which is a spatial geography index on Coords I created to try and speed the query up, having read that I could do that. This doesn't seem to make a difference.Now, I do have the latitudes and longitudes for these companies, which is how I created the Coords field in the first place. So if need be and if it will run more efficiently, I could write another function to calculate the distance. But that seems to defeat the purpose of the geography data type to me.So my question is...what am I missing, and what is a more efficient way to get to my goal? Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-26 : 06:20:58
|
quote:
Originally posted by SEFL
Now, I do have the latitudes and longitudes for these companies, which is how I created the Coords field in the first place. So if need be and if it will run more efficiently, I could write another function to calculate the distance.
Not used the new Geography datatype.We calculate a square, using only Pythagoras, and then find all Lat/Long within that square (we index the Lat + Long columns, so that is very efficient),then we use the full trigonometric functions to do the curvature-of-the-earth formula (i..e on only those within the "square" from the first query) to get only those within the circle.Example: See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85363#314900P.S. you might want to edit your code, above, to put a line break in to reduce the width of this thread  |
 |
|
|
|
|
|
|
|
Speeding up function using STDistance