[GIS] Select all points that fall within any polygon from multiple layers

mapinfosqlwithin

I'm using MapInfo and would like to achieve the following:

Find out all the points that fall within any of the polygons on screen, and which polygons they are contained within.

The data I am working with:

enter image description here

As you can see, there are about 30 different layers. So far I can only see myself going through each table with an SQL statement as I cannot seem to do it in bulk (or statement between multiple tables generates errors regarding join), but then this could produce 30 different files that then need sorting.

Is it possible to do an update table? For example a new column titled "Overlap" which will print the name of the polygon it is contained in or the layer name.

I am open to suggestions.

Best Answer

You will need to do individual queries for each polygon layer. Or you would need to merge all the polygon layers and then join this new layer/table with the point table.

You can update a field in the point table with the name of the polygon or the polygon table name. That might be the best solution.

The best approach would be to do this via a MapBasic tool but we can mimic it using the MapBasic window even though you will have to do the looping by hitting the Enter key 30 times.

First open the point table so that it's the first table opened.

Next open the 30'ish tables to compare with the point table.

Use these statements to select and update the points within a polygon in the polygon table with the name of the polygon table:

Dim sTab As String
Dim aCol As Alias
sTab = TableInfo(2, 1)
aCol = sTab + ".OBJ"
Print "Joining with " + sTab + ". Still " + (NumTables() - 2) + " tables to go"
Select points.OVERLAPTABLE
  From points, sTab
  Where points.obj Within aCol
  Into __TO__BE__UPDATED NoSelect
Print "   " + TableInfo("__TO__BE__UPDATED", 8) + " overlaps"
Update __TO__BE__UPDATED Set OVERLAPTABLE = sTab
Close Table sTab
Undim sTab
Undim aCol

You can copy and paste these lines into the MapBasic window, highlight all the lines and hit Enter. This will run all the statements. Hit Enter again until the Message window say "... 0 tables to go"

Note that I have used "points" for the table containing points and "overlaptable" for the column that should be updated with the name of the tables. You can use other names if you prefer.

The names of the other tables, the 30 polygon tables, are read using a MapBasic function at runtime.

Now if you expect some points to be within polygons from multiple tables and you want to know which, you will have to do the update a bit differently. Use this statement for the update:

Update __TO__BE__UPDATED Set OVERLAPTABLE = OVERLAPTABLE + sTab + ", "
Related Question