Start

This is a log of the AE project. I actually started thinking about, and working on this a bit, over four months ago. I just recently came off a big project at work and had some time to do some actual work on it… so, I went ahead and set the development web site up and started some practical R&D.

Over the past four months I’ve primarily been working on some vaguer R&D. Looking at GPS devices and protocols, and the various platforms they run on. Also I figured out the math behind GPS (which I just implemented in practice for the first time last night).

So far the only thing that’s been done is the basic GPS distance calculation (which still has one error), the overview document, the basic web site, and a lot of thought. :P

Using something called the Great Circle calculation, I was able create a VERY funky stored procedure that, if you pass it a x/y coordinate and a distance, in feet, will pass back all of the stored GPS locations within that distance. I’m VERY happy I finally figured it out. The formula itself isn’t that bad, but implementing it in software against a large number of locations is somewhat complex.

I’m actually not very happy with the long term raw performance, but it will do for now (sub-2 second for 50,000 records in the DB, and 11 seconds for around 400,000). The stored procedure looks like:

CREATE Procedure findLocByDistance
(
@c1a float,
@c1b float,
@feet int
)
As

select * from Locationwhere
degrees(acos(
(sin(RADIANS(@c1a)) * sin(radians(Location.Latitude))) + ( cos(radians(@c1a)) * cos(radians(Location.Latitude)) * cos(radians(@c1b -Location.Longitude)))
))
* 111
* 0.621371 * 5280 <= @feet

Yeah, I know, it LOOKS simple… but it was hellish to get it working. I first worked out how to pass in two sets of x/y coordinates, and the stored proc would pass back the distance between the two. After getting that working, it was fairly simple to convert it into a select. I must admit this is the first time I’ve done trig in a stored proc  it’s also been a LONG time since I’ve done any trig at all, so this took a while to work out.

The problem with performance is it HAS to be a table scan. As it’s performing a calculation, it has to load each and every record to do the calc on. I’ve come up with some ways to make this a bit better, and the live system may REQUIRE additional constants on it (categories, state, zip code, et al.), so this SHOULD work for now. That time was also on a small box. If this ever gets to the million-record range (which takes around 19 seconds to return on this stored proc, with 5 rows) then I’ll need to invest in some bigger iron.