Associating customers with the nearest stores is do-able, but a
bit complicated. This ought to be a MapInfo built-in function,
because if you are new to MapInfo, most of the following probably
will go right past you, but it does work. I've cc'ed this to the
MapInfo-L list so maybe someone who has a bit of time could turn
this into a generic utility for new MapInfo users. And perhaps
someone can come up with a simpler approach.
Anyway, here goes:
The general object here is to create a table that has all your
customers and their distances to all the stores. You order that
file by customer ID and Distance. Then you take that table and
group by customer ID (since the shortest distance to any of the
stores was listed first, the group by clause will grab this first
record for each customer-store record. All you need from this
last file is store Id and customer Id, and then save that table
(so it will be a permanent table, because we need to use it in a
join next.) Open the table you just saved and join it to the
customer records. Now for each customer, you have the store id
for the store that is the closest to that customer.
To create the starting table with all stores and customers with
their distances, you first need to add some columns to each
table. To the Stores table, add three fields with the following
names and types: StoreLon float, StoreLat float, and Link
integer. To the Customers table, add three fields with the
following names and types: CustLon float, CustLat float, and Link
integer. Update the tables to fill in the points' coordinates
into the lat and lon fields (using the CentroidX and CentroidY
functions. In the end you should have the coordinates for the
stores in StoreLon, StoreLat, the coordinates for Customers in
CustLon and CustLat, and leave 0 in both Link fields.
No do a cartesian join on your two tables using the Link field.
Join the tables where the link field in one matches the link in
the other. Of course these are all zero, so you'll get a table
with every customer joined to every store (which is what we
want.) In the list of fields that you select, you will also need
to create a calculated field for distance, so the full SQL select
might look like:
Select stores.StoreId, Customers.CustId,
Distance(stores.StoreLon, stores.StoreLat,
Customers.CustLon, Customers.CustLat, "mi") "Distance"
from stores, Customers
where stores.Link=Customers.Link
order by Customers.CustId, Distance
into JOIN
Next SQL select that table (JOIN) and group by the CustId and
create the table NEAREST, and extract only the Id numbers, like
so:
Select StoreId, CustId
from JOIN
group by CustId
order by CustId
into NEAREST
Now save this table; close the temporary version and open the one
you just saved so that now you have a permanent table named
Nearest. Join this with the Customer table like so:
Select Customers.CustId, Customers.Name, Nearest.StoreId
from Customers, Nearest
where Customers.CustId=nearest.CustId
order by Customers.CustId
into NEAREST2
Now you have a list of customers and their names (in this case)
with the store Id nearest to them.
Simple, huh?
- Bill Thoen
Berk Charlton wrote:
>
> Mapinfo tech support has been unable to help me with this scenario:
>
> Let's say I have two point tables, one is a table of store locations, and the
> other is a table of customers. I need to be able to put the store id and
> distance to the closest store to each of the customer points.
>
> I new to Mapinfo, but in the past been able to do this in Arcview and Atlas GIS
> easily.
>
> Any suggestions?
>
> Thanks,
> Berk Charlton
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
