MapInfo Pro

 View Only

MapInfo Monday: Aggregating Data up into Polygons

  • 1.  MapInfo Monday: Aggregating Data up into Polygons

    Employee
    Posted 09-06-2021 08:59
    Edited by Peter Møller 09-06-2021 10:05
    Happy #MapInfoMonday,

    In this post, I'll show you how you can aggregate data from points or smaller objects up into larger polygons. In this post, I'll use hexagons as my "larger polygons" but you can use any type of polygon data. You could use postal zones, administrative boundaries, sales districts, flood zone districts, or any other polygon-based dataset you may have.

    A few weeks back we discussed creating hexagons using the Uber H3 library. As you may recall, a couple of the engineers (Bob and Anshul) from the MapInfo Pro engineering team created a small Python add-in that could create Uber H3 hexagons covering polygons in a MapInfo table as long as the table was using Latitude/Longitude WGS84 as it's coordinate system.

    We have now published this tool to the MapInfo Marketplace for you to access and install it very easily from within MapInfo Pro. The tool requires MapInfo Pro v2019.3.​ From the MapInfo Marketplace inside MapInfo Pro simply click the Install Product button to get the tool downloaded, installed, and registered within MapInfo Pro.

    After the tool has been downloaded, you can access the tool from the Tools window. Double-click on the tool to run the Uber H3 Hexagon creation process.

    You will be asked to select the table to create hexagons around and you will be asked to set the name of the output table and the Uber H3 level. For my area, I have decided to create three levels of hexagons: Level 6, 7, and 8. This table gives you an indication of the size of the hexagons for each level.

    Remember to create your "coverage area" a bit bigger than your real area of interest. The tool only creates the H3 hexagons when the centroid of the hexagon is inside your "coverage area".

    My final result looks like this I have made the width of the borderline thinner for the smaller hexagons. The thick red dashed line is my "coverage area"

    Let's add a couple of additional columns to the hexagons tables that we can update with aggregated values from other layers. In this example, we are adding three additional columns but I'll only aggregate data into one of these columns.

    If you have made your map look nice with your polygon table and other tables, now is a good time to save your changes into a workspace. When you start modifying the table structure of a table, the table will get removed from any windows it may appear in during this process. Saving your work to a workspace makes it easy to restore your work afterward.

    Right-click on one of the hexagon tables in the Table List and select Table Structure. I use the Add Field button to add three new columns of type Integer. We will only be using the AddressCount column in this example. Click OK to add the columns to the table.

    As I need to reproduce this step for three tables, I opened the MapBasic window before altering the table structure. This will capture the statement needed to alter the table. You can then very easily modify the statement to refer to another table and run the statement again by hitting the Enter key on your keyboard.

    The next step is to aggregate data from my addresses up into the hexagons that contain the addresses. With MapInfo Pro, this can easily be done using Update Column that you can find on the SQL dropdown on a number of the tabs. You can also find it directly on the Table tab.

    Select your table with polygons. In my case that is one of the hexagon tables. The column to update is the column we added to the table, AddressCount. You want to get the value from another table, in our case the table with addresses and finally, you want to aggregate the data based on the number of points within each hexagon. Use Count from the Calculate dropdown for this.

    Also, click the Join... button to make sure the join condition is correct. We want the objects from the table with addresses to be within the polygons from the hexagon table.

    Note that we hare do a basic count of the points within each polygon. You can also use one of the other available aggregations such as summarizing the values in a column or calculating the average value from a column. This all depends on your goal and your data.

    And again, the MapBasic window can make it easy for you to do this for multiple tables. Below you can see the recorded statement used to update the column AddressCount with the number of addresses within each hexagon/polygon. Just change the name of the first table, and you can run the statement for another table.

    Finally, we want to create a theme that highlights the number of addresses within each hexagon. From the Map tab, select Add Theme.

    A small wizard will now help you create a theme based on your polygon table. I select a Ranges theme and from the template list, I select the Region Ranges Solid Purple, Dark-Light. There are many templates to choose from. Make sure you select one that is for Regions (polygons).

    In the next step, we select the table and the column. This is the table we have aggregated data into and the column in this table. I also check the option Ignore Zeroes or Blanks. This will leave out the polygons where the value is zero.

    Below you can see the result of the first thematic based on the smallest hexagon polygons, level 8.

    I do a similar thematic for the other two levels and end up with three thematic layers. For each of these thematic layers, I set a zoom interval so that the smaller polygons are visible when I'm zoomed into the map and the bigger hexagons appear as I zoom out.

    This results in the following three maps. The black dots, you see in the maps are the addresses. Also, notice that the hexagons that contain no addresses aren't visible on the map as I chose to ignore these.

    Zoom out with a map zoom (map width) of more than 50 km.

    A map zoom between 15 and 50 km

    Zoomed in with a map zoom of less than 15 km.

    I hope this has given you an overview of how to aggregate your data from point datasets up into polygons to gain a better understanding of the distribution of your samples.

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