What Ho! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What Ho!

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-08-02 : 06:47:07

I've created an asp.net page that takes a value, looks in my database and now I want it to return the nearest n (probably 5) locations (from a list of about 30) in order of proximity. I can write all the code - my question though is this.

I have a piece of code that works out the (approximate) distance between the location entered by the user and my locations in a table. As I said above I want to present some of these in a particular order so should I do all this in SQL or should it be in the presentation layer and why?

thanks in advance

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 06:55:04

I reckon the Trig. for that [greatest circles or whatever it is called] is quite onerous to run on the server.

We have Lat/Long of a location, and then for "everything within five miles" we calculate the min/max lat & long for 5 miles from there, and select everything with a Lat/Long BETWEEN those limits - so that a square, right?

So then we do the Trig. stuff client side to remove the ones outside a circle of radius 5 miles.

Actually I suppose you could do the Difference from the Lat&Long of where you are, and the Lat & Long of the "found" place, add the two together, and that's probably a fair, relative, measure of the nearnest of the object - order by that - would that do it?

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-08-02 : 08:14:47

Yeah, I'm doing a very basic straight line between two points approach (based on latitude and longitude) for simplicity as much as anything else - though I may look at more sophisticated methods in the future. I just wasn't sure whether to do it on the server because of the sorting. Do you know if vb.net has any sorting capability?

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-08-02 : 08:16:52

Ignore that last question I've just seen that you can sort an array

thanks Kristen

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:17:19

"I'm doing a very basic straight line between two points approach (based on latitude and longitude)"

With Trig. or not?

Assuming you have Lat/Long for each "object" then I reckon

DECLARE @WantLat float, @WantLong float
SELECT @WantLat=51.5, @WantLong=0.8

SELECT TOP 5 *
FROM MyObjects O
ORDER BY ABS(O.Lat-@WantLat) + ABS(O.Long-@WantLong=0.8)

might do the trick.

So ... if it was 8 miles due East the difference would be 8, and the distance would be 8

If the difference was 4 miles for Lat and 4 miles for Long (Say its N.E. of you), that is still a difference of 8 [by my method], but the actual distance, as the crow flies, would be SQRT(4*4 + 4*4) which is about 5.5

So to get the actual distance you'd need to do the Trig. client side and re-sort.

I was just trying to avoid doing any Trig. on the SQL box - but maybe TRIG isn't a heavy-CPU deal?

When I was at school we used Log Tables for Trig. Perhaps you could have a Tally Table of COS and SINE to save calculating it, and just JOIN instead <vbg>

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-02 : 09:39:34

This would probably be closer
(using the Pythagorean theorem formula of square(a) + square(b) = square(c) )

ORDER BY
	square(ABS(O.Lat-@WantLat)) +
 	square(ABS(O.Long-@WantLong))

However, I think you would have to adjust for the fact the distance for a unit of latitude is not constant, because it is shorter the closer you are to the North or South pole. For a particular longitude it is relatively constant over a short distance, so you could calculate a local correction factor for a longitude.

If the correction factor is @crfact, this should be fairly accurate:

ORDER BY
	square(ABS(@crfact*(O.Lat-@WantLat))) +
 	square(ABS(O.Long-@WantLong=0.8))

quote:
Originally posted by Kristen
...Assuming you have Lat/Long for each "object" then I reckon

DECLARE @WantLat float, @WantLong float
SELECT @WantLat=51.5, @WantLong=0.8

SELECT TOP 5 *
FROM MyObjects O
ORDER BY ABS(O.Lat-@WantLat) + ABS(O.Long-@WantLong=0.8)

might do the trick...

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:45:35

I'm curious to know:

If there was a large number of rows in the table is this calculation slow?

Assuming an index on Lat / Long ...

Maybe put a where clause in restricting to, say, +/- 5 miles [equivalent in fractions of a degree], check the number of rows, if not enough try again with +/- 10 miles and so on?

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-08-02 : 10:18:46

Thanks for this guys I find the input helpful.

My method was to use Pythagorus purely for simplicity. For these purposes an approximation will do as I am only trying to show the concept and as I said there are only about 30 or so (reasonably spread out) locations. I'm actually using Eastings and Northings which I beleive are the same as lat/long these days though I do remember getting confused some years ago with all those OS references and the letters. I don't need to worry about inconsistent lengths of lat/long as a) I want an approximation and b) I am only looking at a small part of the UK (sorry - perhaps should have mentioned that earlier)

This is all getting dangerously close to a discussion on Norms for the 'mathematicians' among us

thanks again

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:28:19

HeadsUp if you will have stuff both sides of the Greenwich Meridian (Esp. if you've got "E" & "W" in the data, hopefully you've got Plus and Minus instead!)

You'd not have to worry if you were in USA .... but that would be a bit like a slumbering Y2K problem, eh? Remember them? They made us all a fortune :-(

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources