MapInfo Pro

 View Only
  • 1.  Query to find records that are closed from each other

    Posted 07-20-2022 04:44
    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
    ------------------------------


  • 2.  RE: Query to find records that are closed from each other

    Posted 07-21-2022 09:17
    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
    ------------------------------



  • 3.  RE: Query to find records that are closed from each other

    Posted 07-22-2022 10:19
    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
    ------------------------------



  • 4.  RE: Query to find records that are closed from each other

    Posted 07-22-2022 11:25
    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
    ------------------------------



  • 5.  RE: Query to find records that are closed from each other

    Posted 07-22-2022 11:51
    Edited by Nick Hall 07-22-2022 11:55
    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
    ------------------------------



  • 6.  RE: Query to find records that are closed from each other

    Employee
    Posted 08-01-2022 02:29
    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
    ------------------------------