search

CartoDB / bigmetadata

BSD 3-Clause "New" or "Revised" License
43 stars 11 forks source link

Adding CA FSAs #550

Closed antoniocarlon closed 6 years ago

antoniocarlon commented 6 years ago

Adding Canada Forward Sortation Areas Closes https://github.com/CartoDB/bigmetadata/issues/548

antoniocarlon commented 6 years ago

Notes for the acceptance: The Census data has been fully tested locally and I wouldn't expect surprises (everything should run smoothly).

I haven't completely tested the NHS (interpolated) survey because it takes a long time to run. For the acceptance of the FSA NHS data, the following queries can be used to check that the values are correct:

--Get value from the FSA NHS tables
SELECT t001c001_t
  FROM observatory.obs_2f6e51358073ffce7b7c28722479cd1027dfbbd4
 WHERE geo_code = 'J0X';

--Calculate value on the fly for FSA (interpolated from CSD)
SELECT round(sum(t001c001_t * ST_Area(ST_Intersection(geo_csd.the_geom, geo_fsa.the_geom)) / Nullif(ST_Area(geo_csd.the_geom), 0))::numeric, 2)
  FROM observatory.obs_7c68559f954eab631eec57b833e351dea9753f67 data_csd,
       observatory.obs_0a6e93e146dd1bf034c88aa218bde8da2aef916b geo_fsa,
       observatory.obs_eec7efe6673a21367062128ab89c2fc30ade88c4 geo_csd
 WHERE ST_Intersects(geo_csd.the_geom, geo_fsa.the_geom)
   AND data_csd.geo_code = geo_csd.geom_id
   AND geo_fsa.geom_id = 'J0X';
antoniocarlon commented 6 years ago

I have executed the generation of FSAs in staging and everything went fine. It can be considered a successful acceptance :+1: