One idea on that table scan – there’s a coord system that can be converted to some kind of grid system (forgot the name, saw it while reading about GPS). Basically, each loc could have a Grid ID. Then, we could do a initial select on that grid (which I think can be figured out much easier than the distance thing), THEN do the distance thing inside of that grid... something or other like that.
I’ve gotten the initial DB stored proc stuff written (which was a pain. I hate ASP/VBScript. Can’t wait to go to ASP.NET. I’ve got all that stuff in VB components, but had to convert to script as I won’t be able to put COM stuff up on the initial site).
I almost went to .NET over the weekend. It appears to be a hugely superior environment over ASP/VBScript. I’d do it… but if I do, then I’m going to limit what I can put up on my domain. I *THINK* CIHost is going to allow for .Net stuff, but they won’t allow DLL’s I’m sure. I still might do it, as the basic web mechanics seem to be a lot better… have to wait and see.
Anyway, I have an ASP page that can add a loc, and another that can find locs based on feet from a passed loc. I’ve got wrapper functions that any page can call to do this (so they’re functions that wrap around the Stored Proc Stuff). Those wrappers will contain all the stuff I consider to be middle tier. If this thing takes off, all of that will move to COM (Or some .NET thing).
4/07/02
Well, buying a house impacted the amount of time I had to work on AE the last couple of weeks
Sat down tonight and just started working through the basic web site. Finished up the tables (and connections between them), wrote a few stored procs, added the DB of ZIP codes and Country Names, and an ASP Add Location page that actually LOOKS nice, and used the two DB’s (via stored procs).
I’ve broken the site up into three conceptual parts, Web, Middle Tier, and Stored Procs. They allow for some separation, which will let me change how things work later.
The Web part is straight ASP, and knows nothing about the DB. It makes calls to the Middle Tier right now. The calls could easily be converted to COM or whatever; right now they are just server side VBScript.
The Middle Tier is also just VBScript that wraps around the data. The actual access to the data is exclusively via stored procedures.
I’m just going to work through the basic web site, get it up and running. I’ll then work on the XML and a test MFC (.NET?) application that used the site as a remote application. Then it’s off to GPS land… :P
Sunday, March 31, 2002
Friday, March 15, 2002
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.
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.
Subscribe to:
Posts (Atom)
