Admin boundaries in maps - investigation

[nadiagorchakova]

Context

Part of contractual obligations to the 9 country program

Example from Liberia water atlas:

The map feature is also supposed to support 'filtering/grouping on admin boundaries'

Problem or idea

What is the cause?

Solution or next step

How could we solve it?

[jonase]

I did some initial investigation on this and just want to quickly document the steps I took

Preparing the database

• Download a shapefile from http://www.gadm.org/download (we might use another source later)
• Prepare a sql file using the shp2psql tool (in this case I used Uganda as an example):

shp2pgsql -I -s 4326 \
  UGA_adm_shp/UGA_adm4.shp \
  public.uga_adm4 > uga_adm4.sql

• Create the table in postgres:

  cat uga_adm4.sql | psql \
  --host=localhost \
  --port=5432 \
  --dbname=lumen_tenant_1 \
  --username=lumen \
  --password

Create a map in Lumen

• Import a Flow form into Lumen and apply the new "To Geometry" transformation

• git checkout @peeb's demo branch (https://github.com/akvo/akvo-lumen/compare/issue/1004-maps-integration-test)

• As everything is manual, we need to figure out the datasets table name + column names we want to use in queries

• Create a windshaft map spec similar to

  const mapConfig = {
  version: '1.6.0',
  layers: [
  {
    type: 'mapnik',
    options: {
      srid: 4326,
      sql: `SELECT geom, count(${table}.d1) AS total, SUM(CASE WHEN ${table}.c17140671 = '1:Yes' THEN 1.00 ELSE 0.00 END) / COUNT(*) AS percentage from uga_adm4 LEFT JOIN ${table} ON st_contains(uga_adm4.geom, ${table}.d1) GROUP BY uga_adm4.geom`,
      geom_column: 'geom',
      cartocss: '#a [total >= 1]{ polygon-opacity: 0.25; line-color: #000; line-width: 0.5; } #a [percentage >= 0.3] {  polygon-fill: #00f } #a [percentage < 0.3] {  polygon-fill: #f00; } #a [total = 0] {  polygon-opacity: 0; line-width: 0 }',
      cartocss_version: '2.0.0',
    },
  },
  {
    type: 'mapnik',
    options: {
      srid: 4326,
      sql: `select * from ${table}`,
      geom_column: 'd1',
      cartocss: '#s { marker-width: 4; marker-fill: #e00050; }',
      cartocss_version: '2.0.0',
    },
  },
  ],
  };

• This will render a map with Administrative Level 4 boundaries coloured depending the occurrence percentage of a particular value in an option question. The individual data points are also rendered (I don't want to paste the map as it might contain private data)

[gabemart]

I have explored the visual side a bit more, building on what Jonas documented. Here are some more findings:

Slow performance with large geoms

Postgres seems to be slow when the number of datapoints is low and the size (i.e. physical size) of the geoms is large. I discovered this when trying to color the admin boundary for the USA at admin level 0, which is the whole country. This is a bit counter intuitive - I had thought that smaller admin boundaries would be expensive queries, because there will be lots and lots of boundaries.

This is a known issue - see 7.1 in https://postgis.net/docs/performance_tips.html

Here are some example queries running on USA admin boundary level 1 (level 0 is too slow for effective testing), and using a test dataset of food service data

lumen_tenant_1=> explain analyze SELECT avg(ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.c14) AS foo FROM usa1 LEFT JOIN ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e ON st_contains(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) GROUP BY usa1.geom;
                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=76117.47..76118.65 rows=52 width=662122) (actual time=440359.728..441245.584 rows=52 loops=1)
   Group Key: usa1.geom
   ->  Sort  (cost=76117.47..76117.65 rows=70 width=662122) (actual time=440316.339..440334.623 rows=4112 loops=1)
         Sort Key: usa1.geom
         Sort Method: quicksort  Memory: 543kB
         ->  Nested Loop Left Join  (cost=0.00..56308.83 rows=70 width=662122) (actual time=1765.749..438438.254 rows=4112 loops=1)
               Join Filter: ((usa1.geom ~ ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) AND _st_contains(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1))
               Rows Removed by Join Filter: 207367
               ->  Seq Scan on usa1  (cost=0.00..5.52 rows=52 width=662114) (actual time=0.028..0.299 rows=52 loops=1)
               ->  Materialize  (cost=0.00..283.99 rows=4066 width=40) (actual time=0.023..23.598 rows=4066 loops=52)
                     ->  Seq Scan on ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e  (cost=0.00..263.66 rows=4066 width=40) (actual time=0.711..15.258 rows=4066 loops=1)
 Planning time: 2.421 ms
 Execution time: 441254.734 ms
(13 rows)

We can speed this up a lot by only considering the admin boundaries that contain at least one datapoint, by adding a WHERE ST_Intersects(usa1.geom, ${table}.${geopoint}) condition - with this condition, the query runs in 1/170 of the time

lumen_tenant_1=> explain analyze SELECT avg(ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.c14) AS foo FROM usa1 LEFT JOIN ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e ON st_contains(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) WHERE ST_Intersects(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) GROUP BY usa1.geom;
                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=8084.43..8084.79 rows=18 width=662122) (actual time=2021.925..2583.564 rows=5 loops=1)
   Group Key: usa1.geom
   ->  Sort  (cost=8084.43..8084.47 rows=18 width=662122) (actual time=2021.638..2034.276 rows=4065 loops=1)
         Sort Key: usa1.geom
         Sort Method: quicksort  Memory: 414kB
         ->  Nested Loop  (cost=0.14..2991.55 rows=18 width=662122) (actual time=1.549..1373.434 rows=4065 loops=1)
               ->  Seq Scan on ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e  (cost=0.00..263.66 rows=4066 width=40) (actual time=0.157..11.961 rows=4066 loops=1)
               ->  Index Scan using usa1_geom_idx on usa1  (cost=0.14..0.66 rows=1 width=662114) (actual time=0.318..0.323 rows=1 loops=4066)
                     Index Cond: ((geom ~ ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) AND (geom && ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1))
                     Filter: (_st_contains(geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) AND _st_intersects(geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1))
                     Rows Removed by Filter: 1
 Planning time: 0.662 ms
 Execution time: 2584.153 ms
(13 rows)

The "performance tips" document linked above suggests setting SET enable_seqscan TO off to improve performance, but that does not speed up the query in our case:

lumen_tenant_1=> SET enable_seqscan TO off; explain analyze SELECT avg(ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.c14) AS foo FROM usa1 LEFT JOIN ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e ON st_contains(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) WHERE ST_Intersects(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) GROUP BY usa1.geom;
SET
                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=10000008084.43..10000008084.79 rows=18 width=662122) (actual time=2073.424..2632.973 rows=5 loops=1)
   Group Key: usa1.geom
   ->  Sort  (cost=10000008084.43..10000008084.47 rows=18 width=662122) (actual time=2073.123..2085.648 rows=4065 loops=1)
         Sort Key: usa1.geom
         Sort Method: quicksort  Memory: 414kB
         ->  Nested Loop  (cost=10000000000.14..10000002991.55 rows=18 width=662122) (actual time=0.976..1411.484 rows=4065 loops=1)
               ->  Seq Scan on ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e  (cost=10000000000.00..10000000263.66 rows=4066 width=40) (actual time=0.130..11.961 rows=4066 loops=1)
               ->  Index Scan using usa1_geom_idx on usa1  (cost=0.14..0.66 rows=1 width=662114) (actual time=0.327..0.332 rows=1 loops=4066)
                     Index Cond: ((geom ~ ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) AND (geom && ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1))
                     Filter: (_st_contains(geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) AND _st_intersects(geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1))
                     Rows Removed by Filter: 1
 Planning time: 0.369 ms
 Execution time: 2633.547 ms
(13 rows)

http://revenant.ca/www/postgis/workshop/indexing.html suggests that adding an index on the geom column might improve performance (on our working dataset - the admin boundary dataset had a geo index created as per jonas's instructions above). We can do this with CREATE INDEX geoidx ON ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e USING GIST (d1); VACUUM ANALYZE ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e. After doing this, I saw a small increase in peformance:

lumen_tenant_1=> explain analyze SELECT avg(ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.c14) AS foo FROM usa1 LEFT JOIN ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e ON st_contains(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) WHERE ST_Intersects(usa1.geom, ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e.d1) GROUP BY usa1.geom;
                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=10000005433.63..10000005433.99 rows=18 width=662122) (actual time=1814.368..2395.544 rows=5 loops=1)
   Group Key: usa1.geom
   ->  Sort  (cost=10000005433.63..10000005433.67 rows=18 width=662122) (actual time=1814.103..1827.176 rows=4065 loops=1)
         Sort Key: usa1.geom
         Sort Method: quicksort  Memory: 414kB
         ->  Nested Loop  (cost=10000000000.15..10000000340.75 rows=18 width=662122) (actual time=73.710..1280.051 rows=4065 loops=1)
               ->  Seq Scan on usa1  (cost=10000000000.00..10000000005.52 rows=52 width=662114) (actual time=0.025..0.232 rows=52 loops=1)
               ->  Index Scan using geoidx on ds_52f8a8f1_fd45_4321_b6b8_b9e27c16533e  (cost=0.15..6.44 rows=1 width=40) (actual time=1.956..20.762 rows=78 loops=52)
                     Index Cond: ((usa1.geom ~ d1) AND (usa1.geom && d1))
                     Filter: (_st_contains(usa1.geom, d1) AND _st_intersects(usa1.geom, d1))
                     Rows Removed by Filter: 78
 Planning time: 0.365 ms
 Execution time: 2400.522 ms
(13 rows)

Color manipulation

We need to color the admin boundaries in a data-driven way.

One simple method is to pull a value directly from the SQL and use it in cartoCss - e.g. SELECT avg(c14) AS foo... in the SQL, and then #a { polygon-opacity: [foo]}in the cartoCss. This appears to work only for cartocss properties which accept a number directly, like opacity. Trying to use [foo] in a color string, like color: rgb([foo], 0, 0), or in a color function, like lighten(red, [foo]) will not work. Similarly, using a variable like @value: [foo]; color: rgb(@value, 0, 0) will not work.

This could be a problem, as manipulating color using only opacity does not give us much flexibility, and raises additional problems in multi-layer maps - high-opacity colors obscure map details (e.g. labels) from layers further down the stack.

One workaround would be to generate fixed cartocss selectors programatically, like [value > 0.99] { color: rgb(255, 0, 0) } [value > 0.98] { color: rgb(254, 0, 0,) }... but this is clearly not an attractive option.

Another workaround is to generate color strings in the SQL query directly, like SELECT concat('rgb(', avg(c14), ', 0, 0)') AS foo... - when done this way, the resulting string can be used directly in the cartocss, like { color: [foo] }.

I've used RGB in the above examples for simplicity, but in practise we may want to use hsl or a color function.

Things to think about

• When we color an admin boundary, we probably need for it to be translucent, so that labels from the base layer can still be viewed. However, colors from the base layer can bleed through and make the coloring of the admin boundary harder to discern, so perhaps using a monochrome base layer might be useful for these types of maps. For example, there are monochrome OSM tilesets available.

• performance for large geoms needs more research. We are very early on this so there may be (and probably are) lots of easy optimisations we haven't considered

Example map using a monochrome base layer and color strings generated in sql query

[iperdomo]

We have found that depending on the available data (shapefile) we can a more (or less accurate) admin boundaries. In this example we're trying to color the "Grand Cape Mount" county.

GDAM

Liberia Goverment

[iperdomo]

The same county with GAUL shapefile (http://www.fao.org/geonetwork/srv/en/metadata.show?id=12691)

[iperdomo]

HDX - https://data.humdata.org/dataset/liberia-admin-level-2-boundaries

[iperdomo]

I'm currently investigating how to speed up the generation of a custom map based on liberia test dataset in (https://github.com/akvo/akvo-maps).

This is the current made up map visualization to color read those counties with less than 60% of available water points that are Working and protected (this is a made up formula, and SQL statement is not optimized)

{
  "version": "1.5.0",
  "layers": [
    {
      "type": "mapnik",
      "options": {
        "sql": "WITH counties AS (SELECT adm1_name, st_union(geom) AS geom FROM lbr_humandata GROUP BY adm1_name ), valid_data AS (  SELECT functionality, geom FROM liberia WHERE geom IS NOT NULL ), liberia_data AS (SELECT c.adm1_name, c.geom AS geom, d.functionality    FROM counties c, valid_data d   WHERE ST_Contains(c.geom, d.geom) ), totals AS (  SELECT adm1_name, count(*) AS total    FROM counties  GROUP BY adm1_name )SELECT l.adm1_name, l.geom, (SUM(CASE WHEN l.functionality = 'Working and protected' THEN 1 ELSE 0 END) / SUM(t.total)) as ratio FROM liberia_data l, totals t WHERE l.adm1_name = t.adm1_name GROUP BY l.adm1_name, l.geom;",
        "geom_column": "geom",
        "srid": 4326,
        "cartocss": "#s { polygon-opacity: 0.25;} #s[ratio<=0.6] {line-color: #FF0000; line-width: 0.5; polygon-fill: #FF0000;}",
        "cartocss_version": "2.0.0",
        "interactivity": "adm1_name"
      }
    }
  ]
}

[iperdomo]

Cold server just restarted

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (WITH counties AS (SELECT adm1_name, st_union(geom) AS geom FROM lbr_humandata GROUP BY adm1_name ), valid_data AS (  SELECT functionality, geom FROM liberia WHERE geom IS NOT NULL ), liberia_data AS (SELECT c.adm1_name, c.geom AS geom, d.functionality    FROM counties c, valid_data d   WHERE ST_Contains(c.geom, d.geom) ), totals AS (  SELECT adm1_name, count(*) AS total    FROM counties  GROUP BY adm1_name )SELECT l.adm1_name, l.geom, (SUM(CASE WHEN l.functionality = 'Working and protected' THEN 1 ELSE 0 END) / SUM(t.total)) as ratio FROM liberia_data l, totals t WHERE l.adm1_name = t.adm1_name GROUP BY l.adm1_name, l.geom) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 1411.366 ms

• The database 1.41s. The initial POST request takes 1.74s and then 1.92s to answer the tile requests

• Zooming in, we make lots of requests, most of them taking more than 2s, giving the feeling of an slow UI.

An easy approach is to create a materialized view with the data, so the database doesn't need to recalculate it on every request.

CREATE MATERIALIZED VIEW AS [QUERY]

The query in the map config is now SELECT * FROM <view_name>, e.g.

{
  "version": "1.5.0",
  "layers": [
    {
      "type": "mapnik",
      "options": {
        "sql": "select * from map_view_1",
        "geom_column": "geom",
        "srid": 4326,
        "cartocss": "#s { polygon-opacity: 0.25;} #s[ratio<=0.6] {line-color: #FF0000; line-width: 0.5; polygon-fill: #FF0000;}",
        "cartocss_version": "2.0.0",
        "interactivity": "adm1_name"
      }
    }
  ]
}

Now the sever responds in milliseconds and not seconds:

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (select * from map_view_1) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 4.768 ms

The initial POST request is 131ms vs 1.74s and the subsequent GET requests in less than 390ms

The user experience of zooming in is also improved, as the requests are faster:

[iperdomo]

If we use a table CREATE TABLE AS <query> WITH an index on the geom column we make the query even faster.

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (select * from map_view_1) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 4.648 ms

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (select * from t_view_1) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 1.704 ms

[iperdomo]

Less fancy query not using CTE.

{
  "version": "1.5.0",
  "layers": [
    {
      "type": "mapnik",
      "options": {
        "sql": "SELECT adm1_name, geom, (SUM( CASE WHEN functionality = 'Working and protected' THEN 1 ELSE 0 END ))::float / (count(*))::float as ratio FROM (SELECT c.adm1_name, c.geom AS geom, l.functionality FROM (SELECT adm1_name, ST_Union(geom) AS geom FROM lbr_humandata GROUP BY adm1_name) c, liberia l WHERE ST_Contains(c.geom, l.geom)) AS a GROUP BY adm1_name, geom",
        "geom_column": "geom",
        "srid": 4326,
        "cartocss": "#s { polygon-opacity: 0.25;} #s[ratio<=0.6] {line-color: #FF0000; line-width: 0.5; polygon-fill: #FF0000;}",
        "cartocss_version": "2.0.0",
        "interactivity": "adm1_name"
      }
    }
  ]
}

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (SELECT adm1_name, geom, (SUM( CASE WHEN functionality = 'Working and protected' THEN 1 ELSE 0 END ))::float / (count(*))::float as ratio FROM (SELECT c.adm1_name, c.geom AS geom, l.functionality FROM (SELECT adm1_name, ST_Union(geom) AS geom FROM lbr_humandata GROUP BY adm1_name) c, liberia l WHERE ST_Contains(c.geom, l.geom)) AS a GROUP BY adm1_name, geom) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 985.392 ms

Caching the resulting data in a materialized view

LOG:  execute <unnamed>: SELECT ST_AsBinary("geom") AS geom,"ratio" FROM (select * from map_view_1) as cdbq WHERE "geom" && ST_SetSRID('BOX3D(-179.9999996156177 -85.05112874664718,179.9999996156177 85.05112874664718)'::box3d, 4326)
LOG:  duration: 3.115 ms

[iperdomo]

Partially caching the boundaries of counties can save some aggregation work.

test_database=# EXPLAIN ANALYZE SELECT adm1_name, geom, (SUM( CASE WHEN functionality = 'Working and protected' THEN 1 ELSE 0 END ))::float / (count(*))::float as ratio FROM (SELECT c.adm1_name,                                                                                   c.geom AS geom,                                                                               d.functionality                                                                         FROM  (SELECT   adm1_name,                                                                                    St_union(geom) AS geom                                                                FROM    lbr_humandata                                                                        GROUP BY adm1_name) c,                                                                      (SELECT * FROM liberia WHERE geom IS NOT NULL) d                                        WHERE  St_contains(c.geom, d.geom) ) AS a GROUP BY adm1_name, geom;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=843.53..844.66 rows=15 width=48) (actual time=890.055..960.752 rows=15 loops=1)
   Group Key: lbr_humandata.adm1_name, (st_union(lbr_humandata.geom))
   ->  Sort  (cost=843.53..843.67 rows=58 width=61) (actual time=882.320..956.354 rows=11523 loops=1)
         Sort Key: lbr_humandata.adm1_name, (st_union(lbr_humandata.geom))
         Sort Method: external merge  Disk: 221936kB
         ->  Nested Loop  (cost=70.19..841.83 rows=58 width=61) (actual time=42.924..520.480 rows=11523 loops=1)
               ->  HashAggregate  (cost=70.04..70.23 rows=15 width=40) (actual time=42.839..263.183 rows=15 loops=1)
                     Group Key: lbr_humandata.adm1_name
                     ->  Seq Scan on lbr_humandata  (cost=0.00..69.36 rows=136 width=4273) (actual time=0.019..0.216 rows=136 loops=1)
               ->  Index Scan using liberia_geom_idx on liberia  (cost=0.15..51.39 rows=4 width=53) (actual time=0.597..17.029 rows=768 loops=15)
                     Index Cond: (((st_union(lbr_humandata.geom)) ~ geom) AND (geom IS NOT NULL))
                     Filter: _st_contains((st_union(lbr_humandata.geom)), geom)
                     Rows Removed by Filter: 556
 Planning time: 0.736 ms
 Execution time: 998.822 ms
(15 rows)

test_database=# EXPLAIN ANALYZE SELECT adm1_name, geom, (SUM( CASE WHEN functionality = 'Working and protected' THEN 1 ELSE 0 END ))::float / (count(*))::float as ratio FROM (SELECT c.adm1_name,                                                                                   c.geom AS geom,                                                                               d.functionality                                                                         FROM  (SELECT  * FROM lbr_counties) c,                                                                      (SELECT * FROM liberia WHERE geom IS NOT NULL) d                                        WHERE  St_contains(c.geom, d.geom) ) AS a GROUP BY adm1_name, geom;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=774.30..775.43 rows=15 width=98) (actual time=544.045..637.141 rows=15 loops=1)
   Group Key: lbr_counties.adm1_name, lbr_counties.geom
   ->  Sort  (cost=774.30..774.44 rows=58 width=111) (actual time=531.859..533.360 rows=11523 loops=1)
         Sort Key: lbr_counties.adm1_name, lbr_counties.geom
         Sort Method: quicksort  Memory: 1630kB
         ->  Nested Loop  (cost=0.15..772.60 rows=58 width=111) (actual time=0.364..376.871 rows=11523 loops=1)
               ->  Seq Scan on lbr_counties  (cost=0.00..1.15 rows=15 width=90) (actual time=0.018..0.043 rows=15 loops=1)
               ->  Index Scan using liberia_geom_idx on liberia  (cost=0.15..51.39 rows=4 width=53) (actual time=0.629..24.978 rows=768 loops=15)
                     Index Cond: ((lbr_counties.geom ~ geom) AND (geom IS NOT NULL))
                     Filter: _st_contains(lbr_counties.geom, geom)
                     Rows Removed by Filter: 556
 Planning time: 0.707 ms
 Execution time: 637.469 ms
(13 rows)

[iperdomo]

{
  "version": "1.5.0",
  "layers": [
    {
      "type": "mapnik",
      "options": {
        "sql": "SELECT adm1_name, geom, (SUM( CASE WHEN functionality = 'Working and protected' THEN 1 ELSE 0 END ))::float / (count(*))::float as ratio FROM (SELECT c.adm1_name, c.geom AS geom, l.functionality FROM (SELECT adm1_name, ST_Union(geom) AS geom FROM lbr_humandata GROUP BY adm1_name) c, liberia l WHERE ST_Contains(c.geom, l.geom)) AS a GROUP BY adm1_name, geom",
        "geom_column": "geom",
        "srid": 4326,
        "cartocss": "#s { text-name: [adm1_name]; text-face-name: 'DejaVu Sans Condensed';  text-size: 12; text-halo-fill: fadeout(white, 30%); text-halo-radius: 2.5; polygon-opacity: 0.25; line-width: 0.5;} #s[ratio<=0.6] {line-color: #FF0000; polygon-fill: #FF0000;}",
        "cartocss_version": "2.0.0",
        "interactivity": "adm1_name"
      }
    }
  ]
}

[nadiagorchakova]

the latest mockup by Kiarii https://projects.invisionapp.com/share/F8DR392P7#/screens/256230936

was user-tested with a couple of partners