Three Measures in the Data Observatory Worth Exploring

We are making so many new measures available through the Data Observatory that we just had to highlight some interesting ones.

1. US population with a Master’s Degree

The supply and demand ratio for PhDs has gone crazy in recent years. At the same time there are alternative voices that are doubting the utility of higher education for many types of people. We’re not sure what sort of effect these types of stories will have over the long-run. But for sure, we can identify populations worth keeping an eye on. Here’s one for example, the population count that holds a Master’s Degree.

Grab some geometries

Here we will grab an area around Baltimore defined by a custom bounding box and insert them into an empty table we created in our CARTO dashboard. We are using the nice clipped geometries released previously.

INSERT INTO <my table name> (the_geom, name)
SELECT *
FROM OBS_GetBoundariesByGeometry(
  st_makeenvelope(
    -76.89468383789062,39.050118705681825,
    -75.99655151367188,39.53529197854428,
    4326),
  'us.census.tiger.block_group_clipped'
) As m(the_geom, geoid);

Add some data

Next, we’ll add a numeric column called, masters_degree and populate it with values from the Data Observatory. We can request the data in two ways, raw counts,

UPDATE <my table name>
SET masters_degree = OBS_GetMeasureByID(name,
  'us.census.acs.B15003023',
  'us.census.tiger.block_group_clipped',
  '2010 - 2014')

Or normalize it on the fly with Population over 25.

UPDATE <my table name>
SET masters_degree = OBS_GetMeasureByID(name,
  'us.census.acs.B15003023',
  'us.census.tiger.block_group_clipped',
  '2010 - 2014') /
NULLIF(OBS_GetMeasureByID(name,
  'us.census.acs.B15003001',
  'us.census.tiger.block_group_clipped',
  '2010 - 2014'), 0);

2. London area European population born outside the UK and Ireland

With the Brexit vote come and gone, a lot of people are digging into the data to better understand how it came to be and what impacts are coming. The Data Observatory is full of measures to help. Here is one that is particularly interesting, Population born in Europe but outside of the UK or Ireland.

Get the geoms

INSERT INTO <my table name> (the_geom, name)
SELECT *
FROM OBS_GetBoundariesByGeometry(
  st_makeenvelope(
    -0.5912017822265625,51.28854705640744,
    0.3069305419921875,51.678942096371244,
    4326),
  'uk.cdrc.the_geom'
) As m(the_geom, geoid);

Add some data

Here, we are normalizing the value on the fly with the total population in each area.

UPDATE <my table name>
SET born_europe_outside_uk_ireland = OBS_GetMeasureByID(name,
  'uk.ons.LC2205EW0091',
  'uk.cdrc.the_geom',
  '2011') /
NULLIF(OBS_GetMeasureByID(name,
  'uk.ons.LC2102EW0001',
  'uk.cdrc.the_geom',
  '2011'), 0);

3. Spaniards age 35 through 39

This is an interesting Measure for a number of reasons. Age data in Spain is of particular interest. While the life expectancy grows the predominant age groups get younger. If you look at the map above, you find that cities are young, while the surrounding country is aging. We like to share this map so that we can remind the CARTO’ers in Madrid to go visit their parents. If we had to pick one Measure from Spain though, the 35 through 39 year old group has got to be it. It’s one of the most populous age bins, but is slowly losing the lead to the 30-34 group. Go find out where these people are.

For Data Observatory users, this is how you’ll add a column that counts the number of Spanish people from 35 through 35 in an area (or a rate for a point).

Get the geoms

INSERT INTO <my table name> (the_geom, name)
SELECT *
FROM OBS_GetBoundariesByGeometry(
  st_makeenvelope(
    -3.8960266113281254,40.329795743702064,
    -3.44696044921875,40.56832825339617,
    4326),
  'es.ine.the_geom'
) As m(the_geom, geoid);

Add some data

Again, normalizing on the fly with the total population of the area.

UPDATE <my table name>
SET pop_35_39 = OBS_GetMeasureByID(name,
  'es.ine.pop_35_39',
  'es.ine.the_geom',
  '2015') /
NULLIF(OBS_GetMeasureByID(name,
  'es.ine.t1_1',
  'es.ine.the_geom',
  '2015'), 0);

More

Learn more about all the measures, boundaries, and discovery methods in the Data Observatory over on our documentation page or by watching our webinar!

Happy data analyzing!

About the author

Director of Business Architecture at CARTO.

More posts from Matt Forrest

Related Posts

Ready to optimize your territories with Location Intelligence?

Close circle icon

Contact us

Please fill out the below form and we'll be in touch real soon.