ES:LatAm/Proyectos/Resolución de notas/Preparación premios

Esta página contiene la descripción de los pasos para poder identificar los premios ONL - OpenNotesLatam.

Procesamiento de notas del Planet para análisis en base de datos.

Lista de pasos

Instalar Postgres.
Crear una base de datos Postgres.
Activar la funcionalidad PostGIS.
Instalar un procesador de archivos GeoJSON hacia Postgres: ogr2ogr
Instalar Java y descargar Saxon.
Crear las tablas del modelo.
Insertar las áreas de los países de Latam.
Descargar el archivo del planet.
Procesar el archivo de notas para insertarlo en la DB.
Identificar las notas que son de Latam.
Ejecutar queries para el análisis sobre las notas.

Los pasos anteriores son necesarios para poder hacer un análisis, principalmente de resolución de notas. Se toma el archivo de notas del Planet, y se procesan todas. Por este motivo, la identificación del lugar al cual pertenece una nota puede ser time-consuming. Igualmente, este mecanismo no está pensado para hacerlo en deltas (los cambios de notas en las últimas 24 horas, por ejemplo), pero esta lista de pasos puede ser la base para definir un mecanismo más optimizado.

1. Instalar Postgres.

Aquí no se explica en detalle este paso, ya que depende del entorno en que se ejecute y hay muy buenos tutoriales en Internet que explican diferentes casos (para Ubuntu). Se recomienda que sea sobre un Linux. La ejecución se hizo sobre Ubuntu 22.04.

Si se está sobre Ubuntu, el servicio se debe subir de la siguiente manera:

sudo systemctl start postgresql.service

2. Crear una base de datos Postgres.

Cuando se instala Postgres, como parte del proceso se crea una DB por defecto. Se recomienda crear una DB dedicada para esto para que se tenga un entorno aislado.

3. Activar la funcionalidad PostGIS.

Para esto se debe descargar el paquete, y crear la extensión como se explica aquí:

https://postgis.net/install/ o http://www.geomapik.com/desarrollo-programacion-gis/como-instalar-postgis-en-ubuntu/

4. Instalar un procesador de archivos GeoJSON hacia Postgres: ogr2ogr

Los archivos de las fronteras de países se obtienen en formato GeoJSON, ya sea desde https://osm-boundaries.com/Map o desde Overpass turbo.

Por lo tanto es necesario poder importar los archivos GeoJSON dentro de la base de datos.

La explicación de instalación de la herramienta está en: https://mothergeo-py.readthedocs.io/en/latest/development/how-to/gdal-ubuntu-pkg.html

El paso desde un Ubuntu es:

sudo apt-get install ogr2ogr

5. Instalar Java y descargar Saxon.

Dependiendo del procesador XML que se use, requiere ciertos programas para su ejecución. Debido a que el XML de las notas es un archivo muy grande, y debido a que se usa sintaxis de XLST 2.0 para convertirlo en un archivo CSV, se usa el procesador Saxon.

Como es un .jar, se requiere instalar un Java Runtime Edition, preferiblemente superior a v11.

El Saxon usado es el HomeEdition y se puede descargar desde SourceForge el archivo .jar. Este no requiere licencia.

6. Crear las tablas del modelo.

Estas son las tablas del modelo que se usan para cargar los datos de las notas.

CREATE TABLE countries (
 id_country SERIAL NOT NULL,
 country_name VARCHAR(20) NOT NULL,
 geom GEOMETRY NOT NULL
);

CREATE TABLE comment_actions (
 action_id SMALLINT NOT NULL,
 description VARCHAR(10) NOT NULL
);

ALTER TABLE comment_actions
 ADD CONSTRAINT pk_comments
 PRIMARY KEY (action_id);

CREATE TABLE users(
 uid INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL
);

ALTER TABLE users
 ADD CONSTRAINT pk_users
 PRIMARY KEY (uid);

CREATE TABLE notes (
 note_id INTEGER NOT NULL,
 lat DECIMAL NOT NULL,
 lon DECIMAL NOT NULL,
 created TIMESTAMP NOT NULL,
 close TIMESTAMP
);

ALTER TABLE notes
 ADD CONSTRAINT pk_notes
 PRIMARY KEY (note_id);

CREATE TABLE comments (
 note_id INTEGER NOT NULL,
 action_id SMALLINT NOT NULL,
 time TIMESTAMP NOT NULL,
 uid INTEGER
);

ALTER TABLE comments
 ADD CONSTRAINT fk_notes
 FOREIGN KEY (note_id)
 REFERENCES notes (note_id);

ALTER TABLE comments
 ADD CONSTRAINT fk_actions
 FOREIGN KEY (action_id)
 REFERENCES comment_actions (action_id);

ALTER TABLE comments
 ADD CONSTRAINT fk_users
 FOREIGN KEY (uid)
 REFERENCES users (uid);

Diagrama del modelo ER creado en la DB.

INSERT INTO comment_actions VALUES
(0, 'unknown'),
(1, 'opened'),
(2, 'commented'),
(3, 'closed'),
(4, 'reopened');

Sentencias de borrado para limpiar la DB, en caso de un problema:

DROP TABLE comments;
DROP TABLE notes;
DROP TABLE users;
DROP TABLE comment_actions;
DROP TABLE countries;

7. Insertar las áreas de los países de Latam.

Como se dijo anteriormente, las áreas se pueden descargar de:

Por medio de Overpass turbo.
Por medio de la página OSM-boundaries.

Cuando se usa OSM-Boundaries, es necesario loggearse, y con esto se habilitará la opción download. Asegurarse que usa la proyección es SRID 4326. Se puede seleccionar el país de la lista, incluir las áreas marítimas y sin simplificar.

Los países que se consideran de la región son los siguientes, con su respectivo nombre de archivo. Sin embargo, no es una lista exhaustiva ni estricta; se pueden agregar otros países:

B-Argentina.geojson
B-Belice.geojson
B-Bolivia.geojson
B-Brazil.geojson
B-Chile.geojson
B-Colombia.geojson
B-CostaRica.geojson
B-Cuba.geojson
B-DominicanRepublic.geojson
B-Ecuador.geojson
B-ElSalvador.geojson
B-Guatemala.geojson
B-Guyana.geojson
B-Haiti.geojson
B-Honduras.geojson
B-Jamaica.geojson
B-Mexico.geojson
B-Nicaragua.geojson
B-Panama.geojson
B-Paraguay.geojson
B-Peru.geojson
B-Suriname.geojson
B-TheBahamas.geojson
B-Uruguay.geojson
B-Venezuela.geojson

Una vez con los archivos, se importan en la base de datos dentro de un ciclo, gracias a la herramienta ogr2ogr.

echo "CREATE TABLE import (
 geometry    GEOMETRY
)" | psql -d postgres
echo "delete from countries" | psql -d postgres
for i in $(ls -1 *.geojson) ; do
 J=${i:2};
 COUNTRY=${J%".geojson"}
 ogr2ogr -f "PostgreSQL" PG:"dbname=postgres user=angoca" "${i}" -nln import -overwrite
 STATEMENT="INSERT INTO countries (country_name, geom) select '${COUNTRY}', wkb_geometry from import"
 echo ${STATEMENT} | psql -d postgres
done
echo "drop table import" | psql -d postgres

8. Descargar el archivo del planet.

Se requiere descargar el archivo más reciente del planet:

https://planet.openstreetmap.org/notes/

El archivo viene con un formato bz2. Puede llegar a ser necesario instalar el programa para extraer este tipo de archivos.

Para extraerlo se ejecuta:

bzip2 -d planet-notes-latest.osn.bz2

Esto extrae un archivo osn, que tiene estructura XML. Por este motivo, lo renombramos como XML para que lo tengamos presente.

mv planet-notes-latest.osn planet-notes-latest.osn.xml

9. Procesar el archivo de notas para insertarlo en la DB.

La forma de insertar los datos de las notas dentro de la base de datos es tomando archivos planos y haciendo una carga en bulk.

Debido a que esto puede fallar, estos son los comandos para borrar lo que esté en la base de datos, que se haya cargado mal.

echo "delete from comments" | psql -d postgres
echo "delete from notes" | psql -d postgres
echo "delete from users" | psql -d postgres

Estos comandos procesan el archivo de notas, y lo convierten en archivos planos. Se usa la funcionalidad XSLT para hacer transformaciones, y convertir los datos en archivos CSV - Separados por comas.

java -Xmx5000m -cp ./saxon-he-11.4.jar net.sf.saxon.Transform -s:planet-notes-latest.osn.xml -xsl:users-csv.xslt -o:output-users.csv
java -Xmx5000m -cp ./saxon-he-11.4.jar net.sf.saxon.Transform -s:planet-notes-latest.osn.xml -xsl:notes-csv.xslt -o:output-notes.csv
java -Xmx5000m -cp ./saxon-he-11.4.jar net.sf.saxon.Transform -s:planet-notes-latest.osn.xml -xsl:comments-csv.xslt -o:output-comments.csv

Para cargar los usuarios, desde el archivo de notas se generó un archivo con muchos duplicados. Pero se cargará en una tabla temporal, y por medio de SQL se inserta solo una ocurrencia en la tabla destino.

echo "CREATE TABLE users_duplicate(
 uid INTEGER NOT NULL,
 username VARCHAR(512) NOT NULL
)" | psql -d postgres
echo "copy users_duplicate from '$(pwd)/output-users.csv' csv" | psql -d postgres
echo "INSERT INTO users SELECT DISTINCT * FROM users_duplicate" | psql -d postgres
echo "drop table users_duplicate" | psql -d postgres

Se cargan los datos de las otras tablas.

echo "copy notes from '$(pwd)/output-notes.csv' csv" | psql -d postgres
echo "copy comments from '$(pwd)/output-comments.csv' csv" | psql -d postgres

Como parte de la tabla de comentarios, como tal no se cargan, ya que no se va a hacer análisis de textos. Si esto se quisiera, sería necesario:

Modificar la estructura de la tabla, para incluir un campo más de texto.
El procesador puede generar registros inconsistentes. Por lo que hay que evaluar si se modifica el XSLT o la forma de insertar. Esto debido a los saltos de línea dentro de los comentarios.

Este es el contenido de los archivos para procesar el archivo de notas, y generar 3 archivos planos:

Archivo: notes-csv.xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
 <xsl:template match="/">
  <xsl:for-each select="osm-notes/note"><xsl:value-of select="@id"/>,<xsl:value-of select="@lat"/>,<xsl:value-of select="@lon"/>,"<xsl:value-of select="@created_at"/>",<xsl:choose><xsl:when test="@closed_at != ''">"<xsl:value-of select="@closed_at"/>"
</xsl:when><xsl:otherwise><xsl:text>
</xsl:text></xsl:otherwise></xsl:choose>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

Archivo: users-csv.xslt

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
 <xsl:template match="/">
  <xsl:for-each select="osm-notes/note">
  <xsl:variable name="note_id"><xsl:value-of select="@id"/></xsl:variable>
   <xsl:for-each select="comment">
<xsl:choose> <xsl:when test="@uid != ''"><xsl:value-of select="@uid"/>,"<xsl:value-of select="replace(@user,'''','''''')"/>"<xsl:text>
</xsl:text></xsl:when></xsl:choose>
   </xsl:for-each>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

Archivo: comments-csv.xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
 <xsl:template match="/">
  <xsl:for-each select="osm-notes/note">
  <xsl:variable name="note_id"><xsl:value-of select="@id"/></xsl:variable>
   <xsl:for-each select="comment">
<xsl:choose> <xsl:when test="@uid != ''"> <xsl:copy-of select="$note_id" />,<xsl:choose>
<xsl:when test="@action = 'opened'">1</xsl:when><xsl:when test="@action = 'commented'">2</xsl:when><xsl:when test="@action = 'closed'">3</xsl:when><xsl:when test="@action = 'reopened'">4</xsl:when><xsl:otherwise>0</xsl:otherwise></xsl:choose>,"<xsl:value-of select="@timestamp"/>",<xsl:value-of select="@uid"/><xsl:text>
</xsl:text></xsl:when><xsl:otherwise>
 <xsl:copy-of select="$note_id" />,<xsl:choose>
<xsl:when test="@action = 'opened'">1</xsl:when><xsl:when test="@action = 'commented'">2</xsl:when><xsl:when test="@action = 'closed'">3</xsl:when><xsl:when test="@action = 'reopened'">4</xsl:when><xsl:otherwise>0</xsl:otherwise></xsl:choose>,"<xsl:value-of select="@timestamp"/>",<xsl:text>
</xsl:text></xsl:otherwise> </xsl:choose>
   </xsl:for-each>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

10. Identificar las notas que son de Latam.

A partir de las coordenadas de latitud y longitud de las notas, es necesario saber si quedan en algún país de Latam. Para esto es que se usan las capacidades GIS de Postgres: PostGIS.

Por este motivo, se insertaron las fronteras de los países dentro de la base de datos, y ahora con las coordenadas de la nota sabremos si una nota está o no en Latam.

Debido a que solo vamos a procesar notas de Latam, vamos a agregar una nota adicional a la tabla de notas, donde indicaremos el código del país.

ALTER TABLE notes ADD COLUMN id_country INTEGER;

Debido a que el llamado para saber si una nota pertenece a Latam requiere varios pasos, se crea la siguiente función:

CREATE OR REPLACE FUNCTION get_country (
  lon DECIMAL,
  lat DECIMAL
) RETURNS INTEGER
LANGUAGE plpgsql
AS $func$
 DECLARE
  country_id INTEGER;
  f RECORD;
  contains BOOLEAN;
 BEGIN
  country_id := -1;
  FOR f IN
    SELECT geom, id_country
    FROM countries 
   LOOP
    contains := ST_Contains(f.geom, ST_Point(lon, lat, 4326));
    IF (contains) THEN
     country_id := f.id_country;
     EXIT;
    END IF;
   END LOOP;
  RETURN country_id;
 END
$func$
;

Ahora, lo mejor es poblar la columna con el id del país. Para eso se hace en fracciones, para ver el avance y no generar una transacción muy grande. Esto se hace primero en un bounding box de toda Latam, con paquetes de 100 mil notas.

for i in $(seq 0 100000 3400000) ; do
 echo ${i}
 echo "update notes
 set id_country = get_country(lon, lat)
 where note_id > ${i}
 and -120 < lon 
 and lon < -30 
 and 33 > lat 
 and lat > -56
 and id_country is null" | psql -d postgres
done

Igualmente se puede procesar las del resto del mundo, para ponerles un -1. Esta parte puede servir para áreas fuera del bounding box, como la isla de Pascua. Esta parte puede tomar bastante tiempo en terminar.

echo "update notes
 set id_country = get_country(lon, lat)
 where id_country is null" | psql -d postgres

11. Ejecutar queries para el análisis sobre las notas.

Con los datos ya procesados en la base de datos, se pueden hacer distintos tipos de análisis.

Se puede conectar Postgresql con LibreOffice para hacer tablas pivote - https://www.uzerp.com/blog/2019/05/06/libreoffice_postgres/

Desempeño por país

Tiempo promedio histórico para cerrar notas.

Aquí se puede ver que Colombia, Ecuador y Cuba no han tenido buenos tiempos de resolución históricamente (Pero en el 2022, gracias a ONL, esto comenzó a cambiar.)

Este query no se usa para un premio o reconocimiento.

select count(1), c.country_name, avg(close - created)
from notes n join countries c on (n.id_country = c.id_country) 
where n.id_country is not null
and n.id_country <> -1
and n.close is not null 
group by n.id_country, c.country_name
order by avg(close - created);

Histórico de resolución de notas, con corte al año anterior (Notas viejas).

En este caso, hasta el 2021 (año pasado) incluido, cuánto se demoraron en cerrarse las notas históricas.

Este query no se usa para un premio o reconocimiento.

select count(1), c.country_name, avg(close - created)
from notes n join countries c on (n.id_country = c.id_country) 
where n.id_country is not null
and n.close is not null
and n.id_country <> -1
and extract(year from n.created) < extract(year from current_date)
and extract(year from n.close) < extract(year from current_date)
group by n.id_country, c.country_name
order by avg(close - created);

Resolución de notas, con la tasa de notas del año en curso.

En este caso, en el 2022, cuántos días se demoraron en cerrarse las notas creadas en el mismo año. Donde se ve que Chile tiene una tasa de 2 días para cerrar más de 2000 notas.

Este query se usa para el premio de "Comunidad con mejor atención a usuarios".

select count(1), c.country_name, avg(close - created), count(1)
from notes n join countries c on (n.id_country = c.id_country) 
where n.id_country is not null
and n.id_country <> -1
and n.close is not null
and extract(year from n.created) = extract(year from current_date)
and extract(year from n.close) = extract(year from current_date)
group by n.id_country, c.country_name
order by avg(close - created);

Cantidad de notas abiertas históricamente por país.

Este query no se usa para un premio o reconocimiento.

select count(1), c.country_name
from notes n join countries c on (n.id_country = c.id_country)
where n.id_country is not null
and n.id_country <> -1
group by n.id_country, c.country_name;

Cantidad de notas en estado abierto actualmente por país.

Este query no se usa para un premio o reconocimiento.

select count(1), c.country_name
from notes n join countries c on (n.id_country = c.id_country)
where n.id_country is not null
and n.id_country <> -1
and n.close is null
group by n.id_country, c.country_name;

Notas cerradas históricamente.

Este query no se usa para un premio o reconocimiento.

select count(1), c.country_name
from notes n join countries c on (n.id_country = c.id_country)
where n.id_country is not null
and n.id_country <> -1
and n.close is not null
group by n.id_country, c.country_name;

Notas aún abiertas por país por año. Importar esto en LiberOffice para hacer una tabla dinámica.

Este query se usa para el premio de "Comunidades con solo notas activas".

select c.country_name country, extract(year from n.created) as year, count(1) qty
 from notes n join countries c on (n.id_country = c.id_country) 
 join comments m on (n.note_id = m.note_id)
 join comment_actions a on (m.action_id = a.action_id)
 where n.id_country is not null
 and n.id_country <> -1
 and n.close is null
 and a.description = 'opened'
 group by c.country_name , extract(year from n.created)
 order by 1, 2;

Países que más notas cerraron en este año.

Este query se usa para el premio: "País comprometido con sus notas".

select c.country_name, count(1)
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
and extract(year from n.close) = extract(year from current_date)
group by c.country_name
order by count(1) desc
fetch first 5 rows only;

Países que más cierran notas del año en curso.

Este query se usa para el premio "Escuchando a sus usuarios".

select c.country_name, count(1) 
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id) 
join users u on (m.uid = u.uid) 
join comment_actions a on (m.action_id = a.action_id) 
where n.id_country is not null 
and n.id_country <> -1 
and n.close is not null 
and a.description = 'closed' 
and extract(year from n.created) = extract(year from current_date) 
and extract(year from n.close) = extract(year from current_date) 
group by c.country_name
order by count(1) desc fetch first 5 row only;

Países sin notas abiertas.

Este query se usa para el premio: "Lista vacía".

CREATE OR REPLACE FUNCTION get_count_by_date_by_country (
 country_id INTEGER,
 date_note TIMESTAMP
) RETURNS INTEGER
LANGUAGE plpgsql
AS $func$
 DECLARE
  qty INTEGER;
 BEGIN
SELECT COUNT(1) INTO qty
  FROM notes n
  WHERE n.id_country IS NOT NULL -- El país es en Latam
  AND n.id_country <> -1
  AND (n.close IS NULL -- La nota no está actualmente cerrada
   OR n.close > date_note)
  AND n.created < date_note
  AND N.id_country = country_id;
  RETURN qty; 
 END
$func$ ;

select c.country_name, n.close, get_count_by_date_by_country(n.id_country, n.close)
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
and extract(year from n.close) = extract(year from current_date)
and get_count_by_date_by_country(n.id_country, n.close) <= 5
order by c.country_name, n.close
;

Países que han llegado a 0 o mínima cantidad de notas (menos de 5).

Este query es usado para el premio: "Lista vacía".

Preparación de datos:

CREATE OR REPLACE FUNCTION get_count_by_date_by_country (
 country_id INTEGER,
 date_note TIMESTAMP
) RETURNS INTEGER
LANGUAGE plpgsql
AS $func$
 DECLARE
  qty INTEGER;
 BEGIN
SELECT COUNT(1) INTO qty
  FROM notes n
  WHERE n.id_country IS NOT NULL -- El país es en Latam
  AND n.id_country <> -1
  AND (n.close IS NULL -- La nota no está actualmente cerrada
   OR n.close > date_note)
  AND n.created < date_note
  AND N.id_country = country_id;
  RETURN qty; 
 END
$func$ ;

ALTER TABLE notes ADD COLUMN previous_opened INTEGER;

UPDATE notes
SET previous_opened = get_count_by_date_by_country(id_country, close)
WHERE id_country IS NOT NULL
AND id_country <> -1;

Ejecución de query:

select c.country_name, n.close, previous_opened
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
and extract(year from n.close) = extract(year from current_date)
and previous_opened <= 5
order by c.country_name, n.close
;

Desempeño por contribuidor

Mayores contribuidores por año - Notero del año.

Esto permite asignar un reconocimiento de "Notero del año". El primer criterio es el que más haya cerrado notas en un año (más de 1000). No se puede repetir este reconocimiento a un mismo usuario.

select u.username, extract(year from m.time), count(1)
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join users u on (m.uid = u.uid)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
group by u.username, extract(year from m.time) having count(1) >1000
order by extract(year from m.time), count(1) desc;

Histórico de mayores contribuidores - Notero del año.

Esto permite asignar un reconocimiento de "Notero del año". Este es el segundo criterio, si en el primer criterio no hay mucha diferencia entre los usuarios, o no hay uno sobresaliente que no haya sido reconocido anteriormente. Por ejemplo, que no haya un usuario diferente a los ya reconocidos y que haya cerrado más de 1000 notas en el año. Por lo que aquí permite escoger a algún contribuidor que haya cerrado muchas notas históricamente, pero que nunca haya sido el mayor cerrador de notas en un año específico, por lo que se le da el reconocimiento por su trayectoria.

select u.username, count(1)
from notes n join countries c on (n.id_country = c.id_country)
join comments m on (n.note_id = m.note_id)
join users u on (m.uid = u.uid)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
group by u.username having count(1) >1000
order by count(1) desc;

El voluntario que más ha cerrado sus propias notas.

Este query sirve para el premio Organizado.

select u.username username, count(1)
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join users u on (m.uid = u.uid)
join comment_actions a on (m.action_id = a.action_id)
join comments o on (n.note_id = o.note_id)
join users ou on (o.uid = ou.uid)
join comment_actions oa on (o.action_id = oa.action_id)
where n.id_country is not null
and n.id_country <> -1
and n.close is not null
and a.description = 'closed'
and oa.description = 'opened'
and u.username = ou.username
and extract(year from n.close) = extract(year from current_date)
group by u.username
having count(1) > 500
order by count(1) desc
fetch first 10 row only;

Voluntario nuevo que cerró más rápidamente 100 notas.

Este query se usa para el premio "Mejor Entusiasta".

select username, max - min Notas_100
from (
select username, min(close_Date) min, max(close_Date) max
from (
select u.username username, n.close close_Date
,Row_Number() Over (Partition By u.username Order By n.close Asc) As Row_Number
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join users u on (m.uid = u.uid)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' -- La nota fue cerrada (pero pudo haber sido reabierta-recerrada)
and extract(year from n.close) = extract(year from current_date)
and u.username not in ( -- El usuario no ha cerrado más de 10 notas en el pasado en Latam.
select username
from (
select u.username username, n.close close_Date
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id)
join users u on (m.uid = u.uid)
join comment_actions a on (m.action_id = a.action_id)
where n.id_country is not null -- El país es en Latam
and n.id_country <> -1
and n.close is not null -- La nota está actualmente cerrada
and a.description = 'closed' 
order by n.close
) t
where extract(year from t.close_Date) < extract(year from current_date)
group by username
having count(1) > 10
)
) r
where Row_Number in (1,100)
group by username
) s
where max <> min
order by max - min 
;

Usuarios que han cerrado en más países.

Este query se usa para el premio "Internacional".

select username, count(1), avg(qty)
from (
select u.username username, c.country_name, count(1) qty
from notes n join countries c on (n.id_country = c.id_country) 
join comments m on (n.note_id = m.note_id) 
join users u on (m.uid = u.uid) 
join comment_actions a on (m.action_id = a.action_id) 
join comments o on (n.note_id = o.note_id) 
join users ou on (o.uid = ou.uid) 
join comment_actions oa on (o.action_id = oa.action_id) 
where n.id_country is not null 
and n.id_country <> -1 
and n.close is not null 
and a.description = 'closed' 
and oa.description = 'opened' 
and u.username <> ou.username 
and extract(year from n.close) = extract(year from current_date) 
group by u.username, c.country_name

having count(1) >= 5 -- Al menos 5 notas por país
order by u.username, c.country_name
) t
group by username
having count(1) > 1 -- En más de 1 país
order by count(1) desc
;

Usuarios que han usado el hashtag del grupo #ONL.

Aplicaciones

Mayores aplicaciones que crean notas por país. Estos son los mensajes por los que se filtran las notas por app:

StreetComplete: via StreetComplete

OrganicMaps: #organicmaps
Maps.me: This is an auto-generated note from MAPS.ME application
Maps.me: #mapsme
OnOSM.org: onosm.org submitted note from a business
Mapy.cz: Forwarding anonymous report from #Mapy.cz

ToDo: Esto se puede hacer con otra columna en la tabla comments, que se llame aplication, y se pobla con el XSLT con la función contains.

Estadísticas

Las notas existen desde Abril de 2013. Para finales de 2022 (momento de los premios y reconocimiento) habrán más de 9 años de antigüedad.
A continuación los rangos de IDs de notas por año y la cantidad de notas creadas por año. Aunque no todas están disponibles, ya que algunas las escoden por temas de privacidad.


Año	Id menor	Id mayor	Cantidad de notas
2013	1	94093	94093
2014	94094	294231	200138
2015	294232	491148	196917
2016	491149	836632	345484
2017	836633	1254172	417540
2018	1254173	1635239	381067
2019	1635240	2042260	407021
2020	2042261	2480421	438161
2021	2480422	2991120	510699
2022	2991121

Ids de notas importantes:
- 1000000 - 2017-05-18T08:41:38Z
- 2000000 - 2019-11-18T12:32:07Z
- 3000000 - 2022-01-06T22:23:42Z

Definiciones

Notas viejas: Notas creadas el año pasado (normalmente, con más de 1 año de antigüedad) y que aún no se han cerrado.
Notas recientes: Notas creadas en el año en curso.
Nota propia: Nota que fue abierta y cerrada por el mismo usuario.
Cerrado, Resuelto, Procesado: Son sinónimos para el hecho de leer el comentario y hacer o no un cambio en el mapa.
Crear, Abrir: Hecho de crear una nota en OSM.
Estado abierto: Nota que para un punto dado en el tiempo, sigue aún abierta, pero pudo haber sido creada en cualquier momento atrás.

Reconocimientos, badges y premios

Reconocimientos

Notero del año. (Listo)
- Se le otorga a un usuario una única vez.
- Tiene que haber cerrado más de 1000 notas en un año o tiene que haber cerrado muchas notas históricamente.

Premios

País

Comprometido con sus notas. (Listo)
- El país qué más notas cerró este año con respecto a las que estaban abiertas (abiertas este año y años pasados.)
Escuchando a sus usuarios. (Listo)
- Los países que más están cerrando notas recientes (del año en curso).
Solo notas recientes. (Listo)
- A partir de "Notas aún abiertas por país por año".
- Solo hay notas abiertas creadas el año en curso
- Con la tabla dinámica se podrá ver qué países han cerrado todas las notas viejas (de años pasados).
Lista vacía. (Listo)
- Países que llegaron a 0 notas abiertas.

Mejor atención a usuarios. (Listo)
- A partir de "Resolución de notas, con la tasa de notas del año en curso."
- Menos de 15 días en promedio para resolver notas recientes.
- Esto permite que a los usuarios del mapa se les procese la retroalimentación haciendo un cambio en el mapa.

Voluntario

Organizado. (Listo)
- Los voluntarios que han cerrado sus propias notas, con más de 500 notas.
Mejor Entusiasta. (Listo)
- Haber cerrado 100 notas este año en el menos tiempo. No puede haber cerrado más de 10 notas en años pasados.
- Promueve a los nuevos miembros de ONL.
Multinacional. (Listo)
- Haber cerrado al menos 5 notas en más países.

Badges

Excavador - Cerrar más de 10 notas de hace más de 6 años.
Arqueologista - Cerrar más de 100 notas de hace 6 años
Epic - Resolver más de 100 notas de menos de 1 día de creadas.
Legendario - Resolver más de 500 notas de menos de 1 día de creadas.
Yearly - Haber cerrado notas durante más de 5 años
Civic duty - Haber reabierto más de 50 notas.
Sherif - Haber participado en una nota con más de 10 comentarios.
Internacional - Haber cerrado notas en más de 5 países.

Talkative - Haber cerrado más de 100 notas dejando mensaje de cerrado diferente.
Disciplinado - Haber cerrado más de 100 notas con el hashtag del equipo #ONL.

Referencias

Badges de StackOverFlow - https://stackoverflow.com/help/badges
Badges de MissingMaps - https://www.missingmaps.org/users/#/angoca/badges
Badges OSM Communities - https://community.openstreetmap.org/badges
Badges de StreetComplete - User:Westnordost/StreetComplete/Achievements
Badges de GitHub - https://github.com/drknzz/GitHub-Achievements

ES:LatAm/Proyectos/Resolución de notas/Preparación premios

Contents

Procesamiento de notas del Planet para análisis en base de datos.

Lista de pasos

1. Instalar Postgres.

2. Crear una base de datos Postgres.

3. Activar la funcionalidad PostGIS.

4. Instalar un procesador de archivos GeoJSON hacia Postgres: ogr2ogr

5. Instalar Java y descargar Saxon.

6. Crear las tablas del modelo.

7. Insertar las áreas de los países de Latam.

8. Descargar el archivo del planet.

9. Procesar el archivo de notas para insertarlo en la DB.

10. Identificar las notas que son de Latam.

11. Ejecutar queries para el análisis sobre las notas.

Desempeño por país

Desempeño por contribuidor

Aplicaciones

Estadísticas

Definiciones

Reconocimientos, badges y premios

Reconocimientos

Premios

País

Voluntario

Badges

Referencias

Navigation menu

ES:LatAm/Proyectos/Resolución de notas/Preparación premios

Procesamiento de notas del Planet para análisis en base de datos.

Lista de pasos

1. Instalar Postgres.

2. Crear una base de datos Postgres.

3. Activar la funcionalidad PostGIS.

4. Instalar un procesador de archivos GeoJSON hacia Postgres: ogr2ogr

5. Instalar Java y descargar Saxon.

6. Crear las tablas del modelo.

7. Insertar las áreas de los países de Latam.

8. Descargar el archivo del planet.

9. Procesar el archivo de notas para insertarlo en la DB.

10. Identificar las notas que son de Latam.

11. Ejecutar queries para el análisis sobre las notas.

Desempeño por país

Desempeño por contribuidor

Aplicaciones

Estadísticas

Definiciones

Reconocimientos, badges y premios

Reconocimientos

Premios

País

Voluntario

Badges

Referencias

Navigation menu

Search