There's more to experience when you log in!
Welcome to yet another #MapInfoMonday article!
Today, we will look at one way to copy data from a point to another point nearby.
MapInfo Pro allows you to update one table with values from another table when you can specify a join between these two tables using the Update Column dialog. This join can be an attribute join or a spatial join. These joins are however somewhat limited compared to the full amount of join options you have these days with MapInfo Pro. You can for example not use a join specifying that two records, for example, points, should be within a distance of say 1 meter from eachother.
In this article, I will show you how you join the two tables using the Select statement and they use Update Column to update one of the tables with data from the other table using the result of the join query.
As you can see in the map above, the orange measurement points are not located exactly on top of the existing black address points. Also, these orange points are not the same distance from the addresses so we need to use some sort of tolerance to find the nearest one.
The idea here is to calculate the distance between the measurement points and the addresses and only keep those where the distance is under say 1 meter. This will probably not match up all measurement points with an address but at least some. We can then extend the distance and slowly work our way through the measurement points until all have been assigned an address.
To avoid including a measurement point that already has been assigned and address we add an additional condition to only use the measurement points where the FullAddress column is empty, that is no address has been assigned to it.
Select t1.*, t2.AddressPostalCode
From Address_Measurements As "t1"
, Addresses As "t2" Cross Join
Where ObjectDistance(t1.obj, t2.obj, "m") < 1
And t1.FullAddress = ""
Please note that with a Cross Join you are joining each record in the first table to each record in the second table and then you use the conditions in the Where part of the Select statement to limit the records returned. This can be slow especially if you have large tables. In my example, the address table holds around 14,000 records, and the measurement table holds around 200 records. This results in a total of 2,800,000 that the conditions need to be applied to. Further down, I'll show how you can limit the number of addresses to use.
Once the query has returned some records I can use the Update statement to copy the address string from the AddressPostalCode to the FullAddress field in the query result.
You can do this via the Update Column dialog, or use the SQL Window as I do below using this Update statement:
Set FullAddress = AddressPostalCode
You will now have to repeat this process until all measurements have been assigned the full address from the nearest address.
For each repeat, you make the distance bigger. You can either add 1 to the current distance or maybe go from 1 to 2, to 5, and to 10. The idea is to try to avoid finding multiple addresses for a single measurement.
If you feel comfortable about the process, you can also consider merging the Select and Update statements into one script. This will make it easier to repeat the process as you can stick to using one script.
Note that a MapBasic script in the SQL Window can consist of multiple statements that are executed in order.
Please note that you may see an error in the Output Area of the SQL Window. This will happen if the query doesn't find any matches which can happen if this distance is too small or if all the measurements have been assigned a full address.
As I mentioned earlier, the Cross Join may take time to run if you are comparing a large number of records.
The most simple way to speed up the Cross Join query is to limit the number of records in the tables.
In this example, we can limit the number of addresses in the Cross Join by only using those that are close to the measurement points. This can easily be done using a Sub Select query where we only return the addresses that are within a combined buffer around all the measurement points.
Here is an example of such a Select statement. The statement uses a sub-select to create an aggregated buffer around all the measurement points. This aggregated buffer is then used to select the addresses that are within 50 meters of the measurement points.
Where OBJ Within
(Select AggregateBuffer(obj, 12, 50, "m") From Address_Measurements)
As you can see in the image above, this query runs quite fast. Less than half a second. In the map, you can see the purple points which are the addresses that are within the 50-meter buffer from the measurement points. I have used a quite large buffer. You can certainly make this closer to the actual distance you are using in your Cross Join Select statement.
You will also have to change the Select statement to now use your temporary query instead of the Address table. As I am using a table alias for my tables, all I have to change is to change the name of the second table in the From part of the Select statement.
This small optimization improves the performance from 105 seconds to around 10 seconds.
If you want, you can even add the Sub Select query to your combined script.
I hope you have found this article useful. There are other ways to do this. You can use the Distance Calculator to find the nearest address for each measurement point and you can create larger and larger buffers around your measurement points and use a standard spatial operator to match the address points to the measurement points. As you probably know, MapInfo Pro is quite flexible and allows you to do things in multiple ways.