DO analysis fails with multiple columns with the same numerator

[javitonino]

STR:

• Add a DO analysis
• Select total population, not normalized
• Add a new measurement
• Select total population, normalized by area

You get an error like:
column _data.total_pop_2011 does not exist

The analysis definition is

numerators | […]
0 | uk.ons.LC2102EW0001
1 | uk.ons.LC2102EW0001
normalizations | […]
0 | prenormalized
1 | area
denominators | […]
0 | null
1 | null
geom_ids | […]
0 | uk.cdrc.the_geom
1 | uk.cdrc.the_geom
numerator_timespans | […]
0 | 2011
1 | 2011
column_names | […]
0 | total_pop_2011
1 | total_pop_per_sq_km_2011

[antoniocarlon]

(Followup)

Querying the first measurement:

http://<mymap>/api/v2/sql?q=SELECT%20OBS_GetMeta((SELECT%20ST_SetSRID(ST_Extent(the_geom)%2C%204326)%20FROM%20(SELECT%20*%20FROM%20untitled_table_4)%20q)%2C%20%27%5B%7B%22numer_id%22%3A%22es.ine.t1_1%22%2C%22numer_timespan%22%3A%222015%22%2C%22normalization%22%3A%22prenormalized%22%7D%5D%27)&api_key=<my_api_key>

returns

{"rows":[{"obs_getmeta":[{"id":1,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"prenormalized","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"}]}],"time":0.169,"fields":{"obs_getmeta":{"type":"JSON"}},"total_rows":1}

And executing the query:

SELECT * FROM cdb_observatory.OBS_GetData(ARRAY[(ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-4, 41), 4326), 3857), 250), 4326), 1)::geomval], '[{"id":1,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"prenormalized","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"}]');

returns the expected value:

'[{"value":3.38578494040731}]'

Querying the second measurement:

http://<mymap>/api/v2/sql?q=SELECT%20OBS_GetMeta((SELECT%20ST_SetSRID(ST_Extent(the_geom)%2C%204326)%20FROM%20(SELECT%20*%20FROM%20untitled_table_4)%20q)%2C%20%27%5B%7B%22numer_id%22%3A%22es.ine.t1_1%22%2C%22numer_timespan%22%3A%222015%22%2C%22normalization%22%3A%22area%22%7D%5D%27)&api_key=<my_api_key>

returns

{"rows":[{"obs_getmeta":[{"id":1,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_per_sq_km_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"area","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"}]}],"time":0.167,"fields":{"obs_getmeta":{"type":"JSON"}},"total_rows":1}

And executing the query:

SELECT * FROM cdb_observatory.OBS_GetData(ARRAY[(ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-4, 41), 4326), 3857), 250), 4326), 1)::geomval], '[{"id":1,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_per_sq_km_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"area","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"}]');

returns the expected value:

'[{"value":30.4982346984447}]'

Querying both measurements (OBS_GetData)

SELECT * FROM cdb_observatory.OBS_GetData(ARRAY[(ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-4, 41), 4326), 3857), 250), 4326), 1)::geomval], '[{"id":1,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_per_sq_km_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"area","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"},{"id":2,"numer_id":"es.ine.t1_1","timespan_rank":1,"score_rank":1,"timespan_rownum":1,"score_rownum":1,"score":18.055877553474502,"suggested_name":"total_pop_2015","numer_aggregate":"sum","numer_colname":"total_pop","numer_geomref_colname":"cusec_id","numer_tablename":"obs_24b656e9e23d1dac2c8ab5786a388f9bf0f4e5ae","numer_type":"Numeric","numer_description":"","numer_t_description":null,"denom_aggregate":null,"denom_colname":null,"denom_geomref_colname":null,"denom_tablename":null,"denom_type":null,"denom_reltype":null,"denom_description":null,"denom_t_description":null,"geom_colname":"the_geom","geom_geomref_colname":"cusec_id","geom_tablename":"obs_01b060d28ba00522076d6f7d7e8939adc6069c28","geom_type":"Geometry","geom_timespan":"2011","geom_description":"The smallest division of the Spanish Census.","geom_t_description":null,"numer_timespan":"2015","numer_name":"Total population","denom_name":null,"geom_name":"Sección Censal","normalization":"prenormalized","max_timespan_rank":null,"max_score_rank":null,"target_geoms":null,"target_area":null,"num_geoms":0.0912014279825554,"denom_id":null,"geom_id":"es.ine.the_geom"}]');

Also returns the expected value:

'[{"value":30.4982346984447},{"value":3.38578494040731}]'

[javitonino]

Yes, IIRC the DO part works, this is a problem with the analysis in camshaft itself, and how it generates the column names.