OpenStreetMap logo OpenStreetMap

qeef's Diary

Recent diary entries

The fifth year of the Divide and map. Now.

Posted by qeef on 1 January 2025 in English.

This is the developer’s diary concerning the fifth year of the damn project – the project that helps mappers by dividing a big area into smaller planar shapes that people can map together.

The damn project is currently used by few mappers as a tool for tracking personal mapping. (Does it make it a competitor to the SimpleTaskManager?)

In 2024, we had some problems:

  • We had performance issues when working with more than 300 000 squares in a single area; we solved these issues by pagination and forbidding creation of areas with more than 10 000 squares.

  • The damn JavaScript clients confuse mappers and we need to be serious about it; we will address this issue in 2025, but it’s not yet decided how. We are open to ideas!

2024 did not bring much to the damn project, though there are some highlights:

  • Probably no suprise that we moved to OAuth2. The amount of work is -21 lines of code. We welcome such changes.

  • I wrote Unofficial design documents for HOT Tasking Manager.

    The original intent of the damn project was to show that HOT Tasking Manager (HOT TM) can be done better. To help HOT TM developers, last year I wrote Unofficial design documents for HOT Tasking Manager. There is (obviously) more reasoning and comparison than in the damn server dev doc, but I don’t think it will help anyway. There is too much work and I saw the HOT TM source code. It’s unmaintainable, in my opinion.

  • We implemented enforce divide to squares function when creating new area.

    If the (area’s GeoJSON)’s FeatureCollection has member 'name' then divide to squares function is NOT used. In such a case it’s expected you already divided the area – this is for compatibility reasons with MapSwipe. enforce divide to squares overrides this behavior, enforcing the division of the area to squares even if the 'name' member is present in the GeoJSON’s FeatureCollection of the area.

And finally, the plan for 2025 – there are two main things:

See full entry

The fourth year of the Divide and map. Now.

Posted by qeef on 2 January 2024 in English.

Welcome to the summary of work done in the fourth year of Divide and map. Now. – the damn project that helps mappers by dividing a big area into smaller squares that people can map together.

Four years ago, the damn project was developed to constructively criticize the HOT Tasking Manager. (HOT stands for the Humanitarian OpenStreetMap Team.) I see that the limitations of the HOT Tasking Manager persist, that it is not getting better, and that the developers of the HOT Tasking Manager can still benefit from constructive criticism.

In this diary, I will first recap the functionality and scope of the damn project. Then I will write about the work in 2023 and the work for 2024.

Functionality and scope

To understand the damn project, let us break it down into four main components, each of which has its own repository: the server, the web clients, the JOSM damn plugin, and the deployment guide. The deployment (for sysadmins) brings up the damn project. The damn server (for backend developers) contains the core service – JSON API to the PostGIS database. The JOSM damn plugin connects to the damn server so that mappers do not have to open the web browser to contribute to OpenStreetMap when using the damn project. Finally, the web clients repository (for frontend developers) contains the code base for multiple web clients.

The features available to mappers are divided into the JOSM damn plugin and the web clients, as the damn project serves multiple groups of mappers and recognizes these groups.

See full entry

Dive into the HOT Tasking Manager codebase

Posted by qeef on 20 December 2023 in English.

This diary is about programming, as my diaries usually are. This diary is about the code, particularly about backend codebase of the HOT Tasking Manager (TM). TL;DR of this diary is: HOT TM code is unmaintainable mess and that will not change. HOT TM developers inherited something they can do nothing about. Please, prove me wrong if you can and end up my frustration.

I will try to find out why Error when trying to split tasks happens, so deep breath and dive.

I suspect backend, so I will omit the note that it could be replicated on chrome browser only. And I will start directly at TasksActionsSplitAPI of backend/api/tasts/actions.py file, because that looks reasonable.

In the first try block I need to find out what is that SplitTaskDTO. I always forgot what “DTO” means. The import line helps and looking into backend/models/dtos/grid_dto.py says it is “DTO used to split a task”. Thanks. But the code looks like the data structure so I merely remember the purpose – just data or alike.

Only the second try left, so let’s see. The comment on the first line says it checks the project exists. I am just curious how it’s implemented, so see backend/services/project_service.py to find out exists method. Side note – I completely misunderstand classes with static methods only. Oh, exists is just Project.exists. How is that implemented? Just curious… See backend/models/postgis/project.py to see that exists is… it looks like a database query. I don’t SQLAlchemy, but note the first database request. I will number them, this one being #1.

We are back at the second try. It looks the main work is done in split_task of backend/services/grid/split_service.py, so see that (static again) method. Well, heh, not funny anymore. But I am not going to give up easily this time. So split_task now.

See full entry

Damn load testing for the third time

Posted by qeef on 3 September 2023 in English.

The damn deploy repository of the Divide and map. Now. has been refactored. And that’s a great opportunity for another round of load testing.

This is the third round of load testing, see the first and the second one if you are interested.

The load testing is a bit different from the last time. I performed load testing of new, freshly deployed damn project instance on $6/month VPS with 1 GB RAM, single 2.5 GHz vCPU, and 25 GB SSD. (The changes from the last time are that there is no more load testing of the “production” server, the price increased by $1/month, and shared_buffers is now 256 MB instead of 409 MB.)

The preparation for load testing on the server’s side, when the damn project is deployed, is just to run

docker-compose -f damn-deploy/gen.yml run --rm prepareloadtest

to create 1000 test users and 10 (load) testing areas in the database. For each run of load testing, the database has been deleted and created again with

systemctl stop damn-http.service
reboot
docker volume rm damn-deploy_damndb-volume
systemctl start damn.target
docker-compose -f damn-deploy/gen.yml run --rm prepareloadtest

commands. For each run of load testing, log the server’s utilization with

sar -o load-test-100 -A 15 $((4 * 61)) 1>/dev/null 2>&1

Then, from that file, you can generate data series and plot the graphs with

./get-info.sh 100
gnuplot plot1.pl
gnuplot plot2.pl

where the content of the corresponding files is

get-info.sh:

See full entry

Divide and map. Now. deploy refactored

Posted by qeef on 18 August 2023 in English.

This diary is about how the Divide and map. Now. is deployed.

Divide and map. Now. consists of multiple parts like server, JavaScript clients, JOSM plugin or web page. Each part is clearly separated and has its own repository. All are integrated within the damn-deploy, which has its own repository, too.

The dataflow of HTTP services

I am not a sysadmin. When I worked on the refactor deploy, I kept Docker and Systemd and Debian. I simplified dockerfiles, removed unnecessary ones, removed unused SQL code, added systemd units and rewrote the “How to deploy” and “How to upgrade” in the README.

I added damn-www-template with the hugo static site generator, simple blogging theme and an example content. If you care to run your own instance, you may get inspired. But don’t get limited – only a dockerfile serving your web page is expected.

See full entry

Missing Maps Mapathons Core Team

Posted by qeef on 18 July 2023 in English.

We have organized mapathons in Prague. We planned them, met on site, trained new mappers, mapped something and went to the pub. There we discussed and planned and exchanged our ideas. We called ourselves the core team. (I am only writing in the past tense because I am not there anymore; there is actually still a core team organizing mapathons in Prague.)

I was involved in Missing Maps CZ & SK from 2016 to 2020. I was involved in organizing mapathons. During that time I wrote and still maintain the mapathoner plugin. I like free software and I know that it is different from open source. I lobbed for openness. In 2020, I published Divide and map. Now. – the damn project and still maintain it. In 2022 I wrote simple hot intersecting areas and have not updated it since.

I am not sure if it’s time for this diary, but I want to write down my opinion about the core team and the community, because we have been talking about the community and the core team all along. And in my opinion, these two terms are often misunderstood.

DISCLAIMER: These are my own views. Please read this diary accordingly. I am in no way affiliated with OSMF, Missing Maps, HOT, MSF or the Red Cross.

Have you heard of WHAT, WHY, HOW and WHO questions? This is one of my favorite ways to discuss things.

Missing Maps’ WHAT and WHY are clear from their website. Let me put it another way: map the (vulnerable people of the) world because it’s a good thing. WHO is the community, the mappers. HOW is decided by the core team.

See full entry

Let’s recap some work done on the damn project.

Divide and map. Now. – the damn project – helps mappers by dividing a big area into smaller squares that people can map together.

As outlined in Work for 2023, I have been working on the improvements to the web clients. The consequences are better clients and easier deployment (which is not yet documented). Also, I have restructured and slightly rewrote the https://damn-project.org/ web page.

Changes to the web clients

New client for beginner mappers is out, see mapper. I was thinking of how to better describe map-review-done workflow. The original client has “Show mapping square workflow diagram” showing ASCII art square’s state flow when clicked. I had and idea to show SVG figure (generated by dot) instead of ASCII art, because it is easier to generate, maintain, and translate. SVG is text, too, so I can bare that. But wait! SVG is HTML element, isn’t it? So it’s clickable, isn’t it? So it can be done interactive, can’t be?

Mapper web client of the damn-project.org

See full entry

The third year of the Divide and map. Now.

Posted by qeef on 1 January 2023 in English.

The damn project helps mappers by dividing some big area into smaller squares that a human can map. This diary is about the work done in the third year since the publication.

And, to be honest, not much has been done. I had a little of time this year. Still, there are some interesting improvements.

Deployment and server

I will start with probably the most boring stuff: I worked on the documentation and tests. That is thankless work, but I believe it pays off in the longer term. In short – 35 files changed, 1675 insertions(+), 842 deletions(-) and you, as mapper, should not see the difference before/after.

In parallel, I worked on the refactoring deploy. I have moved some upkeep procedures already and I will slowly continue the work.

Notathons

The most motivating for me, I think, is a feedback with a request like “hey, we are working on this and we need that”. This time it was from guys organizing notathons. We improved the damn plugin for JOSM to download notes automatically and periodically. Also, when using the plugin, the changeset comment is (finally!) automatically set based on the area information.

Get inspired by issues of similar projects

I wrote already about damn project point of view on some issues. From that diary, I think that this issue is solved by option to Review newbie, or Map or review work of other mappers workflow.

Because there are not many issues with the damn project (I do not complain!) I sometimes look up interesting issues somewhere else. So, what is there?

First two here and here deal with locking of multiple tasks (squares in damn) for mapping or validation (review in damn). Locking of multiple squares goes against the principle of “divide and map” and therefore it is problematic, but there are valid use-cases. When you need to map multiple squares, merge them first (in mappy client). Do the same with the squares you want to validate, but it is probably better idea to set which mapper in the damn plugin for JOSM instead.

See full entry

The inspiration for this diary comes from the email sent to the HOT mailing list. I must say that I’m not involved in Missing Maps anymore, so I don’t currently use the HOT Tasking Manager (TM). Also, I’m the author of the competing project. That’s the disclaimer.

I will start with the point since when the HOT Tasking Manager became unacceptable for me. That was when mappers began to be forced to provide their email addresses. The reason was: just 4% of the mappers shared it.

The purpose of a Tasking Manager is to divide big area into smaller squares that a human can map. Then, let mappers communicate on what they are working on by changing the states of the squares. So, TM helps with a group mapping management.

However, the changes go to the OpenStreetMap. You don’t have to use Tasking Manager to update OpenStreetMap, but you (must) use OpenStreetMap when working with a Tasking Manager. OpenStreetMap itself provides communication channels for mappers, particularly changeset discussions and private messages.

Changeset discussion is used to discuss changes mappers do in the OpenStreetMap. Private messaging is used to send direct messages between mappers. In both cases a notification is sent by email.

We are almost there. So, why is the communication within the HOT Tasking Manager wrong? Because the HOT TM duplicates the communication about things it does not manage – changes to the OpenStreetMap. Because it allows group and automated messages/emails that are, by definition, depersonalized. Because it confuses beginner mappers about which communication channels are really important.

I would like to end with a proposal for the HOT Tasking Manager developers. Please, keep the functionality of the HOT Tasking Manager non-overlapping with the OpenStreetMap. Please, do leverage OpenStreetMap for the rest.

This diary post is inspired by the cleaning up after a task manager task. It shows how to do the clean-up steps for a Divide and map. Now. area.

The data quality matters. The proposal in the Johnwhelan ‘s diary is to run duplicated building script and JOSM validator when an area is finished on the whole area. The rest of his diary deals with how to get the area’s border geometry and the OpenStreetMap data into the JOSM.

Here are the steps to download the area’s geometry and the OpenStreetMap data when using the damn project:

  1. Load the area’s geometry by navigating to the area in the JOSM damn plugin, then click Get area geometry button.

    It’s also possible to navigate to the area in the mappy (web) client, right click on arbitrary square and download area geojson, and open the downloaded file in the JOSM. It’s good idea to right click on the created area.geojson layer and Convert to GPX layer to make it read-only, which is the same result as when using the JOSM damn plugin.

  2. Download the data from Overpass API, which is the second tab on Download map data … dialog. You can get the Overpass query by navigating to the area in the mappy (web) client, right click on arbitrary square and area overpass query. Then copy the query to the JOSM’s Download from Overpass API, Overpass query: field and click Download. Be sure you have enough RAM for big areas.

That’s all. It’s kind of fun I’m writing this diary just few days after my damn project developer’s “annual report”, but I didn’t make it sooner.

The second year of the Divide and map. Now.

Posted by qeef on 1 January 2022 in English.

It’s two years since the Divide and map. Now. has been published. I would like to summarize the second year of the development.

What is it about? Divide and map. Now. – the damn project – helps mappers by dividing some big area into smaller squares that a human can map.

Why should I care? Divide and map. Now. is proven to handle a mapathon with 200 mappers. There are four clients available for mappers and multiple mapping workflows. There is deployment guide for admins. You may create new or modify existing areas, use RSS to track areas’ changes, and check abandoned or overlapping areas by Python3 scripts.

In 2021 I’ve refactored the server and load tested it. There is the API documentation that is stable for more than half a year now. The web clients were also refactored: the light is text-only web client for beginners, the panel is for advanced mappers and looks like it’s integrated into the iD editor, and the mappy web client has square-based graphical interface. The web clients include improved statistics that can show OpenStreetMap contributors that haven’t used Divide and map. Now. when mapping in the same area. The damn JOSM plugin was updated to the new API and loads temporary data stored in the server when available.

I’ve got inspired by the Tanga Building Footprints Import, mapping highways’ radars and mirrors, and documented the mapping workflows available in the different clients.

I’ve implemented Python3 client with the scripts to find abandoned or intersecting areas based on the potential HOT tasking manager improvements and looked at the competing HOT Tasking Manager and SimpleTaskManager issues from the damn project point of view.

I’ve created the finished areas read-only service and announced the policy for finished areas.

See full entry

HOT Tasking Manager (TM), SimpleTaskManager (STM), and Divide and map. Now. (damn) are tools for collaborative mapping, with different philosophies and different approaches. In this diary, I discuss some issues of the first two from the perspective of the third one.

DISCLAIMER: I’m the damn project developer.

Which issues to consider: I filter out bug reports and issues that oppose the damn project philosophy. Then, I pick up issues I think are interesting and categorize them, describe the category, and provide some comments how the particular issues are or would be solved.

About naming: TM’s and STM’s project corresponds to damn’s area. TM’s and STM’s task corresponds to damn’s square. Where TM uses validation, damn uses review.

Solved by design

There are many issues of both managers that wouldn’t be an issues for the damn project because of it’s design. Let’s quickly recap that design.

There are areas divided to squares. Any change to an area is stored as commit. So, creating new area leads to creating new squares as well as new commit with to map type for each square. During collaborative mapping, new commits of different types like locked or done are added with area and square identifiers, always updating particular square of the area. It’s not possible to delete something.

See full entry

The idea to map radars and mirrors of the Czech highways was raised in the mailing list of the Czech community. One of the comments is questioning about how to track the work done–and that’s exactly the problem to be solved by the damn project. (Note that the idea to map radars and mirrors of the Czech highways is still just an idea and there are no further steps done yet I’m aware of.)

I already wrote two Get inspired by … diaries. These were the potential HOT tasking manager improvements and the Tanga Building Footprints Import. This diary discuss the use of the damn project for tracking the work done on highways.

Create area

The damn project helps mappers by dividing some big area into smaller squares that a human can map. How this applies to highways?

It’s possible to upload two kinds of GeoJSON boundary files to the damn manager. If the FeatureCollection has member ‘name’ then divide to squares function is NOT used. Moreover, I’ve updated the damn project to understand what to do with a feature containing a LineString geometry: create a “square” with the border around the LineString. (“Square” here means a square of an area of the damn project.)

So… when you download some highway=motorways within some interesting bounding box (I mean from overpass API in JOSM,) save as GeoJSON, and add name member, you are able to upload that as GeoJSON boundary file to the damn manager.

Note that I experimented with joining ways to make larger squares of an area. The JOSM damn plugin then refused to automatically download the OSM data of a square because the square was too large, and the data had to be downloaded manually.

Mapping

The mappy client may help to visualize the area. An example is area 2352 and it looks way better with background image switched off. The JOSM damn plugin, light client, or panel using map random square/map nearest square are better options, though.

See full entry

As I’m not yet sure what will be included in the alpha version of the damn project, I’m getting inspired. This time by Tanga Building Footprints Import announced on the imports mailing list.

It looks like the buildings are already imported in the Tanga city, e.g. this one, but I’ve never done the import thing, so I can’t say.

In this diary, I’m going to introduce the example mapping workflow with the prepared data based on Tanga Building Footptrints Import using the damn project. Not everything is yet fully automated, so this diary is more like describing the proof of concept. However, I’m open to the needs of the future.

Create area

There is the dataset of the buildings available on the wiki page. I like that idea of huge GeoJSON file that includes the features with the corresponding geometries. As I’m not the import guy, I’m confused with _key_s in the properties (I mean these underscores of each key.) I need only building:yes tag, so I’ve renamed just those (note that notepad’s “Replace All” would do the same:)

sed -i 's/_building_/building/' buildings.geojson

I’ll create new area to work on. Therefore, I need the geometry of the whole area, where the buildings are. For that, I’ll use the damn-client.py scripts. (The shapely must be installed.)

cd damn-client.py
./convex_hull.py buildings.geojson > ch-buildings.geojson

The command took about a minute on my laptop. The ch-buildings.geojson can be used as the GeoJSON boundary file in the damn project’s manager.

Buildings of the squares

So I’ve area to work on divided to squares. Now, I need the GeoJSON files containing all the buildings of each square. I’ll use the damn-client.py scripts once again:

cd damn-client.py
./prepare_tmp.py https://server.damn-project.org 2351 buildings.geojson

See full entry

This diary is about load testing. I find load testing useful mainly because of two reasons: 1. it shows multiuser access problems; 2. it shows how good is the implementation.

The idea is to simulate mapathon. A mapathon is an event where multiple mappers map the same (big) area. To manage the work, the area is divided into smaller squares. Then, each mapper asks the server to map or review a square, works some time on the square, and finally asks the server to mark the square as needs mapping, needs review, or is done.

I use locust.io to load test the damn server. The test file is part of the repository. There is 80 % of mappers, 20 % of reviewers, and 2 testing areas. Each mapper/reviewer works for 30 - 60 seconds, then waits for 30 - 60 seconds, and then works again. All the mappers are spawned within the first minute. The test is stopped soon after there is 0 % to map for both of the areas.

damn-project.org runs on $5/month VPS with 1 GB RAM, single 2.2 GHz vCPU, and 25 GB SSD disk.

100 mappers handled The first round of load testing gives the idea about the server after the refactoring. The average response time for 100 mappers is 270 ms. Not bad, I would say.

It’s a bit worse when testing 200 mappers, though. I’ve stopped the test after 20 minutes.

Some improvements Two years ago, I decided that there must be some better solution for the problem of dividing a big area into smaller squares. I found that there is PostGIS extension to PostgreSQL so I decided to go with it. Two year later (now,) I’ve discovered database indexes.

Also, I’ve read part of the documentation and set postgres parameters.

Finally, I switched gzip on.

See full entry

In the beginning of the year, I’ve been refactoring the Divide and map. Now.. I’ve already wrote about the clients and about the improvements. In this diary, I would like to share some details about how the server looks like now.

The damn project distributed architecture is depicted below. I’ll write about the api and db bubbles, where db is the PostgreSQL database with the PostGIS extension and the api is the python FastAPI application.

damn project architecture

Damn server API

Automatically generated documentation of the API is useful for the developers of clients. There are some not so much interesting endpoints dealing with authentication and authorization, users updates, or squares GPXs. I will elaborate on /areas, /geometries, /area/AID, /area/AID/commits, and /area/AID/squares endpoints here.

See full entry

I liked Pete’s diary at the first sight. However, when published, I was already decided about my next work. Last Thursday I finally found some time to look closer at this interesting topic. I spent night working on it. And I will share the results in this diary.

First, I will not write about the HOT tasking manager. Second, these are my opinions. Third, understand damn in the following text as the shortcut of Divide and map. Now.

The summary of my understanding of the original diary follows. I’ll append my thoughts, though. I made a few toots during the night, which can serve to track the time effort.

What is TM?

Questions:

  • What the TM is?
  • What mapping process is?
  • What is the origin of a project?
  • What is the ownership of a project?
  • For who is TM for?

Problems:

  • New mappers don’t understand (because they don’t have answers.)
  • New mapper edits frustrate old mappers.

Consequences:

  • Discourage new mappers.
  • Learning process interruption.

Thoughts:

The damn project has advantage here, because the name says exactly what the project does. Using areas, squares, and commits notation improves the clearness, too.

As there are multiple clients, each of them targets different group of mappers. Therefore, it’s easier to improve client for newbie mappers without limiting the experienced ones.

Each commit to the area is stored and shown when requested (statistics page,) improving the understanding of the area history.

Project creators

Original problem:

  • When TM was opened, the data quality was low due to poor project description.

Solution:

  • Onboarding process. Making sure that managers fulfill the expectations of having skills to:

    • engage local communities and contributors,
    • respond the questions and info requests,
    • ensure the documentation of the project is on the wiki (in the case of organised editing,)
    • ensure that standards are met,
    • ensure that the mappers are able to do what requested.

See full entry

The state of the mapper's clients for the damn-project.org

Posted by qeef on 12 March 2021 in English. Last updated on 15 March 2021.

I’ve been refactoring the Divide and map. Now. last two months. (No surprise, I’ve plan it.) The most beautiful work was done on the server and the database. I will cover that in more technical diary later.

In this diary, I want to summarize how the clients look like now.

Client + panel

Lightweight
client

The (lightweight) client keeps with the common mapping workflow: Map -> Review -> Done. It’s meant to load fast with the minimum data transfer. It tries to avoid rushed clicks of newbie mappers by putting the phrases of “needs more mapping”, “is ready for review” or “split the square” into the sentence. Newbie mappers may click “let reviewers know” as I’ve already wrote.

See full entry

The first year of the Divide and map. Now.

Posted by qeef on 1 January 2021 in English.

Divide and map. Now. helps mappers by dividing some big area into smaller squares that a human can map.

It’s been a year already since I announced the damn project, so it’s time to summarize the first year of development.

For mappers

I finally managed to make the manager manager-friendly. There is news for the client, too. Along with mapping random or recent squares, a mapper can choose to map the nearest square. Moreover, reviewers can review a newbie square.

I added background OpenStreetMap for the squares. When you click on a square, you may lock that square manually. When you lock multiple squares, you can merge them.

A mapper can share the link to an area or a square. A mapper can download the client and open it on the computer (saving 96KB of bandwidth next time.)

I fixed bugs of the damn plugin reported by #OSMWorldDiscord guys (Thanks again!) and added the “lock & open in JOSM” link to the client.

I wrote a new client. Wondering, how the integration without integration to iD editor looks like? Open the panel and check it out.

For communities

Divide and map. Now. has a separate repository for the deployment. You need to change seven rows in the config files (five rows with security and OpenStreetMap OAuth tokens, one row with the domain, one with the email address) and create one empty file. And you are ready to go.

I was wondering what is missing in the basic setup. I mean – the minimum you need to run is the server. You probably want a client and manager, too. That’s all. You know – the damn project helps mappers …

However, you are part of a community changing the world. The client and manager are OK, but you probably want to tell others about the community.

See full entry

Divide and map. Now. -- square locking policy

Posted by qeef on 12 December 2020 in English. Last updated on 11 March 2021.

I am writing (again) about the Divide and map. Now. The idea of the project is to divide up a big area to organize mapping better. In this note, I will share how I was thinking about the mapping workflow from the beginning, how I finally improved it, and how I hesitated but broke the workflow in the end. And I am sure I did right.

The mapping workflow

The workflow copies a mapathon. Many guys are mapping one area. Few of them are reviewing the work of colleagues. They are locking squares of the area to avoid rewriting the data under their hands.

Since the beginning, it’s possible to lock random or recent square. Mapping guys lock random squares. The reviewing guys lock recent ones to provide feedback to the mappers as soon as possible.

Implementation details
----------------------

Feel free to skip code notes if you are not interested in the code.

There is some background I need to share before showing the SQL query: 

- The server is RESTful.
- Changes to squares work the same as git commits.

(It means that the first commit of any square is `to map`. Then mapper locks a random square adding the `locked` commit to the database. When finished, the mapper adds the `to review` commit. Then the `locked` commit is added again by a reviewer who finally adds the `done` commit.)

The important thing is that no one can delete a commit. Just add. Also, there is no square state. Square consists only of area identifier, square identifier, and border. If you need to know the square's "state," you need to look at the square's last commit.

Finally, the query to map random square is:

    WITH last AS (
	SELECT DISTINCT ON (sid) cid, sid, aid, type, author
	FROM current_commits
	WHERE aid=$1
	ORDER BY sid, cid DESC
    ),
    tomap AS (
	SELECT *
	FROM last
	WHERE type='to map'
	ORDER BY RANDOM()
	LIMIT 1
    )
    INSERT INTO current_commits (sid, aid, author, type, message)
    SELECT sid, $1, $2, 'locked', 'I am mapping'
    FROM tomap
    RETURNING sid

See full entry