MapInfo Pro

Expand all | Collapse all

SQL syntax

  • 1.  SQL syntax

    Posted 10-12-2018 02:27

    I have 2 separate point layers and want to know the points from each layer that occur within 10m of each other. Can someone help me with the SQL syntax for that? Thanks in advance.



  • 2.  RE: SQL syntax

    Posted 10-12-2018 12:31

    Hi,

    Try this:

    Select table_a.id, table_b.id

    from table_a, table_b

    where CartesianObjectDistance(table_a.obj, table_b.obj, "m") <10 and table_a.crossjoin_id = table_b.crossjoin_id into joined_points_10m?

    crossjoin_id is an integer column containing all the same id (eg all zeros). You need to perform a cross join - where every point in table a is linked to every point in b before you can compare the distances.

    You can use ObjectDistance for non-projected data.

     



  • 3.  RE: SQL syntax

    Posted 10-12-2018 15:57

    Hi @Tom Parker? 

    I really like your workaround for doing a cross join. I had similar thought but never actually tried it out yet. 👌

    However, I'd think you need to change the order of the conditions so that the actual join is the first condition:

    Select table_a.id, table_b.id

    from table_a, table_b

    where table_a.crossjoin_id = table_b.crossjoin_id

    And CartesianObjectDistance(table_a.obj, table_b.obj, "m") <10

    into joined_points_10m?



  • 4.  RE: SQL syntax

    Posted 10-22-2018 19:33

    Thanks Tom and Peter, I appreciate your help.