Martin and Listers,
Following is a copy of the MB window steps that perform a solution to
your problem of determining the closest point in one table to a point in
another table. Note that it is not a dynamic solution, but static, where
the shortest distance can be looked up in a third table. These steps
are based on a solution that I've seen, where it was required to find
the closest and furthermost point from each point in the same table.
Firstly, simplify the 2 tables down to 1 column each (usually the unique
identifier or something similar). Then follow the steps below. The
tables are called "points1" and "points2".
1. Alter Table "points1" ( add Join Integer ) Interactive
2. Create Index On points1 (Join)
3. Update points1 Set Join = 1
4. Browse * From points1
5. Alter Table "points2" ( add Join Integer ) Interactive
6. Create Index On points2 (Join)
7. Update points2 Set Join = 1
8. Browse * From points2
9. Select points1.Name_1, points2.Name_2, Distance (CentroidX
(points1.obj), CentroidY (points1.obj), CentroidX (points2.obj),
CentroidY (points2.obj), "m" ) "Distance" from points1, points2 where
points1.Join = points2.Join
order by col1, col3 into dist_list
10. Browse * From dist_list
Steps 1-4 add a column called "Join" to table "points1", update it's
value to "1" and index it
Steps 5-8 add a column called "Join" to table "points2", update it's
value to "1" and index it
Steps 9-10 then join the tables "points1" and "points2" on the column
called "Join" and determine the distance between the points using the
distance command. As both tables contain the "Join" field with the same
value ie. "1", every point in table "points1" will be matched with every
point in "points2" table, thus yielding every distance required. The
"order" phrase can be used to identify the shortest and longest etc.
distances as required.
** If the results are to be used again, a "SAVE" operation will be
required on the resultant "dist_list" table.
The down side of this solution is that the table size becomes large very
quickly as the multiplier effect is applied. The two tables that I used
had 120 and 201 points, yielding a result table of 24120 records. (120
times 201)
Hope that this is of some benefit.
___________________________________
Regards ...
John van Uitregt, GIS Analyst
Logan City Council, Queensland, Australia
Tel. 61-7-38265697 Fax 61-7-38080014
Email: mailto:[EMAIL PROTECTED]
Web Site: http://www.logan.qld.gov.au
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: 18 April, 2000 4:57 PM
> To: Mapinfo-l
> Subject: MI Point in one table nearest point in another?
>
> Hi folks
>
> I have a table of geocoded address points and another table containing
> geocoded points 1km apart (ie a grid) with individual values attached
> to
> each point.
>
> I've been trying to work out a way of finding out what grid point from
> the
> second table (and hence its value) is closest to a geocoded address in
> the
> first table. I tried buffering the address points and looking for
> grid
> points within the buffers but that wouldn't give me an answer. Is
> there a
> SQL statement I can use that asks for obj in table 2 closest to obj in
> table 1?
>
> thanks for your assistance
>
> Martin Gilbert
> Commonwealth Department of Health & Aged Care
> Canberra, Australia
>
>
> ----------------------------------------------------------------------
> To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and
> put
> "unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
>
> **********************************************************************
>
> If this Email contains Council related business then a copy MUST be
> forwarded to the Records Section for logging in the Records
> Management System.
>
> This email has been scanned and found to be free of all known viruses.
>
> **********************************************************************
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
----------------------------------------------------------------------
To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put
"unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
