MapInfo Pro

Hi all,

 I'm new to MpaInfo, so excuse me in advance if my question is trivial...

I've got records on the map that contains Lat-Long.
I'd like to run a query that would give me groups of records that are within a certain distance (in meters, or kilometers) from each other.
For example records A and D are 200m apart. If I put my threshold at 500m, I'd like to have as a result (A,D).

Thanks in advance

------------------------------
JJ CASALONGA
Knowledge Community Shared Account
------------------------------

I'm sure I'm not the most expert, but i'd

• Buffer the dataset to 500m, and save it as a new table
• Open the buffered table and the points table
• Run a sql query joining the two, where points.obj within buffered.obj

That would do it, although maybe there's a better way!

------------------------------
Martin Burroughs
Oldham Council
------------------------------

Assume your points are on a line, 300 meters apart. Still using a threshold of 500 meters.
Now A and B are within distance and so are B and C. But A and C are not (dist=600 meters).
Which result do you expect?

------------------------------
Uffe Kousgaard
ROUTEWARE
Roskilde
------------------------------

Hi,
This is the pseudo-SQL query that I'd like to acheive :

 Select A.ID, B.ID, Distance (A.Lat, A.Long, B.Lat, B.Long, "m")
From MyTable As A, MyTable as B
Where A.ID > B.ID
And A.Lat is not Null
And B.Lat is not Null
And Distance (A.Lat, A.Long, B.Lat, B.Long, "m") < 500

I looked in the MapInfo SQL, and there is indeed a Distance function.
But it doesn't allow me to join a table with itself.

And to answer your question, if A, B and C are on a straight line, each one separated by 300m, I'd like to have the results :
 A, B, 300
 B, C, 300

------------------------------
JJ CASALONGA
Knowledge Community Shared Account
------------------------------

Original Message:
Sent: 07-22-2022 10:19
From: Uffe Kousgaard
Subject: Query to find records that are closed from each other

Assume your points are on a line, 300 meters apart. Still using a threshold of 500 meters.
Now A and B are within distance and so are B and C. But A and C are not (dist=600 meters).
Which result do you expect?

------------------------------
Uffe Kousgaard
ROUTEWARE
Roskilde
------------------------------

Hi,

I would do sort of what you've down above.  However, I would add a column to the layer and put a value in all rows.  E.g. Column called join and update it with the word join.  Copy the layer and then as you almost suggest:

Select mytable.ID, mytable2.ID, Distance(centroidX(mytable.obj), centroidy(mytable.obj), centroidX(mytable2.obj), centroidy(mytable2.obj), "m")
From MyTable, mytable2
Where mytable.join = mytable2.join

this would join all the points to all the other points and calculate a distance for every combination.  You can then filter the results as a separate query.

There may well be a more elegant way of doing it but that's how I would do it!

HTH

Nick



------------------------------
Nick Hall
Mapchester LTD
Manchester
------------------------------

Original Message:
Sent: 07-22-2022 11:25
From: JJ CASALONGA
Subject: Query to find records that are closed from each other

Hi,
This is the pseudo-SQL query that I'd like to acheive :

 Select A.ID, B.ID, Distance (A.Lat, A.Long, B.Lat, B.Long, "m")
From MyTable As A, MyTable as B
Where A.ID > B.ID
And A.Lat is not Null
And B.Lat is not Null
And Distance (A.Lat, A.Long, B.Lat, B.Long, "m") < 500

I looked in the MapInfo SQL, and there is indeed a Distance function.
But it doesn't allow me to join a table with itself.

And to answer your question, if A, B and C are on a straight line, each one separated by 300m, I'd like to have the results :
 A, B, 300
 B, C, 300

------------------------------
JJ CASALONGA
Knowledge Community Shared Account
------------------------------


Original Message:
Sent: 07-22-2022 10:19
From: Uffe Kousgaard
Subject: Query to find records that are closed from each other

Assume your points are on a line, 300 meters apart. Still using a threshold of 500 meters.
Now A and B are within distance and so are B and C. But A and C are not (dist=600 meters).
Which result do you expect?

------------------------------
Uffe Kousgaard
ROUTEWARE
Roskilde

Take a look at this article: MapInfo Monday: Finding Objects nearby

First, you need to make a copy of your table and open this copy into MapInfo Pro too.

Use the Select using Location to build the structure of the query, and then modify it in the SQL Window.

As you compare a table to itself or a copy of itself, you want to exclude the joins between the identical records.
You need to add an additional condition through the SQL Window. I thought your idea of using this condition was brilliant:
Where A.ID > B.ID

That would avoid getting the reversed results too.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------