Serverová část the damn project

Jiří Vlasák

Co vlastně řešíme?

Problém

Související otázka

Řešení už máme

(Řazeno podle data prvního příspěvku do repozitáře se zdrojovým kódem.)

Ostatní, co jsem našel, jsou deprecated.

Divide and map. Now.

The damn project helps mappers by dividing some big area into smaller squares that a human can map.

Architektura

Architektura the damn project

Oblast k mapování

Architektura databáze 1/2

current_areas

current_squares

Map -> Review -> Done

Úprava oblastí k mapování

Architektura databáze 2/2

current_commits

SQL pro Map -> Review -> Done

Budu mapovat

Provedu review

WITH last AS (
    SELECT DISTINCT ON (sid) cid, sid, type
    FROM current_commits
    WHERE aid=$1
    ORDER BY sid, cid DESC
),
toreview AS (
    SELECT *
    FROM last
    WHERE type='to review'
    ORDER BY cid DESC
    LIMIT 1
)
INSERT INTO current_commits (sid, aid, author, type, message)
SELECT sid, $1, $2, 'locked', 'Working on review'
FROM toreview
RETURNING sid

Nejbližší square:

toreview AS (
    SELECT sid, cid, type
    FROM last, mine
    WHERE type='to review'
    ORDER BY ST_Distance(mine.border, last.border)
    LIMIT 1
)

square nezkušeného mappera:

toreview AS (
    SELECT sid
    FROM last
    INNER JOIN users
    ON (last.author=users.display_name)
    WHERE type='to review'
    AND (info::json->>'newbie')::timestamp > now()
    ORDER BY RANDOM()
    LIMIT 1
)

SQL pro Map -> Review -> Done

Po mapování rozhoduji, jestli

A po review

SELECT * FROM current_commits
WHERE aid=$1 AND sid=$2
ORDER BY cid DESC
LIMIT 1

Je poslední commit locked? Je autor posledního commitu autor právě přidávaného commitu?

INSERT INTO current_commits(sid, aid, author, type, message)
VALUES ($1, $2, $3, 'done', $4)

Kde použít PostGIS

Map -> Review -> Done

Vytváření area

Rozdělím area nebo square

Funkce st_divide napsaná v pgsql.

...
RETURN QUERY
WITH foo AS (
    SELECT
        ST_Translate(
            ss_def,
            ST_XMin(ar) + i * dx,
            ST_YMin(ar) + j * dy
        ) AS ss
    FROM
        generate_series(0, cntx) AS i,
        generate_series(0, cnty) AS j
)
SELECT
    ST_Intersection(ss, ar)
FROM
    foo
WHERE
    ST_intersects(ss, ar)
    AND (
        ... ST_Polygon or ST_MultiPolygon or closed ST_LineString ...
    )
;
...
INSERT INTO current_squares (sid, aid, border)
SELECT row_number() OVER () as sid, aid, sq
FROM
    just_saved_area,
    (
        SELECT st_divide(input_area_geometry) as sq
        FROM input_area_united
    ) as foo
RETURNING sid, aid

Nový square ze všech mnou zamknutých

...
to_be_merged AS (
    SELECT border
    FROM current_squares cs, locked lo
    WHERE cs.sid=lo.sid
    AND cs.aid=lo.aid
),
merged AS (
    SELECT ST_UNION(border) as mb
    FROM to_be_merged
)
INSERT INTO current_squares (sid, aid, border)
SELECT f.msid + row_number() OVER () as sid, f.maid as aid, mb
FROM
    (
        SELECT MAX(cs.sid) as msid, MAX(cs.aid) as maid
        FROM current_squares cs, locked lo
        WHERE cs.aid=lo.aid
    ) as f,
    merged
RETURNING sid, aid

Shrnutí the damn project

Demo

Otázky