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
------------------------------