Archive for the 'MapBasic' Category

I do from time to time get this question:

I have two versions of the same table. How can I see whether there has been made changes to the objects for the matching records in these two tables?

The answer is a bit tricky, because there is no straight forward answer.

First, let me say that you can’t compare two spatial objects using the normal operators such as = and <> which of course would have been straight forward. So we have to come up with an alternative.

Comparing spatial values

The procedure will be the same for all object types, but depending on the object type you will have to compare fewer or more values to see if the spatial objects are identical.

What we actually will be doing, is comparing a set of spatial values from each spatial object to see if the object has been changed or are alike. These spatial values could be:

• The coordinates of the centroid of the spatial objekt
• The length or perimeter of the spatial object
• The area of the spatial object
• Number of nodes in the spatial object
• Number of segments in the spatial object
• The coordinates of one or more nodes of the spatial object

Comparing coordinates

When comparing coordinates it is advices to introduce a tolerance. How little or how much must a coordinate have been moved in order no longer to be the same? For this purpose you can do one of two things:

• Compare the difference between the coordinate values to a set value in stead of comparing the coordinate values themselves
• Round the coordinate values before comparing them

If we want to compare the difference between the coordinate values, we will have to compare each coordinate value separately.

If we can settle with compare a rounded value, we can integrate this values with the other spatial values and in this manner create a combined spatial finger print of the spatial object.

Common ID in both tables

I all cases this procedure does require that an ID exists in both tables. We need to make sure that we can join the records in the two tables using this ID in a SQL query.

We need to know that record with ID 111111 in the first tables matches the record with ID 111111 in the second table. The ID have to be static.

The spatial finger print

A spatial finger print is a set of spatial values that combined uniquely identifies a specific spatial object. The spatial finger print will be combined by a set of values determined by the object types:

• A point object: Here we only need to look at the coordinate of the spatial object
• A line object: Here we must look at the begin and end coordinates of the spatial object
• A polyline object: We will have to look at the length of the the spatial object, the coordinates of the start and end nodes, the coordinate of the centroid of the spatial object, the number of nodes and segments in the spatial object. We could also consider adding the coordinates of the mid node of the spatial object to the spatial finger print
• A region/polygon object: Here we need the perimeter as well as the area of the spatial object, coordinates of the first node and coordiantes of the centroid of the spatial object and the number of segments and the number of nodes. Again we can also consider adding the coordinates of the mid node of the spatial object to the spatial finger print

MapBasic functions to be used

Below you will find a list of the MapBasic functions that we will be using. Read more about each function in the MapBasic Reference Guide or in the MapBasic help file.

• CentroidX() and CentroidY(): returns the coordinate of the centroid
• ObjectGeography(): can amongst other things return the begin and end coordinates of a line object
• ObjectInfo(): can return specific details about a spatial object, such as the number of segments, the number of nodes and the number of nodes in a specific segment
• ObjectNodeX() and ObjectNodeY(): returns coordinates of a specific node
• Round(): Rounds floating values
• CartesianObjectLen(): returns the (cartesian) length of a spatial object
• CartesianArea(): returns the (cartesian) area of a spatial object
• CartesianPerimeter(): returns the (cartesian) perimeter of a spatial object

The spatial finger print using SQL

Let’s see how the spatial finger print will look using a SQL or MapBasic expression.

Note that I’m using “#” to separate the individual values.

Point

Line

Polyline

Region/polygon

The expressions above can be used to update a column with the spatial finger print and later use this column to compare objects across tables.
However do note that you might extent the magic limit of 254 characters which is the limit of a Char columns in MapInfo Professional.

Combining these expressions using a SQL query
If you want to use the expressions from above to compare polylines or regions, you will have to use the MapBasic window. The dialog SQL Select is limited to approximately 512 characters and our query will be around 1600 characters – depending on the name of your tables.

The query for regions would look like this:

In the MapBasic window the query will look this:

Do remember to highlight the entire query in the MapBasic window before hitting the Enter key to run the query. Otherwise you will only execute the single line in which your cursor currently is placed. In the example above the query is separated into a number of lines in order to improve the overview.

The query above will be a lot more simple for points and lines, but almost the same for polylines.

Do note that the query above only is comparing the spatial part of the spatial objects – it doesn’t consider the styling of the objects, which also might have been changed.

And finally I want to let you know that in a number of rare specific cases this method will not find the changes. This could be a number of nodes in the objects have been changed and this didn’t have any effect on the area or the perimeter of the spatial object.

Peter

PS: Note that I’m using “not … = …” i stead of “… <> …” in the text version of the query.