DO Madrid

README.md

Data Observatory

• Create an empty table with the editor: do_madrid
• Remove the description and name field an create a geo_id field of string type
• Execute this query to get the boundaries:

INSERT INTO do_madrid (the_geom, geo_id)
  SELECT *
  FROM OBS_GetBoundariesByGeometry(
    st_makeenvelope(-4.18,40.13,-3.29,40.72, 4326),
    'es.cnig.muni'
);

• Get the total population:

UPDATE do_madrid
  SET total_pop =
    ROUND(
      OBS_GetMeasure(
        the_geom,
        'es.ine.t1_1'
      )
    );

• Segmented population:

UPDATE do_madrid
  SET pop_20_40 =
    ROUND(
      OBS_GetMeasure(
        the_geom,
        'es.ine.pop_20_24'
      ) +
      OBS_GetMeasure(
        the_geom,
        'es.ine.pop_25_29'
      ) +
      OBS_GetMeasure(
        the_geom,
        'es.ine.pop_30_34'
      ) +
      OBS_GetMeasure(
        the_geom,
        'es.ine.pop_35_39'
      )
    ) ;

• Get the supermarkets and the other grocieries

UPDATE do_madrid
  SET supermarkets =
    OBS_GetMeasureById(
      geo_id,
      'es.lacaixa.supermarkets',
      'es.cnig.muni'
  );

UPDATE do_madrid
  SET supermarkets =
    OBS_GetMeasureById(
      geo_id,
      'es.lacaixa.supermarkets',
      'es.cnig.muni'
  );

• Same with other measures: malls, automobiles, etc.

• Calculated normalized columns using this kind of queries:

alter table do_madrid
add column supermarkets_km numeric ;

update do_madrid
set supermarkets_km =
ROUND(
  (supermarkets/ST_Area(the_geom::geography)::numeric * 10e6)::numeric,
  1
);

• Created a dashboard to render by default automobiles per person but allow to filter and change the simbology by other metrics.