baltimore economic data through the data observatory

README.md

Baltimore Data through the Data Observatory

See map

Step 1

Create an empty table, rename it to baltimore_economic_data

Get Boundaries, filter by Baltimore's FIPS (24510)

INSERT INTO baltimore_economic_data(the_geom, name)
SELECT *
FROM OBS_GetBoundariesByPointAndRadius(
  CDB_LatLng(39.2904,-76.6122),
  25000 * 1.609,
  'us.census.tiger.census_tract_clipped'
) As m(the_geom, geoid)
WHERE geoid LIKE '24510%'

Step 2

Search for 'gini' using the function OBS_Search() like this:

SELECT *
FROM OBS_Search('gini')

Copy the id of the index you want. In our case, gini corresponds to us.census.acs.B19083001.

Step 3

Create a new NUMERIC column called gini_index, then run this query to fill it with GINI measures from the US Census:

UPDATE baltimore_economic_data
SET gini_index = OBS_GetMeasure(ST_PointOnSurface(the_geom), 'us.census.acs.B19083001','area','us.census.tiger.census_tract')

Step 4

Adding more by searching for income:

SELECT *
FROM OBS_Search('income')

I'll choose the following:

Add new NUMERIC column called per_capita_income:

UPDATE baltimore_economic_data
SET per_capita_income = OBS_GetMeasure(ST_PointOnSurface(the_geom), 'us.census.acs.B19301001','area','us.census.tiger.census_tract')

Add new NUMERIC column called percent_income_on_rent:

UPDATE baltimore_economic_data
SET percent_income_on_rent = OBS_GetMeasure(ST_PointOnSurface(the_geom), 'us.census.acs.B25071001','area','us.census.tiger.census_tract')

Add new NUMERIC column called median_household_income:

UPDATE baltimore_economic_data
SET median_household_income = OBS_GetMeasure(ST_PointOnSurface(the_geom), 'us.census.acs.B19013001','area','us.census.tiger.census_tract')