MapInfo Pro Developers User Group

Expand all | Collapse all

update objects from one table to another table

  • 1.  update objects from one table to another table

    Posted 12-10-2019 13:01
    Hi! I am trying to move from MapInfo 12.5 to 17.0.4. The code I have from version 12.5 ran faster than running on version 17. I am trying to find out why and if anyone can help me. It takes 4 seconds to run on 12.5 and 1 minute and 6 seconds to run on 17.0.4

    Sub main
    OnError Goto DisplayError
       Set Window Message
               Font ("Courier New", 0, 10, BLACK, WHITE)
               Position (0.1, 1.25)
               Width 7.0 Height 4.0

    Print Chr$(12)

       Dim lcRecType As String
       Dim lcID As String
       Dim lnID As Integer
       Dim lnRecordNo As Integer
       Dim loObject As Object
       Dim lnDelay As Integer
       lnDelay = 1000

    Open Table "WebDataInfo.TAB" As DataInfo ReadOnlyOpen Table "WebDataInfo.TAB" As DataInfo ReadOnly
    Open Table "MapIDsAddressInfo.TAB"

    ' This is equivalent To Select Distinct JobRadius
    Select RecType From MapIDsAddressInfo Group By RecType Into XRecType
       Dim lnRowCount As Integer
       lnRowCount = TableInfo(XRecType, TAB_INFO_NROWS)

       Fetch First From XRecType
       Do While Not EOT(XRecType)
           lcRecType = XRecType.RecType

          Print "Merging the rectype of " + lcRecType + " (" + Str$(XRecType.RowID) + " out of " + Str$(lnRowCount) + "). . . ."

          Select TabsTable From DataInfo Where lcRecType = DataInfo.Acronym Into XDataInfo
          If (TableInfo(XDataInfo, TAB_INFO_NROWS) > 0) Then
               Dim lcTable As String
               Dim lcIDField As Alias
               Dim lcIDFieldTemp As String

             lcTable = AllTrim(XDataInfo.TabsTable)
             lcIDFieldTemp = AllTrim(DataInfo.TabsIdfld)
             lcIDField = lcIDFieldTemp

             Print "(Tabs table is " + lcTable + ")"
             Open Table lcTable As GeoTable

             If (Not ColumnInfo("GeoTable", lcIDFieldTemp, COL_INFO_INDEXED)) Then
                Note "Creating Unique ID index for " + lcTable + " (" + lcIDFieldTemp + ") as one did not exist."
                Create Index On GeoTable(lcIDFieldTemp)
              End If

              Dim llCharacterID As Logical
              llCharacterID = (ColumnInfo("GeoTable", lcIDFieldTemp, COL_INFO_TYPE) = COL_TYPE_CHAR)

               Select * From MapIDsAddressInfo Where (lcRecType = MapIDsAddressInfo.RecType) And Not Object Into XMapID
               Print Str$(TableInfo(XMapID, TAB_INFO_NROWS)) + " records need spatial objects"

               If (llCharacterID) Then
                    Fetch First From XMapID
                    Do While Not EOT(XMapID)
                      lcID = XMapID.ID
                      lnRecordNo = XMapID.RowID

                     Select Object From GeoTable Where (lcIDField = lcID) And Object Into XGeoTable

                     If (TableInfo(XGeoTable, TAB_INFO_NROWS) > 0) Then
                            Fetch First From XGeoTable
                            loObject = XGeoTable.Object
                            Update XMapID
                                 Set Object = loObject Where RowID = lnRecordNo
                     End If
                      Fetch Next From XMapID
                   Loop
              Else
                  Fetch First From XMapID
                    Do While Not EOT(XMapID)
                      lcID = XMapID.ID
                      lnRecordNo = XMapID.RowID

                     Select Object From GeoTable Where (lcIDField = lcID) And Object Into XGeoTable

                     If (TableInfo(XGeoTable, TAB_INFO_NROWS) > 0) Then
                            Fetch First From XGeoTable
                            loObject = XGeoTable.Object
                            Update XMapID
                                 Set Object = loObject Where RowID = lnRecordNo
                     End If
                      Fetch Next From XMapID
                   Loop

          End If
      Commit Table XMapID
          Print ""
          Fetch Next From XRecType
      Loop
       Select * From MapIDsAddressInfo Where Object Into XYMapIDsUpdate
       Update XYMapIDsUpdate
               Set X = CentroidX(Object), Y = CentroidY(Object)
     Commit Table XYMapIDsUpdate
      Close Window Message
    Exit Sub
    DisplayError: Note "Error " + Error$()
    Exit SubEnd Sub


    ------------------------------
    Michel Li
    Programmer
    GEOSEARCH LLP
    Austin TX
    ------------------------------


  • 2.  RE: update objects from one table to another table

    Posted 12-12-2019 09:38
      |   view attached

    Hi Michel

    We have seen that creating multiple queries inside a MapBasic application can slow down the program quite a lot.

    The workaround for this is to make sure you do your querying in a way that has the least influence on the interface. You do this by adding the NoSelect and Hide keyword to your query statement. This is only possible if you select into a named query which you already do. The Hide keyword is no in MapBasic 17.0.

    So your query would change from this:

    Select RecType From MapIDsAddressInfo
       Group By RecType
       Into XRecType

    To this:
    Select RecType From MapIDsAddressInfo
       Group By RecType
       Into XRecType NoSelect Hide

    I would also recommend that you close your queries when you are done using these.

    You can also take advantage of the Hide keyword when opening table during your loop that you need in your processing:

    Open Table lcTable As GeoTable Hide

    This will again hide the table from the end-user in the user interface. But remember to close the table during your loop.

    There is another performance improvement that you might benefit from trying out:

    Set Table MapIDsAddressInfo ReDraw Off

    This turns off any redraw of the table until you turn the redraw back on:

    Set Table MapIDsAddressInfo ReDraw On

    I have attached an example of your tool where I have made these small adjustments. But have a look at it and check if it still works as I haven't been able to test it.

    For your information, there are some other changes coming in MapBasic v2019 that you can take advantage of in your application

    Where Condition for Update statements

    Your statements can be changed from a query and an update statement to a single update statement.

    Now you would use these statements:

    Select * From MapIDsAddressInfo
       Where Object
       Into XYMapIDsUpdate NoSelect Hide
    Update XYMapIDsUpdate
       Set X = CentroidX(Object), Y = CentroidY(Object)

    In MapBasic v2019, you can do this:

    Update MapIDsAddressInfo
       Set X = CentroidX(Object), Y = CentroidY(Object)
       Where Object

    Let us know if these suggestions helps speed up the processing

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------

    Attachment(s)

    zip
    TableLoop v2019.zip   1K 1 version


  • 3.  RE: update objects from one table to another table

    Posted 12-23-2019 12:24
    Thanks Peter. It works much faster right now. Also, I have another question you might be able to help me. I am trying to create mappershorcut by right clicking and see the shortcut but it seems it does not work in the newer version. Here is the code:

    ' Recreate right click Map Menu '
    Create Menu "MapperShortcut" ID 17 As "(-",
    "Open Doq's For Boundary" Calling OPEN_DOQ_FOR_BND,
    "Open Hypso For Boundary" Calling OPEN_HYP_FOR_BND,
    "Open Drg's For Boundary" Calling OPEN_DRG_FOR_BND,
    "Open Og Layers For Boundary" Calling OPEN_OGS_FOR_BND,
    "Open Fema For Boundary" Calling OPEN_FEMA_FOR_BND,
    "Open Nwi For Boundary" Calling OPEN_NWI_FOR_BND,
    "Open Soil For Boundary" Calling OPEN_SOIL_FOR_BND,
    "Open Geology For Boundary" Calling OPEN_GEO_FOR_BND,
    "(-",
    "Clear Cosmetic Layer" Calling M_MAP_CLEAR_COSMETIC,
    "(-",
    "Previous View" Calling M_MAP_PREVIOUS,
    "View Entire Layer..." Calling M_MAP_ENTIRE_LAYER

    ------------------------------
    Michel Li
    Programmer
    GEOSEARCH LLP
    Austin TX
    ------------------------------



  • 4.  RE: update objects from one table to another table

    Posted 01-08-2020 09:36
    Hi Michel

    I posted an answer to this context menu question under a different thread. Find it here>>>.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------