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.

##_INIT_REPLACE_ME_PRE_##

INSERT INTO  (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);
##_END_REPLACE_ME_PRE_## 

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

##_INIT_REPLACE_ME_PRE_##

UPDATE 
SET masters_degree = OBS_GetMeasureByID(name 
  'us.census.acs.B15003023' 
  'us.census.tiger.block_group_clipped' 
  '2010 - 2014')
##_END_REPLACE_ME_PRE_## 

Or normalize it on the fly with Population over 25.

##_INIT_REPLACE_ME_PRE_##

UPDATE 
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);
##_END_REPLACE_ME_PRE_## 

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

##_INIT_REPLACE_ME_PRE_##

INSERT INTO  (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);
##_END_REPLACE_ME_PRE_## 

Add some data

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

##_INIT_REPLACE_ME_PRE_##

UPDATE 
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);
##_END_REPLACE_ME_PRE_## 

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

##_INIT_REPLACE_ME_PRE_##

INSERT INTO  (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);
##_END_REPLACE_ME_PRE_## 

Add some data

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

##_INIT_REPLACE_ME_PRE_##

UPDATE 
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);
##_END_REPLACE_ME_PRE_## 

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!