В моей стране предстоящие выборы, и я являюсь членом руководящего органа одной из новых партий. Поскольку мы уделяем большое внимание технологиям (и электронному управлению), наши внутренние операции также получают выгоду от некоторых навыков в области ИТ. Особая задача, стоящая перед нами в эти дни, состояла в том, чтобы распределить несколько волонтеров дня выборов (которые помогают наблюдать за честным процессом выборов) на избирательные участки. И я думаю, что это интересная техническая задача, поэтому я попытаюсь объяснить процесс.
Первое — источники данных. У нас есть онлайн-форма для сбора запросов добровольцев. Во-вторых, у нас есть местные координаторы, которые собирают декларации добровольцев и отправляют их централизованно. Сбор всех данных проблематичен (на данный момент), потому что заполнение онлайн-формы не делает вас подходящим — вы также должны отправить бумажную декларацию в центральный офис (ужасная бюрократия).
Тогда есть предпочтения добровольцев — в форме, которую они заполнили, хотят ли они путешествовать, или они предпочитают свою ближайшую избирательную станцию. А еще есть «приоритетные» избирательные участки, которые считаются более рискованными, и поэтому нам нужны там добровольцы.
Я решил сделать следующее:
- Создайте таблицу базы данных «волонтеры», в которой хранятся все данные обо всех потенциальных волонтерах
- Импортируйте все данные — используя анализатор apache CSV, выполните синтаксический анализ файлов CSV (преобразованных из таблиц Google) с помощью 1. онлайн-формы 2. данных из полученных бумажных деклараций
- Сопоставьте записи из двух источников по полному имени (поскольку объявления не могут содержать электронную почту, которая в противном случае была бы первичным ключом)
- Геокодировать адреса людей
- Импортировать все избирательные участки и их адреса (общедоступные данные центральной избирательной комиссии)
- Геокодировать адреса избирательных участков
- Найдите адрес ближайшего избирательного участка для каждого волонтера
Все шаги несколько тривиальны, кроме последней части, но я все же объясню вкратце. Синтаксический анализ и импорт CSV прост. Единственное, что нужно быть осторожным, — это иметь возможность вставлять дополнительные записи на более позднюю дату, потому что объявления принимаются во время написания.
Геокодирование немного сложнее. Сначала я использовал OpenStreetMap, но ему удалось найти только часть адресов (которые не нормированы — добровольцы и чиновники иногда не заботятся о структуре адресов). API OpenStreetMap можно найти здесь . По сути, он вызывает http://nominatim.openstreetmap.org/search.php?q=address&format=json с адресом. Я попытался очистить некоторые адреса автоматически, что привело к более успешному геокодированию, но не очень.
Остальные координаты я получил через гугл карты . Я извлекаю все негеокодированные адреса и соответствующие им первичные ключи (для добровольцев — электронную почту; для избирательных участков — хеш полунормализованного адреса), анализирую их с помощью javascript, который затем вызывает API Карт Google. Что-то вроде этого:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
<script type="text/javascript" src="jquery.csv.min.js"></script><script type="text/javascript"> var idx = 1; function initMap() { var map = new google.maps.Map(document.getElementById('map'), { zoom: 8, center: {lat: -42.7339, lng: 25.4858} }); var geocoder = new google.maps.Geocoder(); $.get("geocode.csv", function(csv) { var stations = $.csv.toArrays(csv); for (var i = 1; i < stations.length; i ++) { setTimeout(function() { geocodeAddress(geocoder, map, stations[idx][1], stations[idx][0]); idx++; }, i * 2000); } }); } function geocodeAddress(geocoder, resultsMap, address, label) { geocoder.geocode({'address': address}, function(results, status) { if (status === 'OK') { $("#out").append(results[0].geometry.location.lat() + "," + results[0].geometry.location.lng() + ",\"" + label.replace('"', '""').trim() + "\"<br />"); } else { console.log('Geocode was not successful for the following reason: ' + status); } }); }</script> |
Это выплевывает CSV на экране. Затем я взял и преобразовал с помощью регулярного выражения замены (Notepad ++) для обновления запросов:
|
1
2
|
Find: (\d+\.\d+),(\d+\.\d+),(".+")Replace: UPDATE addresses SET lat=$1, lon=$2 WHERE hash=$3 |
Теперь, когда у меня было большинство геокодированных адресов, поиск по расстоянию должен был начаться. Я использовал запрос из этого SO вопроса, чтобы придумать этот (Мой) SQL-запрос:
|
1
2
3
4
5
6
7
|
SELECT MIN(distance), email, names, stationCode, calc.address FROM(SELECT email, codePrefix, addresses.address, names, ( 3959 * acos( cos( radians(volunteers.lat) ) * cos( radians( addresses.lat ) ) * cos( radians(addresses.lon) - radians(volunteers.lon)) + sin(radians(volunteers.lat)) * sin( radians(addresses.lat)))) AS distance from (select address, hash, stationCode, city, lat, lon FROM addresses JOIN stations ON addresses.hash = stations.addressHash GROUP BY hash) as addresses JOIN volunteers WHERE addresses.lat IS NOT NULL AND volunteers.lat IS NOT NULL ORDER BY distance ASC) as calcGROUP BY names; |
Это выплевывает ближайший избирательный участок для каждого из добровольцев. Его легко превратить в запрос на обновление, чтобы задать код избирательного участка каждому добровольцу в указанном поле.
Затем необходимо внести некоторые ручные поправки, основанные на предпочтениях в поездках — если человек желает совершить поездку, мы выбираем одну из «приоритетных станций» и назначаем ее им. Так как это небольшое количество, автоматизировать его не стоит.
Конечно, в действительности, из-за недостатков сбора данных, приведенный выше идеализированный пример сопровождался большим ручным трудом проверки бумажных деклараций, многократного раздражения людей по телефону и очистки данных, но в итоге значительная часть добровольцы были распределены с помощью вышеуказанного механизма.
Помимо того, что это интересная задача, я думаю, это показывает, что навыки программирования полезны практически для любой задачи в настоящее время. Если бы нам пришлось делать это вручную (и даже если бы у нас было несколько человек с хорошими навыками превосходства), это был бы долгий и утомительный процесс. Так что я за то, чтобы всех учили писать код. Им не обязательно становиться разработчиком, но способ, которым программирование помогает нетривиальным задачам, чрезвычайно полезен.
| Ссылка: | Распределение волонтеров на избирательных участках от нашего партнера по JCG Божидара Божанова в блоге на техническом блоге Божо . |