MapInfo Pro

MapInfo Monday: Modify the spatial object using the Update statement

  • 1.  MapInfo Monday: Modify the spatial object using the Update statement

    Posted 02-22-2021 08:41
    Happy #MapInfoMonday,

    Today we will look at how you can use the Update statement via the SQL Window to modify your existing spatial objects in a table.

    This can be very useful if you want to change all - or many - of the ​objects in your table.

    The Update Statement

    The syntax:
    Update Table
       Set column = expr [ , column = expr, ...]

      [ Where RowID = id | condition]
      [ DropIndex ( Auto | On | Off ) ]

    • table is the name of an open table.
    • column is the name of a column or OBJ for the spatial object.
    • expr is an expression to assign to a column.
    • id is the number of a row in the table.
    • condition is the expression a record must match in order to be updated
    With MapInfo Pro v2019, we added some improvements to the Update statement. You can now use the Where part of the statement to filter out only those records that you want to modify. Earlier you had to run a query first to filter out those records and then update the query instead of the base table.

    Using Update through the SQL Window

    To use the Update statement through the new SQL Window, open the SQL Window and from the Create New Script button.

    This will add the basic elements of the Update statement to the text field of the SQL Window as you can see above.

    Now you need to enter the name of the table you want to update and specify the OBJ column to be updated.

    Finally, you need to specify the expression returning a spatial object, that you want to use to alter the existing object. You can refer to the existing spatial object using OBJ. In the example below, I'm using the Centroid() function to extract the centroid point from the existing object and replacing the existing object with the centroid representation instead. Notice how I pass the OBJ value to the Centroid() function.

    A couple of examples

    A few examples and their results for the Parish dataset for Denmark.

    Centroid: The result is a set of points
    Update Parish
      Set Obj = Centroid(obj)

    MBR (Minimum Bounding Rectangle): The result is a set of rectangular polygons
    Update Parish
      Set OBJ = MBR(OBJ)

    Buffer around the Centroid: A set of circular polygons, size depends on the size of the existing parish polygon
    Update Parish
      Set OBJ = Buffer(Centroid(OBJ), 30, Area(OBJ, "sq km")/10, "km")

    Notice how I make the size of the buffer equal to the area divided by 10. You can also make the size depend on a value such as the number of inhabitants or similar.

    Some other useful spatial functions

    In the examples above I have shown you a couple of spatial functions that you can use when modifying existing spatial objects through the Update statement.

    Here is a list of other useful spatial functions:
    • Buffer/CartesianBuffer
    • Centroid
    • Combine
    • ConvertToPline
    • ConvertToRegion
    • CreateLine
    • CreatePoint
    • Erase
    • ExtractNodes
    • MBR
    • Offset/CartesianOffset
    • OffsetXY/CartesianOffsetXY
    • Overlap
    • Rotate
    • RotateAtPoint
    I hope you have found this post useful. If you have ideas for a #MapInfoMonday post, please leave a comment.

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