It's quite long, and please bear in mind that I don't claim to be a programmer so expect ugly. I couldn't upload the .mb so pasting below:
Sub ReHabCon
Dim aSite, aPridet, aInterQual, aHCond, aS1Date, aS2Date, aS3Date, aS4Date, aStatus1, aStatus2, aS1Done, aS2Done, aS4Done, aP1, aP2, aStew, aNonTO, aNotes, aScrub, aSDW, aDWF, aHol, aCav, aOT1, aOT2, aOT3, aOT4, aYT1, aYT2, aYT3, aYT4, aG, aM, aU, aGD As Alias
Dim sSite, sPridet, sInterQual, sHCond, sStatus1, sStatus2, sS1Done, sS2Done, sP1, sP2, sStew, sNonTO, sNotes, sScrub, sSDW, sDWF, sHol, sCav, sOT1, sOT2, sOT3, sOT4, sYT1, sYT2, sYT3, sYT4, sG, sM, sU, sGD As String
Dim iRowID, iRows, iHabCon, iS1Date, iS2Date, iS3Date, iS4Date, iToday, iP2Blank, iLevelUp, iYOT, iDataAge As Integer
iRowID = 0
iLevelUp = 0
select * from sTableName order by Incid into Sort
Select * from Sort
iRows = SelectionInfo(SEL_INFO_NROWS)
Run Menu Command 304
Do While iRowID < iRows
Print "_______"
iRowID = iRowID+1
Select * from Sort Where RowID = iRowID
Print iRowID
iHabCon = 0
iP2Blank = 0
sSite = ""
sHCond = ""
sPridet = ""
sHabCon = ""
sStatus1 = ""
sStatus2 = ""
sS1Done = ""
sS2Done = ""
dToday = 0
iS1Date = 0
iS2Date = 0
iS3Date = 0
iS4Date = 0
iToday = 0
sP1 = ""
sP2 = ""
sStew = ""
sNonTO = ""
sNotes = ""
sScrub = ""
sSDW = ""
sDWF = ""
sHol = ""
sCav = ""
sOT1 = ""
sOT2 = ""
sOT3 = ""
sOT4 = ""
sYT1 = ""
sYT2 = ""
sYT3 = ""
sYT4 = ""
sG = ""
sM = ""
sU = ""
aSite = Sort+"."+ColumnInfo(Sort, "Col1", COL_INFO_NAME) 'Site name
aInterQual = Sort+"."+ColumnInfo(Sort, "Col5", COL_INFO_NAME) 'InterQual
aHCond = Sort+"."+ColumnInfo(Sort, "Col6", COL_INFO_NAME) 'Habitat Condition
aS1Done = Sort+"."+ColumnInfo(Sort, "Col7", COL_INFO_NAME) 'S1 survey
aS2Done = Sort+"."+ColumnInfo(Sort, "Col12", COL_INFO_NAME) 'S2 OOQ
aStatus1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'StatusS1
aP1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'PlantingS1
aStatus2 = Sort+"."+ColumnInfo(Sort, "Col15", COL_INFO_NAME) 'StatusS2
aP2 = Sort+"."+ColumnInfo(Sort, "Col16", COL_INFO_NAME) 'PlantingS2
aS1Date = Sort+"."+ColumnInfo(Sort, "Col8", COL_INFO_NAME) 'S1 date to Alias
aS2Date = Sort+"."+ColumnInfo(Sort, "Col13", COL_INFO_NAME) 'S2 date to Alias
aS3Date = Sort+"."+ColumnInfo(Sort, "Col17", COL_INFO_NAME) 'S3 date to Alias
aS4Date = Sort+"."+ColumnInfo(Sort, "Col24", COL_INFO_NAME) 'S4 date to Alias
aStew = Sort+"."+ColumnInfo(Sort, "Col33", COL_INFO_NAME) 'Agri-environment
aNonTO = Sort+"."+ColumnInfo(Sort, "Col39", COL_INFO_NAME) 'Marginal
aNotes = Sort+"."+ColumnInfo(Sort, "Col42", COL_INFO_NAME) 'No visibility
aScrub = Sort+"."+ColumnInfo(Sort, "Col67", COL_INFO_NAME) 'Scrub
aSDW = Sort+"."+ColumnInfo(Sort, "Col81", COL_INFO_NAME) 'StandingDeadwood
aDWF = Sort+"."+ColumnInfo(Sort, "Col82", COL_INFO_NAME) 'DeadwoodFloor
aHol = Sort+"."+ColumnInfo(Sort, "Col78", COL_INFO_NAME) 'Branch holes
aCav = Sort+"."+ColumnInfo(Sort, "Col80", COL_INFO_NAME) 'Cavities
aOT1 = Sort+"."+ColumnInfo(Sort, "Col49", COL_INFO_NAME) 'OT 1-10
aOT2 = Sort+"."+ColumnInfo(Sort, "Col50", COL_INFO_NAME) 'OT 11-30
aOT3 = Sort+"."+ColumnInfo(Sort, "Col51", COL_INFO_NAME) 'OT31-100
aOT4 = Sort+"."+ColumnInfo(Sort, "Col52", COL_INFO_NAME) 'OT100+
aYT1 = Sort+"."+ColumnInfo(Sort, "Col53", COL_INFO_NAME) 'YT 1-10
aYT2 = Sort+"."+ColumnInfo(Sort, "Col54", COL_INFO_NAME) 'YT 11-30
aYT3 = Sort+"."+ColumnInfo(Sort, "Col55", COL_INFO_NAME) 'YT31-100
aYT4 = Sort+"."+ColumnInfo(Sort, "Col56", COL_INFO_NAME) 'YT100+
aG = Sort+"."+ColumnInfo(Sort, "Col68", COL_INFO_NAME) 'Grazed
aM = Sort+"."+ColumnInfo(Sort, "Col69", COL_INFO_NAME) 'Mown
aU = Sort+"."+ColumnInfo(Sort, "Col70", COL_INFO_NAME) 'Unmanaged
aGD = Sort+"."+ColumnInfo(Sort, "Col76", COL_INFO_NAME) 'GrazingDamage
sSite = aSite
sInterQual = aInterQual
sHCond = aHCond
sS1Done = aS1Done
sS2Done = aS2Done
sStatus1 = aStatus1
sStatus2 = aStatus2
sP1 = aP1
sP2 = aP2
iS1Date = aS1Date
iS2Date = aS2Date
iS3Date = aS3Date
iS4Date = aS4Date
sStew = aStew
sNonTO = aNonTO
sNotes = aNotes
sScrub = aScrub
sSDW = aSDW
sDWF = aDWF
sHol = aHol
sCav = aCav
sOT1 = aOT1
sOT2 = aOT2
sOT3 = aOT3
sOT4 = aOT4
sYT1 = aYT1
sYT2 = aYT2
sYT3 = aYT3
sYT4 = aYT4
sG = aG
sM = aM
sU = aU
sGD = aGD
iYT = 0
iOT = 0
iYOT = 0
'Print "iToday A = "+iToday
dToday = CurDate()-1825
iToday = dToday
'Print "iToday B = "+iToday
Print sSite
If iS1Date <> 0 And iS1Date > iToday Then Print "Top1" GoTo Update1
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Top2" GoTo Update1
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Top3" GoTo Update1
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Top4" GoTo Update1
End If
dToday = CurDate()-3650
iToday = dToday
'Print "iToday C = "+iToday
If iS1Date <> 0 And iS1Date > iToday Then Print "Mid1" GoTo Update2
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Mid2" GoTo Update2
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Mid3" GoTo Update2
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Mid4" GoTo Update2
ElseIf iS1Date <> 0 And iS1Date <= iToday Then Print "Low1" GoTo Update3
Elseif iS2Date <> 0 And iS2Date <= iToday Then Print "Low2" GoTo Update3
Elseif iS3Date <> 0 And iS3Date <= iToday Then Print "Low3" GoTo Update3
Elseif iS4Date <> 0 And iS4Date <= iToday Then Print "Low4" GoTo Update3
End If
Update1:
If sInterQual = "1 (High)" Then Update Selection Set PriDet = "Definitely is Traditional Orchard priority habitat"
GoTo ContinueSub
Else GoTo Update3
Update2:
Update Selection Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
GoTo ContinueSub
Update3:
Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
GoTo ContinueSub
End If
ContinueSub:
Update Selection Set Habdefver = "v2.6"
Update Selection Set Area_Ha = Area(obj, "hectare")
If sOT1 = "Y" Or sOT2 = "Y" Or sOT3 = "Y" Or sOT4 = "Y" Then iOT = 1
End If
Print "iOT = "+iOT
If sYT1 = "Y" Or sYT2 = "Y" Or sYT3 = "Y" Or sYT4 = "Y" Then iYT = 10
Print "iYT = "+iYT
End If
iYOT = iOT+iYT
If iYOT = 11 Then Update Selection set S2Planting = "Young trees in gaps"
End If
If sS2Done = "Done" Then
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If sP2 = "" Then
iP2Blank = 1
GoTo Try1 End If
If Like(sStatus2,"%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP2,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP2,"%gaps","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP2,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP2,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If
ElseIf sS1Done = "Done" Then
Try1:
If Like(sNotes,"%No visibil%","") = 1 Then
Update Selection Set HabCondition = "" Print "HabCon set to blank"
'If iP2Blank = 0 Then Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
'End If
GoTo Cont
Else
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If Like(sStatus1, "%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP1,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP1,"%gaps%","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP1,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP1,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If
End If
Else
Update Selection Set HabCondition = ""
GoTo Cont
End If
If Like(sNonTO,"Los%","") = 1 Then sHabCon = sHabCon+"H" End If
If Like(sNonTO,"Misident%","") = 1 Then sHabCon = sHabCon+"J" End If
If Like(sNonTO,"Relic%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Abandoned%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Intensively%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sSDW,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sDWF,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sHol,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sCav,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sG,"%Y%","") = 1 Then sHabCon = sHabCon+"D" End If
If Like(sM,"%Y%","") = 1 Then sHabCon = sHabCon+"E" End If
If Like(sGD,"%Y%","") = 1 Then sHabCon = sHabCon+"G" End If
If Like(sU,"%Y%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sScrub,"%D%","") = 1 Then sHabCon = sHabCon+"F" End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Habitat condition assessment
'Print "'"+sHabCon+"'"
If Like(sHabCon, "%H%"," ") = 1 Then iHabCon = iHabCon -1000000 End If 'H Lost
If Like(sHabCon, "%J%"," ") = 1 Then iHabCon = iHabCon -100000 End If 'J Misidentified
If Like(sHabCon, "%F%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'F Unmanaged/scrub
If Like(sHabCon, "%G%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'G Grazing damage
If Like(sHabCon, "%K%"," ") = 1 Then iHabCon = iHabCon -10000 End If 'K NonTO
If Like(sHabCon, "%A%"," ") = 1 Then iHabCon = iHabCon +600 End If 'A Gapped up (old and young trees)
If Like(sHabCon, "%L%"," ") = 1 Then iHabCon = iHabCon +500 End If 'L Established/mostly stocked
If Like(sHabCon, "%C%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'C No young trees
If Like(sHabCon, "%B%"," ") = 1 Then iHabCon = iHabCon +500 End If 'B Young orchard
If Like(sHabCon, "%I%"," ") = 1 Then iHabCon = iHabCon +500 End If 'I Deadwood canopy, cavities, floor
If Like(sHabCon, "%D%"," ") = 1 Then iHabCon = iHabCon +1 End If 'D Grazed
If Like(sHabCon, "%E%"," ") = 1 Then iHabCon = iHabCon +1 End If 'E Mown
Print iHabCon
If iHabCon >1100 Then
Update Selection Set HabCondition = "Excellent" Print "Excellent" sHCond = "Ex"
ElseIf iHabCon >=501 and iHabcon <=1100 Then
Update Selection Set HabCondition = "Good" Print "Good" sHCond = "Go"
ElseIf iHabCon <501 and iHabCon >-4000 Then
Update Selection Set HabCondition = "Poor" Print "Poor" sHCond = "Po"
ElseIf iHabCon <-90000 and iHabCon >-200000 Then Print "Misident"
Update Selection set PriDet = "Not applicable"
Insert Into Misidentified ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID
ElseIf iHabCon <-900000 Then Print "Lost"
Update Selection Set HabCondition = ""
Update Selection set PriDet = "Not applicable"
Insert Into Lost ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID
Else Update Selection Set HabCondition = "" sHCond = ""
End If
GoTo Cont
Cont:
'Print "Running Agrienv bit"
If sStew <> "" Then
If sHCond = "" Or sHCond = "Po" Then
Update Selection Set HabCondition = "Good" iLevelUp = iLevelUp +1 'Print sSite+" Agri-Env Level-up!"
End If
End If
'temp exit sub for analysis
'Exit sub
Loop
Print "Sites in stewardship fudged to Good = "+sTableName+", "+iLevelUp
'Commit Table sTableName Interactive
'Commit Table Lost Interactive
'Commit Table Misidentified Interactive
End Sub
------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------
Original Message:
Sent: 11-07-2022 01:40
From: Peter Møller
Subject: Geopackage problems
Hi Steve
>> I had to run a process to extract a value based on other values within the same attribute using a Do Loop
Would you be able to use SQL to do this?
Can you share your code for the process - just the Do Loop?
I don't have anything specific in mind but was wondering if seeing your loop could spark some ideas of why it is getting slow.
Maybe if you are fetching a specific record number instead of just fetching the next record can make the process slower. Not sure.
Are you updating the dataset through this loop too?
Thanks
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 11-05-2022 07:30
From: Steve Oram
Subject: Geopackage problems
I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.
After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.
I also can't modify the structure.
Any insights would be very welcome.
Steve
------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------