Inserting records... (1 Viewer)

[graffy_76]

Ok, I know this is already part of another thread, but my problem is a bit more complex...

The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.

I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.

Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.

That's my solution, but there may be something simpler. Any thoughts?

 

[FoFa]

Don't know much about, so maybe off base, but have you investigated using GPS codes?

 

[The_Doc_Man]

Concur with FoFa, but getting the GPS codes might be harder to find and if the street curves around, you could be screwed.

Another similar approach: Have you considered getting a really BIG city streets map and including the grid coordinates from that map? Then instead of sorting by street names, sort by grid coordinates. But if you have two lights in the same grid-box, you might have duplicates. Or sort by street name of the street in question, but THEN sort the intersections by grid.

OR ... your streets surely have numbers. Like, in Metairie, the intersection of Clearview and Veterans is the 2900 block of Clearview and the 4600 block of Veterans. So if you were listing all the traffic lights on Clearview, put the street number that would apply if the lot on some specific (consistently-chosen) corner were to be given a postal address. If the two streets in question are divided boulevards such that they have multiple equipment items at the same intersection, establish a convention on "number in order of distance from selected corner" - and just be consistent about that.

If you do THAT, you not only have unique numbers, but the record tells your repair crews exactly where to go ... like, go to the 2800 block of Hickory Street. That has got to be pretty much unequivocal.

Even in downtown Birmingham AL, this would work and EVERYTHING numbers starting from 1st Avenue and 1st Street. Even the DIRECTION of numbering changes depending on whether you are on 3rd St. E or 2nd St. W, 4th Ave. N or 9th Ave S; yet this method would work even in THAT messy street grid.

It would even work in uptown New Orleans where some of the rivers follow the curve of the river through at least four or five direction changes.

 

[mresann]

As an aside...

GoogleEarth, the satellite mapping application, has a feature that shows the GPS location in the status bar of the current mouseover position on the map. If you want to get GPS coordinates of ANY place in the metropolitan areas of the US or Europe, just look it up on the map, put the mouse over the location, and copy the GPS in the status bar.

It's scary how good the FREE version is. I was able to see my house, the back yard, and the two trees in the yard. I understand the pay version has even more resolution.

 

[graffy_76]

Some interesting considerations, but with 480 intersections already databased, it's a lot of work even to go to GoogleEarth. Cool tool, though. I had no idea it was there.

For the sake of simplicity in data entry, I'd rather write a database that had a self-maintaining index, rather than having to look information up...

 

[ScottGem]

I would still be inclined to go with longitude/latitude. I think the only issue would be if a street curved back on itself. But streets that curve that much are generally residential without a lot of street lights. It may be a lot of work to get the values initially, but it will make it a lot easier in the future to sort, plot out and produce repair assignments.

 

[Pat Hartman]

Since the intersection number would not be the primary key for the row, there is no problem renumbering if necessary. Just remember to do it in reverse (from the highest number to the lowest) so as to avoid duplicates as you are renumbering.

 

[graffy_76]

Ya know, I booted up Mickeysoft's Streets & Trips and noticed that it does provide lat's & long's. They're huge numbers, but with both of them in the system, it ought to work fine...

Since I'm sorting the intersections first by their county and second by their corridor (which is user-defined), then when a corridor doubles back on itself, I can simply seperate that into two corridors - one eastbound, one westbound, or whatever - which is in keeping with how we do things, anyway.

Since I'm not an uberhacker or anything, I imagine it'd take me as long to solve this programmetrically as it would to just look up the lat's & long's.

Thanks for the great ideas!