MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Aggregating Population Circles

  • 1.  MapInfo Monday: Aggregating Population Circles

    Employee
    Posted an hour ago

    Over the last few weeks, we have been diving into the world of Population Circles and Population Drive Regions, creating maps showing how large an area is required to get to say 50,000 people around some locations.

    We have done this in a manual way meaning that we end up with 10, 20 or more polygons around each location.

    image
    We then use the Layer Filter option to only show the polygons where the population lies in a suitable range.
    Of course this means that sometimes, we will end up with multiple polygons for one location as there are multiple polygons where the population is within the desired range. But if you just want to display these, we can give them a style where you don't notice there are multiple polygons.
    If you click on some of these locations, you will see that this is the case.
    In the map below, I have made only the Population Drive Distance Regions selectable. I have clicked on one of the locations with the Info tool, and in the Info window, you can see that for that location, 3 Population Drive Distance Regions have a population between 40,000 and 60,000.
    image
    We can improve this by using a SQL query instead of the Layer Filter option.
    Let me show you.

    Aggregating Query

    Instead of using the Layer Filter option to only show certain records, I'll now show you how to run a query that will merge the polygons so that you end up with just one record for each location.
    In this first map, you can see the result of a query that aggregates all polygons from the Drive Distance table.
    Select dd.regionid
, dd.Name
, Max(dd.value) As "value"
, dd.unit
, dd.type
, Max(dd.Population) As "Population"
, AggregateCombine(dd.obj)
From Sites_DD As "dd"
Group By dd.ID
Into Selection

    Notice that a few of the columns are aggregated using the Max() aggregate. That is to make sure we keep the highest value. This goes for the value, that holds the drive distance, and the Population column.

    For the OBJ column, or the polygons, I use the aggregate AggregateCombine() to merge the spatial polygons into one.

    image
    Now we aren't interested in including all the records in our result. We need to add a condition to the query to only include the records with a population between 40,000 and 60,000.
    Select dd.regionid
, dd.Name
, Max(dd.value) As "value"
, dd.unit
, dd.type
, Max(dd.Population) As "Population"
, AggregateCombine(dd.obj)
From Sites_DD As "dd"
Where dd.Population Between 40000 and 60000
Group By dd.ID
Into Selection

    As you can see, the only difference from the earlier query is the added Where condition.

    The resulting map looks like this:

    image
    And as there is now only one record per location, it's possible to label these with information about their size and population.
    image
    I did use the Expression from the Label Using dropdown on the Labels context tab.
    image
    And here you see the full label expression:
    Name+Chr$(10)+"Distance: "+Format$(value, "#,#.0")+" "+unit+Chr$(10)+" Population: "+FormatNumber$(Population)

    This process can be used to aggregate objects for locations that overlap each other and get in the way for proper labelling and mapping.

    So far, I have just used the result as a temporary query. I can also save this into a native tab file.

    I hope you found this helpful.



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