MapInfo Pro

Last week, we also looked at Service Area Assignment. You may recall we ended up with a pretty long script that was run from the SQL window in MapInfo Pro. If you can't remember how long that script was, please go back and have a look.

I was basically repeating the same query with different parameters several times.

I said this could be done more simply with an actual MapBasic add-in, so here we go.

Assign Service Areas Add-in

When you build a MapBasic add-in instead of a MapBasic script, you get some benefits, for example, to be able to use custom functions. It does, however, get a bit more complicated: it needs to be compiled, so you can't see or alter the code without recompiling.

In most cases, the benefits do outplay the shortcomings.

For example, in the script, I had to run the query several times. This is done by writing the query the same number of times. In my add-in, I can create a function that wraps my query and then call it several times with different parameters.

I decided to build a function that could assign areas to service engineers based on the drivetime zones. The logic is the same as in the script; I just added a bit to it to count the number of areas assigned.

Here is my function:

'**************************************************************************
Function AssignAreaDT(    ByVal nDTValue As Integer
                        , ByVal fOverlapPct As Float
                        ) As Integer    'Returns number of areas were assigned

Dim nRows As Integer

AssignAreaDT = 0

    Print Time(24) & " " + nDTValue + " min. Drivetime: " + Format$((fOverlapPct * 100), "#,###.") + "% Overlap" 
    Select sa.postsect, sa.NameNew, sa.DistanceNew
        , sa.PropOverlapNew, dt.NAME, dt.value
        , ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
        From msTabServiceAreas As "sa", msServiceEngineerDTs As "dt"
        Where sa.Obj intersects dt.Obj
        And dt.value = nDTValue
        And sa.DistanceNew = 0
        And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
        Order by sa.postsect, PropOverlap Asc
        Into _to_update NoSelect Hide

    nRows   = TableInfo(_to_update, TAB_INFO_NROWS)

    If nRows > 0 Then
        Update _to_update
            Set NameNew = Name,
            DistanceNew = value,
            PropOverlapNew = PropOverlap

        Select postsect
            From _to_update
            Group By postsect
            Into _count_records NoSelect Hide
        nRows   = TableInfo(_count_records, TAB_INFO_NROWS)        
        Close Table _count_records
    End If 
       
    Close Table _to_update
    AssignAreaDT  = nRows

End Function

The initial query is the same as in the script. Instead of just updating the result, I start by validating the number of records to update. If there are no records, there is no reason to run the update statement.

If I'm updating the result, I also calculate the number of records being updated. I perform an additional query to find this number, as some service areas may overlap multiple drive time zones and therefore can appear multiple times in the initial query.

Because I now have this as a function, I can call it in my Main procedure and just change the parameters: the drivetime value in minutes and the overlap percentage.

Here you can see how I use this function several times in my Main procedure:

    '**15 minutes    
    nValue = 15
    fOverlapPct = 0.5
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"
    
    '**30 minutes    
    nValue = 30
    fOverlapPct = 0.5
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"

    '**45 minutes    
    nValue = 45
    fOverlapPct = 0.5
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"

    '**60 minutes    
    nValue = 60
    fOverlapPct = 0.5
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"

    fOverlapPct = 0.25
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"
    
    fOverlapPct = 0.05
    nRows           = AssignAreaDT(nValue, fOverlapPct)
    nRowsAssigned   = nRowsAssigned + nRows
    Print Time(24) & "     Assigned " & nRows & " records. Now assigned totally " & nRowsAssigned & " out of " & nAllRows & " records"

The function returns the number of records being assigned each time. I use this count to report out to the user the number of areas being assigned out of the total number of service areas.

You can see the output in the Message window under the Explorer window in the image below.

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