Nearest geographical neighbour table design...

[Jamie Risk]

I have two tables in my database that refer to a third database
to look up geographical location (as in "Ontario", "New York",
"New Jersey" etc.)

The first two tables contain groups of people that I wish to
match according to their locations. As an example I'd like to
match somebody in Kansas with people in Nebraska, Missouri,
Oklahoma, and Colorado.

I'm thinking the solution will involve the creation of a fourth
table [StateNeighbours] with two significant fields in it
([StateNeighbours.State] and [StateNeighbours.Neighbour], each
being a lookup up to the existing [State] table.

It would easy enough to design the database so that for every
state, I would require all the neighbours be listed in
[StateNeighbours]. This would create redundant information in
my database though:
State Neighbour
... ...
Kansas Colorado
Kansas Arkansas
... ...
Colorado Kansas <- Redundant entry


In reality my '[State]' is a list of 400 locations.


How would I design the DB to avoid entering redundant
information AND consequently extract all that information?

Thanks,

- Jamie

 

[Duane Hookom]

I would probably enter a location such as latitude and longitude for
locations. Then, you can find all the other locations within a specific
radius.

 

[Jamie Risk]

I would probably enter a location such as latitude and longitude for
locations. Then, you can find all the other locations within a specific
radius.

That would simplify things if I were interested in knowing who's
in a certain radius, and if my geographical locations were
geometrically similar. (What do I use as UTM coordinates, the
capitol, the geometric center?)

I'm still looking to solve the problem as originally stated.

 

[Duane Hookom]

How you would set up the coordinates depends on your needs. You might use
two coordinates. One for Northwest corner and the other for the Southeast
corner.

You can reduce redundant information by storing just State and Neighbor.

SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

This would return

Kansas Colorado
Kansas Arkansas
Colorado Kansas
Arkansas Kansas

 

[Jamie Risk]

You can reduce redundant information by storing just State and Neighbor.

SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

This would return

Kansas Colorado
Kansas Arkansas
Colorado Kansas
Arkansas Kansas

Thanks for responding, but that is the opposite of what I'm
hoping to achieve. If a state pair exists, I don't want it to
appear *redundantly* in a different order again in the list.

 

[Duane Hookom]

Assuming the table structure and records

tblStateNeighbors
=========================
State Neighbor
------------ ----------------
Kansas Colorado
Kansas Arkansas
(note Colorado is not listed as a State, only a neighbor)

And a union query quniStateNeighbors
SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

I assume you may want to query for all neighbors of Colorado. Using a the
union query, you would simply query like:

SELECT Neighbor
FROM quniStateNeighbors
WHERE State = 'Colorado'

If you want something to appear redundantly, then work harder to use:
SELECT Neighbor, State
FROM quniStateNeighbors
WHERE State = 'Colorado' Or Neighbor='Colorado'
This would not ever make much sense.

 

[Jamie Risk]

Assuming the table structure and records

tblStateNeighbors
=========================
State Neighbor
------------ ----------------
Kansas Colorado
Kansas Arkansas
(note Colorado is not listed as a State, only a neighbor)

And a union query quniStateNeighbors
SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

I assume you may want to query for all neighbors of Colorado. Using a the
union query, you would simply query like:

SELECT Neighbor
FROM quniStateNeighbors
WHERE State = 'Colorado'


[TRIM]

Very useful thanks. But my other question remains ... How will
I restrict entering redundant 'neighbours' one the form? i.e as
in your example disallow an entry like:

State Neighbor
---------- ----------------
Kansas Colorado
Kansas Arkansas
Colorado Kansas <--- disallow entry?

 

[David F Cox]

This is not a recommendation, but one possible solution.

You could have the requirement that states must be entered in alphabetical
order and enforce it by a general validation rule [state1] < [state2]. This
also eliminates the Kansas - Kansas case.

Assuming the table structure and records

tblStateNeighbors
=========================
State Neighbor
------------ ----------------
Kansas Colorado
Kansas Arkansas
(note Colorado is not listed as a State, only a neighbor)

And a union query quniStateNeighbors
SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

I assume you may want to query for all neighbors of Colorado. Using a the
union query, you would simply query like:

SELECT Neighbor
FROM quniStateNeighbors
WHERE State = 'Colorado'


[TRIM]

Very useful thanks. But my other question remains ... How will I restrict
entering redundant 'neighbours' one the form? i.e as in your example
disallow an entry like:

State Neighbor
---------- ----------------
Kansas Colorado
Kansas Arkansas
Colorado Kansas <--- disallow entry?

 

[Duane Hookom]

David's response would have been my first reply and then add a primary key
(or unique index) on the two fields to prevent duplicates.

--
Duane Hookom
MS Access MVP

This is not a recommendation, but one possible solution.

You could have the requirement that states must be entered in alphabetical
order and enforce it by a general validation rule [state1] < [state2].
This also eliminates the Kansas - Kansas case.

Assuming the table structure and records

tblStateNeighbors
=========================
State Neighbor
------------ ----------------
Kansas Colorado
Kansas Arkansas
(note Colorado is not listed as a State, only a neighbor)

And a union query quniStateNeighbors
SELECT State, Neighbor
FROM tblStateNeighbors
UNION ALL
SELECT Neighbor,State
FROM tblStateNeighbors;

I assume you may want to query for all neighbors of Colorado. Using a
the union query, you would simply query like:

SELECT Neighbor
FROM quniStateNeighbors
WHERE State = 'Colorado'


[TRIM]

Very useful thanks. But my other question remains ... How will I
restrict entering redundant 'neighbours' one the form? i.e as in your
example disallow an entry like:

State Neighbor
---------- ----------------
Kansas Colorado
Kansas Arkansas
Colorado Kansas <--- disallow entry?

 

[Jamie Risk]

Personally, I'd want to model both (Kansas, Colorado) and (Colorado,
Kansas) in the same table because they are two 'neighbours'
relationships. You may consider your preferred solution to involve less
'redundancy' but what's the point when *every* time you use the table
for its intended purpose you must re-establish all the two-way
'neighbours' relationships e.g. via the overhead of a UNION query?

Jamie.

--

Because the state/neighbour is a design analogy of what I'm
trying to do. My 'State' list is several million entries long,
with potentially 30 or 40 neighbours.