SQL Query to find point in Table B from points within a certain distance from points in Table A

[David Rhodes]

Hello,

I have Mapinfo 16 Windows 7.  I am in great need of a query that will find all the points in table B that are within a certain distance from points in table A.  A real bonus would be to have another field that tells what the exact distance was.

Thank you for your consideration, David

[Peter Horne]

Hi David,
You could try the spider graph tool. You will need to have a common column in both tables. If one doesn't exist just create one. After running the spider tool you can then filter to your required distances.

See attached for simple example of table and map outout.

[Peter Horne]

OK, apologies, you did ask for an SQL solution so you could try something like this (uses the same simple tables as previous post) -

Select columns TAbleA.table, tableB.table, SphericalDistance or CartesianDistance(CentroidX(tablea.obj),CentroidY(tablea.obj),CentroidX(tableb.obj),CentroidY(tableb.obj), "km")
from TableA. tableB
where TableA.ID = tableB.ID
into qryDistanceA_B

You would then need to once again filter data to required distances.

Note the part Spherical or CartesianDistance is dependant on how your data is projected (or not). You will use either one or the other, not both.

[Peter Horsbøll Møller]

Let me add a bit of insight to Peter’s suggestion.

 

When he says you need to have a common column in both tables, he means that you should add a column to both tables with the same value in all records.

I would recommend adding an Integer column and just leaving the value to zero for the records in both tables.

 

These columns lets you do what is normally known as a cross join.

This will join all records in the first table with all the records in the second table:

 

Select *
From TableA. TableB
Where TableA.ID = TableB.ID
Into qryDistanceA_B

 

Now we can start extending the conditions of your query.

For instance specifying that you only want to look at the records within a certain distance of each other:

 

Select TableA.ID, TableB.ID, CartesianObjectDistance(TableA.OBJ, TableB.OBJ, "m") "Distance_m"

From TableA. TableB
Where TableA.ID = TableB.ID

AND CartesianObjectDistance(TableA.OBJ, TableB.OBJ, "m") < 1000
Into qryDistanceA_B

 

The CartesianObjectDistance() function is similar to the Distance() function, but it just takes two objects and calculates the nearest distance between these.

You can also just use the function ObjectDistance() if you aren’t working in a projected coordinate system.

 

Peter Horsbøll Møller

Pitney Bowes

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

---

[Peter Horsbøll Møller]

In case you are still using this statement or looking for a way to do this, MapInfo Pro v2019, can help you.

We added support for "Cross Joins" which allows you to join two table without having to use the = operator or one of the spatial operators.

This means you can write your query like this:

Select TableA.ID, TableB.ID, CartesianObjectDistance(TableA.OBJ, TableB.OBJ, "m") "Distance_m"

From TableA. TableB

AND CartesianObjectDistance(TableA.OBJ, TableB.OBJ, "m") < 1000

Into qryDistanceA_B

And our added support for table alias means that you can use an alias for your tables and so easier change the tables using, if that's needed. Personally, I also find it easier to read the Select statement when I'm using the table alias:

Select a.ID, b.ID, CartesianObjectDistance(a.OBJ, b.OBJ, "m") "Distance_m"

From TableA As "a". TableB As "b"

AND CartesianObjectDistance(a.OBJ, b.OBJ, "m") < 1000

Into qryDistanceA_B

Peter Horsbøll Møller