styling available datasets

[javisantana]

New styling options have aggregation capablities so a points table can be shown aggregated by zipcode, adm0 adm1 and so on.

In order to aggregate we need to have those datasets as FDW tables in user account.

We need at least the following tables (please, complete the list @saleiva @urbanphes )

• adm0
• adm1
• postal codes

I'd need to names and schema of those tables and also the bbox where they apply, like

[{
table: 'adm0',
bbox: [ [ xmin, ymin, xmax, ymax], ...] //list of bbox where the dataset can be used 
}]

[saleiva]

These will come from the DO, right?

Ideally we will have all the boundaries that we have in the what's on next on the DO.

2016-05-09 11:44 GMT+02:00 javi santana notifications@github.com:

New styling options have aggregation capablities so a points table can be shown aggregated by zipcode, adm0 adm1 and so on.

In order to aggregate we need to have those datasets as FDW tables in user account.

We need at least the following tables (please, complete the list @saleiva https://github.com/saleiva @urbanphes https://github.com/urbanphes )

• adm0
• adm1
• postal codes

I'd need to names and schema of those tables and also the bbox where they apply, like

[{ table: 'adm0,bbox: [ [ xmin, ymin, xmax, ymax], ...] //list of bbox where the dataset can be used }]

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub https://github.com/CartoDB/cartodb/issues/7329

Sergio Alvarez Leiva

[rafatower]

@saleiva the tables mentioned by @javisantana are the ones used by the internal geocoder, not the boundaries used by DO.

[javisantana]

I'd use tables from DO and not internal geocoder, at the end the internal geocoder should be using DO tables as a source

[saleiva]

exactly

2016-05-09 12:18 GMT+02:00 javi santana notifications@github.com:

I'd use tables from DO and not internal geocoder, at the end the internal geocoder should be using DO tables as a source

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub https://github.com/CartoDB/cartodb/issues/7329#issuecomment-217827844

Sergio Alvarez Leiva

[javisantana]

One important point is: those dataset should contain pretty basic normalization information as columns. I'd say the minimum amount of information is population so we can normalize when creating choropleth maps.

I know we have the DO to augment them but having that information would make things much faster and it's actually a must.

This is the end of non normalized choropleth maps!!

[saleiva]

Uhm... I can modify the wizard yeah On May 9, 2016 9:49 PM, "javi santana" notifications@github.com wrote:

One important point is: those dataset should contain pretty basic normalization information as columns. I'd say the minimum amount of information is population so we can normalize when creating choropleth maps.

I know we have the DO to augment them but having that information would make things much faster and it's actually a must.

This is the end of non normalized choropleth maps!!

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub https://github.com/CartoDB/cartodb/issues/7329#issuecomment-217969437

[javisantana]

about torque aggregated wizards: we don't have a way to normalize in torque. Does make sense to add new styling ways that are wrong?

[ethervoid]

To summarize and make it clearer:

• Create datasets in the DO database that have some needed columns like: population, geometry for the the region (country, postal code...), what more?
• Make the user databases connect using FDW with that datasets when is created in order to have it available in all the user databases to be used by the editor
• Please explain a bit this @javisantana [{ table: 'adm0', bbox: [ [ xmin, ymin, xmax, ymax], ...] //list of bbox where the dataset can be used }]
• Anything else?

// @andrewxhill (for the list of columns, tables and so on...)

[javisantana]

basically I need a json with the available tables and the bbox they cover

[ethervoid]

Yeah but the admin0 table covers all the countries and I assume that we are not going to have an admin0-spain table

[javisantana]

but the postal codes table is not going to cover the whole world, it will be available in some countries so that's why we need the list of bbox where it's available

[ethervoid]

Ok, from a chat with Javi, that JSON is going to be managed by the frontend part in order to know what tables could be used to aggregate.

[saleiva]

Ideally, we should autogenerate that using the bigmetadata service on the DO

2016-05-10 14:19 GMT+02:00 Mario de Frutos notifications@github.com:

Ok, that JSON is going to be managed by the frontend part in order to know what tables could be used to aggregate.

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub https://github.com/CartoDB/cartodb/issues/7329#issuecomment-218140551

Sergio Alvarez Leiva

[javisantana]

actually we should have a service to know what datasets are available in a zone but you know, we only have one magician in the team cc @piensaenpixel

[rafatower]

to my understanding that list should come from research and then we can have a function in user space to query for the list.

[saleiva]

We already have that function I think... (if not we should be close to)

2016-05-10 14:50 GMT+02:00 Rafa de la Torre notifications@github.com:

to my understanding that list should come from research and then we can have a function in user space to query for the list.

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub https://github.com/CartoDB/cartodb/issues/7329#issuecomment-218147484

Sergio Alvarez Leiva

[javisantana]

Yes, that should come from research but to start I'd add:

• admin0
• admin1

[javisantana]

don't forget about this one please!

[rafatower]

As per our conversation, we just need the following: connect all the user DB's with a hardcoded list of tables. To start with, just admin0 and admin1 from the geocoder. cc/ @ethervoid

[talos]

On the DO side, admin0 and admin correspond to the boundary IDs whosonfirst.country and whosonfirst.region respectively. The table names could change, but can be looked up using the API:

SELECT boundary_id, tablename
FROM OBS_GetAvailableBoundaries(CDB_LatLng(0,0))
WHERE boundary_id in ('whosonfirst.wof_region_geom',
                      'whosonfirst.wof_country_geom')

Yields:

boundary_id	tablename
whosonfirst.wof_country_geom	obs_1ea93bbc109c87c676b3270789dacf7a1430db6c
whosonfirst.wof_region_geom	obs_4fca4f060854cc3ae8c109999635a71bbde6964e

[talos]

You can preview those geoms in the observatory.cartodb.com account:

• https://observatory.cartodb.com/tables/obs_1ea93bbc109c87c676b3270789dacf7a1430db6c/public/map
• https://observatory.cartodb.com/tables/obs_4fca4f060854cc3ae8c109999635a71bbde6964e/public/map

[javisantana]

just to be clear: we need real FDW tables and the tables need to be ready for rendering:

• geometries simplified to a specific zoom level (16-17 is enough)
• only the needed data, no extra columns to avoid extra work and be always in memory
• they need to be fixed title, it can NOT use the versioning schema with hash

It can be achieved with a mat view or something

[ethervoid]

Ok, we have the first iteration for this feature.

I've releases to all the team accounts the connection with the tables for admin0 and admin1.

To query for it you have to execute one of the following sentences:

• SELECT * FROM aggregation.agg_admin0;
• SELECT * FROM aggregation.agg_admin1;

Right now the tables have the population field to null, i tried to get the pop_est field from admin0 table but is empty so we don't have population by now. We have just the_geom and the_geom_webmercator.

Enjoy!

// @javisantana @xavijam

[javisantana]

It still lacks of population column, we should try to have it because it's really important. One of the things we should improve is visualization quality. We are adding a way to normalize by area for now but we should have also a population normalization

[ethervoid]

Yes, one way to have it is that research team using the observatory populate a new or existent column in the current tables. I think this could give us some time. I could add that columns to the current deploy in order to have population column

[javisantana]

good idea man

[talos]

OK! World population is a possibility, as a raster. How would that work for you?

[rafatower]

as a raster

Sorry, I don't understand. what do you mean with "raster", @talos ? What we need from you guys is to populate the population column of the agg_admin0 and agg_admin1 tables mentioned above with their total population.

[andrewxhill]

hey @rafatower there is no global vector dataset of populations at admin1, that we know of... if you know of one please share it with us.

What we do know of is a raster (a gridded world population) generated by Columbia which we backlogged because we needed our lawyer to sign off on their usage agreement. It looks like we are okay to move forward with that layer now.

On your end we need to know how you want us to populate those columns. Since the files you want augmented aren't in the D.O., you need to send us those source files or give us instructions on where to populate new columns.

After you get us that, we can populate your column locally (off cartodb and the DO) and send you back a value for each Admin1.

Admin0 is likely easier as you can grab one of these estimates,

• http://data.worldbank.org/indicator/SP.POP.TOTL
• https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population
• https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html

[saleiva]

I find this failing yesterday. What is the actual status of this?

[andrewxhill]

I don't think this should have been assigned to me yet... here is the key part of my comment before:

On your end we need to know how you want us to populate those columns. Since the files you want augmented aren't in the D.O., you need to send us those source files or give us instructions on where to populate new columns.

After you get us that, we can populate your column locally (off cartodb and the DO) and send you back a value for each Admin1.

[rochoa]

There has been no activity on this issue for several months. We are closing it. If you think this still needs to be addressed please open a new issue.