Akvo Maps service (Proof of Concept)

[iperdomo]

The Akvo Maps service is in charge of serving tiles to be use in Akvo products (Flow, Lumen, etc).

Deliverables

• Docker files describing a running system
• Documentation on the format/layout required by the data tables and/or any data preparation need
• API Documentation (e.g. HTTP requests to interact with the system)

Assumptions

• The service will be based on Windshaft
• The service will be deployed as Docker containers
• Following the the Docker philosophy we want to have the least amount of required service per container (e.g. PostGIS, Redis, Windshaft are at least 3 containers)

Out of scope

• We assume that the PostGIS database has already data with the proper format (e.g. lat/lon columns and the_geom, etc), this service is not in charge of importing/syncing data from an external source

[iperdomo]

Our base image should be something that will be supported for a few years, e.g. Ubuntu 14.04 or 16.04

https://upload.wikimedia.org/wikipedia/en/timeline/09570d97e8879c50ac2be27c6a39b045.png

[strk]

You mentioned you want the PostGIS docker image to have a demo database preloaded with data, correct ? Do you have a link to such example data or do you want me to put something togheter ?

[mtwestra]

@strk, I'll send you a data set by email

[strk]

Ok. The data will need to be in this git repository in order to get into the PostGIS docker, fine with you ?

[mtwestra]

@strk, yes, that is fine - it is an open data set

[strk]

With 7d8c759 I made a stub at a akvo-postgis image, with an "akvo" database preloaded with the given dataset. NOTE: 150 records in that CSV file have empty longitude/latitude

Tomorrow will look at the windshaft component

[strk]

I've made progress, but hit a roadblock: https://github.com/CartoDB/Windshaft/issues/503

Basically, it looks like Windshaft is now also depending on the presence of CartoDB functions in the database. For the POC I'll probably add the required functions (in a few hours)

[mtwestra]

@strk, the main issue for us is that most hosted postgres services don't support custom extensions, so if the functions are part of an extension, it is a problem. However, those services do support loading individual SQL functions, so that could be a fallback option if needed. But the dependency does seem strange.

[strk]

Ok for this POC I'll give priority to get it up and running, later I'll see how could the required structure be added to a database w/out using extensions (does it also apply to PostGIS or can it be considered available on systems?).

[mtwestra]

@strk, absolutely, for the POC it is no problem. PostGIS is always available as an extension, it is just unknown 'custom' extensions that are restricted.

[strk]

So it turns out the required database function are shipped with Windshaft, so I modified the Docker for the database to load those functions from SQL files (no need for them to be on the server).

As of current master (71947a876dbc17eb62af06e99f5a54e06a682c3b) you can get a running system by following instructions in the top-level README.md file.

Please let me know if you can get to the point of seeing those blue dots :)

NOTE: you'll have to zoom in to see more dots, evidently the default CSS forbids overlaps (but can be changed in the viewer interface)

[mtwestra]

@strk, @iperdomo, excellent! We'll be testing first thing in the morning, and let you know the results.

[iperdomo]

It seems that our development.js settings has a reference to the_geom column (not completely sure if this is the issue of this error in the logs.

2016-11-09 20:25:04 UTC [8-1] LOG:  database system is ready to accept connections
2016-11-09 20:25:39 UTC [18-1] akvo@akvo ERROR:  attribute "the_geom" does not exist
2016-11-09 20:25:39 UTC [18-2] akvo@akvo STATEMENT:  SELECT row_to_json(s) as result FROM(select _postgis_stats('public.liberia'::regclass, 'the_geom') as stats) as s
2016-11-09 20:27:12 UTC [26-1] akvo@akvo ERROR:  attribute "the_geom" does not exist
2016-11-09 20:27:12 UTC [26-2] akvo@akvo STATEMENT:  SELECT row_to_json(s) as result FROM(select _postgis_stats('public.liberia'::regclass, 'the_geom') as stats) as s
2016-11-09 20:33:06 UTC [38-1] akvo@akvo ERROR:  attribute "the_geom" does not exist
2016-11-09 20:33:06 UTC [38-2] akvo@akvo STATEMENT:  SELECT row_to_json(s) as result FROM(select _postgis_stats('public.liberia'::regclass, 'the_geom') as stats) as s
2016-11-09 20:33:19 UTC [41-1] akvo@akvo ERROR:  attribute "the_geom" does not exist
2016-11-09 20:33:19 UTC [41-2] akvo@akvo STATEMENT:  SELECT row_to_json(s) as result FROM(select _postgis_stats('public.liberia'::regclass, 'the_geom') as stats) as s
2016-11-09 20:42:53 UTC [52-1] akvo@akvo ERROR:  attribute "the_geom" does not exist
2016-11-09 20:42:53 UTC [52-2] akvo@akvo STATEMENT:  SELECT row_to_json(s) as result FROM(select _postgis_stats('public.liberia'::regclass, 'the_geom') as stats) as s

[strk]

It's a bug in Windshaft, filed earlier today: https://github.com/CartoDB/Windshaft/issues/504

[iperdomo]

I can see the blue dots :+1:

[strk]

On Wed, Nov 09, 2016 at 12:53:34PM -0800, Iván Perdomo wrote:

I can see the blue dots :+1:

:)

Try adding to the CSS:

marker-fill:#f45; marker-line-color:#813; marker-allow-overlap:true; marker-fill-opacity: 0.3;

[iperdomo]

Interesting :smile:

[iperdomo]

... adding a OSM base layer

[strk]

Nice! How did you add the baselayer ? Worth pushing it :)

[iperdomo]

Done in https://github.com/akvo/akvo-maps/commit/c9cb5f2adfbbca61d9fd6a2cf0a6d412fe83cbce

[iperdomo]

@strk do you think the tile server can be setup using a maintained version of express? Checking the package.json we depend on "express": "~2.5.11" while this version is not longer supported:

Express 2.x IS NO LONGER MAINTAINED Known and unknown security and performance issues in 2.x have not been addressed since the last update (29 June, 2012). It is highly recommended to use the latest version of Express.

http://expressjs.com/2x/

[strk]

Sure, as the http-facing part is all handled by "us".

Note that we're using the example server implementation as found in "windshaft", so it needs more cleanups too ...

[iperdomo]

Having some fun with CartoCSS. The blue points are water pumps with year construction on 2009 or later (had to clean up the data in the query).

{
  "version": "1.5.0",
  "layers": [
    {
      "type": "mapnik",
      "options": {
        "sql": "select instance, geom, yearcons::integer as yearcons from liberia where yearcons ~ '^\\d{4}$';",
        "geom_column": "geom",
        "cartocss": "#s { marker-width: 5; marker-fill:#f45; marker-line-color:#813;
  marker-allow-overlap:true; marker-fill-opacity: 0.3;} #s[yearcons>=2009] {marker-fill: #1F78B4; marker-line-color: #0000FF;}",
        "cartocss_version": "2.0.0",
        "interactivity": "instance"
      }
    }
  ]
}

[strk]

Nice ! Not as much fun in upgrading server to express 5 :) -- but slowly getting there.

WARNING: I've pushed a commit changing the name of the "akvo-windshaft" image to "akvo-tiler"

[strk]

Express upgraded to 4.3.x as of 9829a41fcc911830b4248031c3479b5e777c25c1 I suggest further changes are discussed with separate issues (as I'd call the Proof of Concept completed, don't you think?)

[iperdomo]

@strk agree. No need more code changes. There are some function calls in the data (postgis image) that I would like to document. The MultiLayer API is already documented, but the data preparation for a table to be used as source of markers is something I would like to have documented.

Deliverables

• [x] Docker files describing a running system
• [ ] Documentation on the format/layout required by the data tables and/or any data preparation needed
• [x] API Documentation (e.g. HTTP requests to interact with the system)

[strk]

Will document in a README in the image/postgis

[iperdomo]

I think with the current state we're done as proof of concept