MapInfo Pro

Hi,

I have a table of small regions (t1) and a table of large regions (t2). These tables are not coterminous and the boundaries overlap. I'd like to populate a "Most_In" field in t1 with the name of the t2 region that overlaps it the most. 

Example: region 1 from t1 overlaps regions a, b and c from t2. Of the overlaps, region b covers the most area. So the "Most_In" value would be "B". 

Here is my program. Unfortunately due to the size of the tables (specifically t1 which is 20,000 rows), it just takes far too long to be useful:

Include "MAPBASIC.DEF"

Declare Sub Main
 
Dim t1 as String
Dim t2 as String
Dim t1Obj as Object
Dim t1Name as String
Dim MostArea as Float
Dim MostName as String
Dim t1alias as Alias
Dim cnt as Integer
Dim TotRow as Integer
Dim MaxVal as Float
 
'''''''''''''''''''''''''''''''''''''''
Sub Main
 
t1 = Input("Main object table", "_Table:", "table", "TableInfo(ScriptContext(2)")
If Not CommandInfo(CMD_INFO_DLG_OK) then Note "Exiting!" End Program Else End if
 
t2 = Input("Second object table", "_Table:", "table", "TableInfo(ScriptContext(2)")
If Not CommandInfo(CMD_INFO_DLG_OK) then Note "Exiting!" End Program Else End if
 
cnt = 0
TotRow = tableinfo(t1,TAB_INFO_NROWS)
Fetch First from t1
 
''''''''''''''''''''''''''''''''''''
Do until EOT(t1)
  t1alias = t1 + ".Name"
  t1Name = t1alias
  t1alias = t1 + ".obj"
  t1Obj = t1alias
 
  Print "Finding overlaps..."
  Select * from t2 where obj partly within t1Obj into _OVERLAPS
 
  Print "Calculating area overlap..."
  Update _OVERLAPS set AreaOv = AreaOverlap(_OVERLAPS.obj, t1obj)
 
  Print "Finding max..."
  Select Max(AreaOv) as "Max" From _OVERLAPS into _MAXLAP
  Fetch First from _MAXLAP
  MaxVal = _MAXLAP.Max
 
  Print "Finding most..."
  Select Name from _OVERLAPS where AreaOv = MaxVal into _MostName
  Fetch First from _MostName
  MostName = _MostName.Name
 
  Print "Updating table with most..."
  Update t1 set Most_In = MostName where Name = t1Name
 
  cnt=cnt+1
  Print "Done " + cnt + " of " + TotRow + "..."
  Fetch Next From t1
Loop
 
'''''''''''''''''''''''''''''''''''''
Note "Complete!"
End Program

End Sub

------------------------------
Ryan Cook
ORH LTD
------------------------------

I would suggest eliminating some of the Select statements and the Update statement.

Change this:

Print "Finding overlaps..."
Select * from t2 where obj partly within t1Obj into _OVERLAPS
 
Print "Calculating area overlap..."
Update _OVERLAPS set AreaOv = AreaOverlap(_OVERLAPS.obj, t1obj)
 
Print "Finding max..."
Select Max(AreaOv) as "Max" From _OVERLAPS into _MAXLAP
Fetch First from _MAXLAP
MaxVal = _MAXLAP.Max
 
Print "Finding most..."
Select Name from _OVERLAPS where AreaOv = MaxVal into _MostName
Fetch First from _MostName
MostName = _MostName.Name

to this:

Print "Finding overlaps, calculating overlap, and sorting by overlap..."
Select  Name, AreaOverlap(obj, t1obj) "Overlap"
from t2 
where obj partly within t1Obj 
Order By Overlap
into _MostName NoSelect Hide
 
Fetch First from _MostName
MaxVal = _MostName.Overlap
MostName = _MostName.Name

I would also recommend getting the RowID from the current record you are processing, and using this when updating:

t1alias = t1 + ".RowID"
t1RowID = t1alias

...

Print "Updating table with most..."
Update t1 set Most_In = MostName where RowID = t1RowID

Note that I added Hide to your Select statement. This will hide the temporary query from the Table List and so save time refreshing the screen.

Finally, you could convert this into a function and use this to update the Name column in your t1 table:

Update t1 Set Name = GetMostOverlappingName(OBJ)

The function GetMostOverlappingName takes the obj from t1 as a parameter and will return the name of the most overlapping polygon from t2. The content of the function will be similar to the changed example I gave above.

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

Thanks Peter, I will give your suggestions a whirl!

Print "Finding overlaps..."Select * from t2 where obj partly within t1Obj into _OVERLAPS Print "Calculating area overlap..."Update _OVERLAPS set AreaOv = AreaOverlap(_OVERLAPS.obj, t1obj) Print "Finding max..."Select Max(AreaOv) as "Max" From _OVERLAPS into _MAXLAPFetch First from _MAXLAPMaxVal = _MAXLAP.Max Print "Finding most..."Select Name from _OVERLAPS where AreaOv = MaxVal into _MostNameFetch First from _MostNameMostName = _MostName.Name

Print "Finding overlaps, calculating overlap, and sorting by overlap..."Select  Name, AreaOverlap(_OVERLAPS.obj, t1obj) "Overlap"from t2 where obj partly within t1Obj Order By Overlapinto _MostName NoSelect Hide Fetch First from _MostNameMaxVal = _MostName.OverlapMostName = _MostName.Name

t1alias = t1 + ".RowID"t1RowID = t1alias...Print "Updating table with most..."Update t1 set Most_In = MostName where RowID = t1RowID

Update t1 Set Name = GetMostOverlappingName(OBJ)

Incredible. It's gone from updating one row every 10-20 seconds to updating ~100 rows per second. I didn't realise you could stick any function into the select expression list - that might be a game-changer!

Also disappointed in myself for not spotting the far more streamlined and obvious sort method for getting the max value rather than faffing about with all that maxval stuff. 

Thanks again Peter!

Print "Finding overlaps..."Select * from t2 where obj partly within t1Obj into _OVERLAPS Print "Calculating area overlap..."Update _OVERLAPS set AreaOv = AreaOverlap(_OVERLAPS.obj, t1obj) Print "Finding max..."Select Max(AreaOv) as "Max" From _OVERLAPS into _MAXLAPFetch First from _MAXLAPMaxVal = _MAXLAP.Max Print "Finding most..."Select Name from _OVERLAPS where AreaOv = MaxVal into _MostNameFetch First from _MostNameMostName = _MostName.Name

Print "Finding overlaps, calculating overlap, and sorting by overlap..."Select  Name, AreaOverlap(_OVERLAPS.obj, t1obj) "Overlap"from t2 where obj partly within t1Obj Order By Overlapinto _MostName NoSelect Hide Fetch First from _MostNameMaxVal = _MostName.OverlapMostName = _MostName.Name

t1alias = t1 + ".RowID"t1RowID = t1alias...Print "Updating table with most..."Update t1 set Most_In = MostName where RowID = t1RowID

Update t1 Set Name = GetMostOverlappingName(OBJ)

You can, as I mentioned, also take this to yet another level using a function.

Include "MAPBASIC.DEF"

Declare Sub Main
Declare Function GetMostOverlappingName(ByVal oPolygon As Object) As String

Dim t1 as String
Dim t2 as String
Dim MostName as String

'''''''''''''''''''''''''''''''''''''''
Sub Main

Dim tStart As Time

	t1 = Input("Main object table", "_Table:", "table", "TableInfo(ScriptContext(2)")
	If Not CommandInfo(CMD_INFO_DLG_OK) then Note "Exiting!" End Program Else End if

	t2 = Input("Second object table", "_Table:", "table", "TableInfo(ScriptContext(2)")
	If Not CommandInfo(CMD_INFO_DLG_OK) then Note "Exiting!" End Program Else End if

	tStart = CurTime()
	Update t1
		Set Most_In = GetMostOverlappingName(OBJ)

'''''''''''''''''''''''''''''''''''''
	Note "Completed: " & (CurTime() - tStart)  & " secs"
	End Program

End Sub

Function GetMostOverlappingName(ByVal oPolygon As Object) As String

GetMostOverlappingName = ""

	Select  Name, AreaOverlap(obj, oPolygon) "Overlap"
		from t2
		Where Obj partly within oPolygon
		Order By Overlap
		into _MostName NoSelect Hide

 	If TableInfo(_MostName, TAB_INFO_NROWS) > 0 Then
		Fetch First from _MostName
		MostName = _MostName.Name
		Close Table _MostName

		GetMostOverlappingName = MostName
	Else
		Close Table _MostName
	End If

End Function

PS: I made a typo in one of the Select statements in my first reply:

Select  Name, AreaOverlap(_OVERLAPS.obj, t1obj) "Overlap"

should have been:

Select  Name, AreaOverlap(obj, t1obj) "Overlap"

I have changed it in my first reply as well.

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

Print "Finding overlaps..."Select * from t2 where obj partly within t1Obj into _OVERLAPS Print "Calculating area overlap..."Update _OVERLAPS set AreaOv = AreaOverlap(_OVERLAPS.obj, t1obj) Print "Finding max..."Select Max(AreaOv) as "Max" From _OVERLAPS into _MAXLAPFetch First from _MAXLAPMaxVal = _MAXLAP.Max Print "Finding most..."Select Name from _OVERLAPS where AreaOv = MaxVal into _MostNameFetch First from _MostNameMostName = _MostName.Name

Print "Finding overlaps, calculating overlap, and sorting by overlap..."Select  Name, AreaOverlap(_OVERLAPS.obj, t1obj) "Overlap"from t2 where obj partly within t1Obj Order By Overlapinto _MostName NoSelect Hide Fetch First from _MostNameMaxVal = _MostName.OverlapMostName = _MostName.Name

t1alias = t1 + ".RowID"t1RowID = t1alias...Print "Updating table with most..."Update t1 set Most_In = MostName where RowID = t1RowID

Update t1 Set Name = GetMostOverlappingName(OBJ)