Show rating history for a certain POI

[schultyy]

As a user I would like to see the mapping history for a certain POI on the detail page. The reason for this feature is the following:

By having access to the rating history it is easier for me as a user to evaluate if the current rating is accurate or not. This means can I really rely on the current rating or should I take it with a grain of salt.

[schultyy]

The following was discussed so far:

• Rating history is shown on the POI detail page
• A history entry consists of the following:
  • The new status
  • When did it happen
  • Who did the edit (Either username or "Wheelmap Visitor" in case it was done by an anonymous user)

A roadmap for this feature can look like this:

• We start to collect these changes in our MySQL database
• We ask Robert from Frauenhofer for an initial dataset to bootstrap our history
• We implement a new API endpoint which provides these data
• We show the history per POI in the wheelmap web application

[holgerd]

Sketch:

[Hoverbear]

Would this data not already be present in OSM?

[holgerd]

@Hoverbear Yes it is in OSM. Eg. https://www.openstreetmap.org/way/32487700/history but it is very technical and not focused on a11y.

[holgerd]

@Hoverbear looks into this. :=)

[Hoverbear]

We currently don't store the history in the MySQL database:

mysql> SHOW COLUMNS FROM pois;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| osm_id       | bigint(20)   | NO   | PRI | NULL    |       |
| version      | int(11)      | NO   |     | NULL    |       |
| tags         | text         | YES  | MUL | NULL    |       |
| geom         | point        | NO   | MUL | NULL    |       |
| status       | mediumint(9) | NO   | MUL | 8       |       |
| created_at   | datetime     | YES  |     | NULL    |       |
| updated_at   | datetime     | YES  |     | NULL    |       |
| node_type_id | bigint(20)   | YES  | MUL | NULL    |       |
| region_id    | bigint(20)   | YES  | MUL | NULL    |       |
| geoj         | text         | YES  |     | NULL    |       |
| geoj_dirty   | tinyint(1)   | YES  |     | 1       |       |
| toilet       | tinyint(1)   | YES  | MUL | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

I also don't see a copy of this data in our Postges instance:

osm=# \d+ pseudo_nodes;
                           Table "public.pseudo_nodes"
      Column      |         Type         |   Modifiers   | Storage  | Description
------------------+----------------------+---------------+----------+-------------
 osm_id           | integer              |               | plain    |
 amenity          | text                 |               | extended |
 tourism          | text                 |               | extended |
 historic         | text                 |               | extended |
 sport            | text                 |               | extended |
 leisure          | text                 |               | extended |
 shop             | text                 |               | extended |
 office           | text                 |               | extended |
 aerialway        | text                 |               | extended |
 aeroway          | text                 |               | extended |
 public_transport | text                 |               | extended |
 tags             | hstore               |               | extended |
 dirty            | boolean              | default false | plain    |
 deleted          | boolean              | default false | plain    |
 way              | geometry(Point,4326) |               | main     |
Indexes:
    "id_index" UNIQUE, btree (osm_id), tablespace "slower"
    "deleted_index" btree (deleted), tablespace "slower"
    "dirty_index" btree (dirty), tablespace "slower"
Has OIDs: no
Tablespace: "slower"

osm=# \d+ planet_osm_nodes;
       Unlogged table "public.planet_osm_nodes"
 Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 id     | bigint  | not null  | plain    |
 lat    | integer | not null  | plain    |
 lon    | integer | not null  | plain    |
 tags   | text[]  |           | extended |
Indexes:
    "planet_osm_nodes_pkey" PRIMARY KEY, btree (id), tablespace "slower"
Has OIDs: no
Tablespace: "slower"

According to this page there is no existing API or source to access to replicate this data beyond irregular snapshots.

So, if we were to want to implement this feature we would likely need to track these changes ourselves starting from one of the full history dumps. I think this could present a considerable body of work.

[holgerd]

thanks. So let's put this on hold for now.