MapInfo Pro

MapInfo Monday: Update points with Data from nearby Points

  • 1.  MapInfo Monday: Update points with Data from nearby Points

    Posted 09-20-2021 09:22

    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.

    The situation

    Imagine that you have received a data file with some measurements for a number of addresses in your area. Unfortunately, each measurement has only been stored with a location and not the address. You now want to take the address from the nearest address point and update the measurements with this address in the column FullAddress.

    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.

    Joining the Measurement Points with the Addresses

    Open the SQL Window as we will use this to build our Select statement.

    Now enter this query:
    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 = ""
    Into Selection

    The Select statement will:
    • return a query with all the columns from the measurement table and the full address from the address table
    • where the distance between the two objects is less than 1 meter
    • and the FullAddress value of the measurements is empty.
    Also notice that I have specified the use of the Cross Join keywords as these join conditions aren't supported for a simple join query.

    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.

    Updating the measurement table with the full address

    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:

    Update Selection
    Set FullAddress = AddressPostalCode

    If you browse the measurement table, as I have done in the image below, you can see that the records that met the condition in your Select statement has been updated with the full address. The other records have not been updated yet as the distance to the nearest address is further than 1 meter.

    Repeat the process

    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.

    Select and Update in one step

    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.

    Speeding up the Cross Join

    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.

    Select *
    From Addresses
    Where OBJ Within
       (Select AggregateBuffer(obj, 12, 50, "m") From Address_Measurements)
    Into AddressesNearby

    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.

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