Hi Rhonda,
If I understand you, then this is the sort of thing you are trying to
achieve...
0) Create Points for both tables, "BUFFER" those in PROBABLES to the
required radius/distance (1320' or whatever).
1) Use a For..Next Loop and the Fetch Rec nLoop from TABLE command to loop
through the PROBABLES table
2) For each row in PROBABLES. Use SQL Select to find all the WELLS WITHIN
XXXX feet (1320' in your example - set from a DIALOG BOX).
3) Save the Newly created selection QUERYn as a tablename relevant to data
in the PROBABLES table.
4) Open QUERYn (or whatever it is called now), add a column - distance.
5) Use Table-Update Column and set it to the distance between the 2
lat/longs.
6) Save the table.
Does this make sense? Try the above inside MapInfo, with the MapBasic
window open. It will generate most of the code that you need.
If I totally missed the mark, I do apologise.
Hope some of the above works,
Mark Crompton
Ah, now I read the rest of your notes... Here's a sample of how to do 1 row
of the above items 2-6
****************************************************************************
************************
SQL SELECT
FROM TABLES should contain both table names
WHERE should look something like this: WELLS.Obj Within Probables.Obj
This should produce a table with everything but the distance in it. You
cannot modify temporary tables, so this one must be saved first, re-opened
and then a column added for distance. Distance can then be calculated again
by using Table-Update Column (in the newly added column) using the
DISTANCE(LONG1, LAT1, LONG2, LAT2, "mi") function, returning a measurement
in miles.
Please tell me this all makes sense...
-----Original Message-----
From: Rhonda Bachman [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 27, 1999 7:26 AM
To: [EMAIL PROTECTED]
Subject: MI FW: Select "offset" information
Does anyone on the list have any ideas as to how to go about this?
-----Original Message-----
From: Rhonda Bachman
Sent: Tuesday, October 26, 1999 2:36 PM
To: 'Bill Thoen'
Subject: RE: Select "offset" information
Thank you for your information. Let me further explain what I have and maybe
it'll get a bit closer.
I have a table called "Probables" - this has lat/longs, names, location
names with some misc data. Consists of 219 records. No ID's unless it would
be a row ID.
I have another table called "Wells" - this has all the above plus other misc
data. Has an ID (API number) but would not match on anything from
"Probables". This table has approximately 29000 records.
The only "distance" reference of any sort are the lat/long columns.
On my map, I have been manually selecting "Wells" that are in the immediate
area of "Probable" - say 1320' (should be an adjustable footage) around the
probable. I save this as a new table with the name of the "Probable" that it
refers to. For the most part, the offsets would consist of more than one
record per probable.
What I'm wanting (hoping) to be able to do, is automate this procedure and
also include the name of the probable in a column for which the offsets were
"picked."
Any hope?
Thanks so much for your time and help...
-----Original Message-----
From: Bill Thoen [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
Sent: Tuesday, October 26, 1999 10:58 AM
To: Rhonda Bachman
Subject: Re: Select "offset" information
Rhonda,
Here's my entire note. Let me know if there are spots that need
to be clarified.
---
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.
Now 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
Rhonda Bachman wrote:
>
> Bill - the part I typed as your response is the only thing I saw - there
was
> no explanation of any kind. I pulled that sentence from the archives.
>
> Yes, I do want to find out the closest points in table 2 that are
> closest to the points in table 1. Each table has lat/long information. I
do
> know how to use the SQL and join tables, but for what I'm wanting, I don't
> know the syntax. I really appreciate any help you can provide.
>
> Thank you.
>
> -----Original Message-----
> From: Bill Thoen [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
> Sent: Monday, October 25, 1999 6:09 PM
> To: Rhonda Bachman
> Subject: Re: Select "offset" information
>
> Rhonda,
>
> Do you mean that you want to know for each point in table 2 which
> point in table 1 is it closest to? And do you also want to limit
> the search to points where the distances are also less than some
> user-defined limit?
>
> I thought I did explain the "how", but I didn't spell out the
> details I guess. I was trying to cover a lot of ground and not
> spend all day writing an explanation. Also, the method I used
> doesn't work beyond a certain number of connections (although 219
> isn't too many. Anyway, it requires that you know how to use the
> SQL select option in the Query menu. If you do know how to join
> tables with SQL, then what part did I leave too vague?
>
> Also I got a much better solution from another MapInfo-L'er,
> which I will post RSN.
>
> - Bill Thoen
>
> Rhonda Bachman wrote:
> >
> > "now you have a list of customers and their names (in this case) with
the
> > store ID nearest to them"....
> >
> > This is your response dated October 20, 1999 to a question from Berk
> > Charlton. I believe this "answer" is exactly what I'm looking for but
you
> > did not elaborate as to the "how" to do it...
> >
> > I have two tables - I am wanting to select all "offsetting" data from
one
> > table that is in proximity to a point in another table. Offsetting data
> may
> > consist of more than one record in relation to the point in the other
> table.
> >
> > At this point, if I continue to select my offsets manually, I will end
up
> > with 219 tables, as I have 219 points in the table for which I need the
> > offsets....
> >
> > Can you help?
> >
> > Thanks in advance....
> >
> > Rhonda Bachman
> > Louis-Dreyfus Natural Gas
> > [EMAIL PROTECTED]
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
