Need Help with Geographical Sort

[Dana F. Brewer]

Hi, and thanks! I have a table of thousands of names and addresses that I
have to make turn by turn maps from. I am using Access to manage the data
and another program for the turn by turn. I am exporting the records from
access by sorting them by either longitude, latitude (which sometimes gives
me addresses spanning the breadth of the city) or latitude, longitude (which
usually gives me addresses in a linear fashion).

Sometimes there are several addresses on the same street but because of
their lat or long they don't get put onto the same map. Can anyone suggest
an alternate sort that might help me to get all addresses within a square
instead of these weird rectangular patterns? I can't just sort by street
name because the street names in a city are not in any sort of order.

Thanks!!!

 

[KARL DEWEY]

I do not follow as longitude and latitude signify a single point.
Are your street addresses loaded as cross streets against the longitude and
latitude combinations?
Post the table and field names with datatype.
Some sample data would be nice along with what you want as an output.

 

[Dana F. Brewer]

Thanks. I hope this will suffice:

ID, Name, Number (Long), Street (Text), Address2 (Text), City, State, Zip
(Text), Longitude (Double), Latitude (Double)

Some cities have hundreds of records all with the same zip code. I was
thinking I could sort or group by zip (after changing to number type) and
then do lat/long or long/lat but this wouldn't work in the many cases where
the zip code is the same.

 

[John W. Vinson]

Hi, and thanks! I have a table of thousands of names and addresses that I
have to make turn by turn maps from. I am using Access to manage the data
and another program for the turn by turn. I am exporting the records from
access by sorting them by either longitude, latitude (which sometimes gives
me addresses spanning the breadth of the city) or latitude, longitude (which
usually gives me addresses in a linear fashion).

Sometimes there are several addresses on the same street but because of
their lat or long they don't get put onto the same map. Can anyone suggest
an alternate sort that might help me to get all addresses within a square
instead of these weird rectangular patterns? I can't just sort by street
name because the street names in a city are not in any sort of order.

Thanks!!!

Well... you're asking for a general algorithm to collapse two dimensional
(lat/long) data to a one-dimensional ("close" or "together") result. I don't
know if there is a generalized way to do so!

If you have the ZIP+4 zipcode, the postal service will have done some of that
for you: the suffix is set up for the postal carriers to make delivery easier.
That may not help, and probably won't be reliably available, though.

One (laborious) solution might be to define your own geographical grid -
either by "neighborhoods", or if that's not feasible, by setting up a grid at
some suitable level of precision (say one minute of lat/long square),
assigning each grid block a number, and using a non-equi join query to assign
each address to its grid block. DeLorme map books have such a grid (but are
pricey).

In any case, there is no real way to linearize 2D data so that any two points
that are "close" will have "close" 1D numbers, to my knowledge.