Data Observatory Webinar snippets

README.md

Data From Nothing

Gathering data from nothing

1. Get boundaries, insert into appropriate table

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

2. Find measures of interest

SELECT * FROM OBS_Search('inequality')

Copy the measure id from this search for other use.

Or the Observatory Catalog: https://cartodb.github.io/bigmetadata/observatory.pdf

3. Create New Column called gini, set type to numeric

4. Get measures

UPDATE baltimore_webinar_demo
SET gini = OBS_GetMeasureById(name, 'us.census.acs.B19083001', 'us.census.tiger.census_tract')

5. Explore more

SELECT * FROM OBS_Search('income')

6. Repeat until you have the data you need

Augmentation Section

ALTER TABLE store_performance_data ADD COLUMN pop_density NUMERIC;

UPDATE evisage.store_performance_data
 SET pop_density = OBS_GetMeasure(the_geom, 'us.census.acs.B01003001')

---

INSERT INTO blocks (the_geom, geom_refs)
 SELECT the_geom, geom_ref FROM (
   SELECT (OBS_GetBoundariesByGeometry(
            ST_Envelope(ST_Collect(the_geom)),
           'us.census.tiger.block_group_clipped')).*
   FROM andrew.store_performance_data) as results
 GROUP BY the_geom

---

ALTER TABLE blocks ADD COLUMN income NUMERIC;

with income_t as(
select geom_refs, OBS_GetMeasureById(geom_refs,  'us.census.acs.B19301001','us.census.tiger.block_group') income
from blocks
)

update blocks set income= income_t.income::numeric
from income_t
where blocks.geom_refs = income_t.geom_refs

---

with typeq as (
select cartodb_id, 
OBS_GetCategory(
  the_geom,
  'us.census.spielman_singleton_segments.X55'
)  as type
from store_performance_data
)

UPDATE store_performance_data SET neighborhood_type = typeq.type 
from typeq
where typeq.cartodb_id = store_performance_data.cartodb_id

---

INSERT into walking_dist (the_geom, data_range)
  SELECT the_geom, data_range FROM (
   SELECT (CDB_Isochrone(the_geom, 'walk', ARRAY[1200]::integer[])).*
   FROM envisage.store_performance_data
  ) as results

---