search

CartoDB / observatory-extension

BSD 3-Clause "New" or "Revised" License
6 stars 4 forks source link

Check why we don't get geometries for Canada #296

Closed ethervoid closed 7 years ago

ethervoid commented 7 years ago

We're using this query to get the available geometries for a bounding box (I've tried the entire polygon of Canada and multiple datasets with points only in Canada) and a numerator for Canada:

WITH _data as (SELECT * FROM OBS_GetAvailableGeometries((SELECT 
ST_SetSRID(ST_Extent(the_geom), 4326) FROM (SELECT * FROM world_borders WHERE iso3 = 
'CAN') q), NULL, 'ca.statcan.cols_nhs.t029c009_t', NULL, NULL) denoms) ORDER BY numgeoms 
DESC) SELECT DISTINCT on (geom_id) geom_id FROM _data WHERE geom_id like 'ca.%';

notice that I've removed the check for valid_numer so it's returning all the geometries and we don't have any geometry for Canada

The results is always 0 despite we have geometries for Canada measures:

dataservices_db=# select geom_id,geom_tags from obs_meta_geom where 
'ca.statcan.cols_nhs.t029c009_t' = ANY(numers);                           
geom_id       |                                geom_tags                                                                          
--------------------+-------------------------------------------------------------------------                                           
ca.statcan.geo.cd_ | {"section/tags.ca": "Canada", "subsection/tags.boundary": "Boundaries"}                                            
ca.statcan.geo.ct_ | {"section/tags.ca": "Canada", "subsection/tags.boundary": "Boundaries"}                                            
ca.statcan.geo.pr_ | {"section/tags.ca": "Canada", "subsection/tags.boundary": "Boundaries"}                                            
ca.statcan.geo.csd | {"section/tags.ca": "Canada", "subsection/tags.boundary": "Boundaries"}                                           
(4 rows)