RU:Москва/Импорт уличных часов Моссвет
Jump to navigation
Jump to search
Страница сделана по шаблону из https://wiki.openstreetmap.org/wiki/Import/Plan_Outline пропущенному через автоматический перевод.
Задачи / Goals
Перенос данных уличных часов в Москве в OSM.
Повременный план / Schedule
Одна правка не медленее 3-4 минут.
Импорт данных / Import Data
Общее описание / Background
Сайт-источник / Data source site: https://data.mos.ru/opendata/1499
Основная лицензия данных / Data license: https://data.mos.ru/about/terms
Тип лицензии при применимости / Type of license (if applicable): CC 3.0
Ссылка на разрешение / Link to permission (if required): http://not.textual.ru/zverik/2/4/dit-confirm-nq8.png
OSM определение источника / OSM attribution (if required): https://wiki.openstreetmap.org/wiki/Contributors#Russia , Moscow City Government . Портал открытых данных правительства Москвы.
Проверка совместимости ODbL / ODbL Compliance verified: есть, специальное разрешение дано. / yes, special permission
OSM файлы данных / OSM Data Files
https://github.com/mkgrgis/OSM_clock_mos/blob/main/Экспорт%20часов.osc
Фактически результат импорта оформляется в виде небольшого GeoJSON, который будет сохраняться через JOSM. См. эквивалент предыдущего документа https://github.com/mkgrgis/OSM_clock_mos/blob/main/Экспорт%20часов.geojson
Тип импорта / Import Type
Вариант 1 PostGIS -> GeoJSON (OSM tag) -> JOSM -> OSM API
Вараинт 2 PostGIS -> .osc (osmChange) -> OSM API
Подготовка данных / Data Preparation
Сокращение и упрощение / Data Reduction & Simplification
Не требуются, предоставлены географические точки только по объектам импорта.
{
"geometry": {
"coordinates": [
37.5701991928666,
55.7493111128692
],
"type": "Point"
},
"properties": {
"DatasetId": 1499,
"VersionNumber": 2,
"ReleaseNumber": 27,
"RowId": null,
"Attributes": {
"Name": "Часы",
"Location": "Бородинская 1-я ул., д19 (на здании)",
"Type": "ВНУ-2",
"PhotoClockType": null,
"Power": 0.06,
"AdmArea": "Западный административный округ",
"global_id": 171655924
}
},
"type": "Feature"
},
Теги, которые ставим / Tagging Plans
План переноса данных источника на теги OSM (из двух частей)
Анализ и называние данных источника
-- Из набора открытых данных
CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы Моссвет"
AS WITH data AS (
SELECT "GeoJSON" fc
FROM "data.mos.ru Часы"
), f AS (
SELECT json_array_elements(data.fc -> 'features') ft
FROM data
)
SELECT row_number() OVER () "№",
st_geomFROMgeojson(f.ft ->> 'geometry') "φλ",
(((f.ft -> 'properties') -> 'Attributes') ->> 'global_id')::int4 "Код",
(((f.ft -> 'properties') -> 'Attributes') ->> 'PhotoClockType') "ФотоТип",
((f.ft -> 'properties') -> 'Attributes') ->> 'Location'::text "Адрес",
((f.ft -> 'properties') -> 'Attributes') ->> 'Type'::text "Тип",
(((f.ft -> 'properties') -> 'Attributes') ->> 'Power')::double precisiON "кВт",
((f.ft -> 'properties') -> 'Attributes') ->> 'AdmArea'::text "Округ",
((f.ft -> 'properties') ->> 'RowId')::int4 "Код записи",
((f.ft -> 'properties') ->> 'DatasetId')::int4 "Код таблицы",
((f.ft -> 'properties') ->> 'VersionNumber')::int4 "Версия табл."
FROM f;
Формирование тегов
См. представление "Часы Москвы"."2 Теги на Моссвет" в главе преобразования данных
-- "Часы Москвы"."2 Теги на Моссвет" потом используются не все теги
CREATE OR REPLACE VIEW "Часы Москвы"."2 Теги на Моссвет"
Теги набора данных / Changeset Tags
source = https://data.mos.ru/opendata/1499
Преобразование данных / Data Transformation
PostGIS скрипты. Загрузка cуществующих данных.
Подготовка таблиц
-- ЧАСЫ МОСКВЫ
-- Таблицы
CREATE TABLE "Часы Москвы"."data.mos.ru Часы" (
"GeoJSON" jsON NOT NULL
);
COMMENT ON TABLE "Часы Москвы"."data.mos.ru Часы" IS 'https://data.mos.ru/opendata';
CREATE TABLE "Часы Москвы"."OSM OverPass столбы" (
"JSON" jsON NOT NULL
);
COMMENT ON TABLE "Часы Москвы"."OSM OverPass столбы" IS '[out:json];
node
[highway=street_lamp]
({{bbox}});
out;';
CREATE TABLE "Часы Москвы"."OSM OverPass часы" (
"JSON" jsON NOT NULL
);
COMMENT ON TABLE "Часы Москвы"."OSM OverPass часы" IS '[out:json];
node
[amenity=clock]
({{bbox}});
out;';
CREATE TABLE "Часы Москвы"."∄" (
"ref:data.mos.ru" int4 NOT NULL
);
COMMENT ON TABLE "Часы Москвы"."∄" IS 'Не существуют, ошибки в данных';
-- Добавочная таблица по желанию
CREATE TABLE "Часы Москвы"."data.mos.ru столбы" (
"JSON" jsON NOT NULL
);
COMMENT ON TABLE "Часы Москвы"."data.mos.ru столбы" IS 'https://data.mos.ru/opendata/61762';
Заполнение таблиц
"Часы Москвы"."OSM OverPass часы"
OverPass API по часам
[out:json];
node
[amenity=clock]
({{bbox}});
out;
SQL INSERT единственной строкой.
"Часы Москвы"."data.mos.ru Часы"
Данные по часам из внешнего источника
GeoJSON из источника https://data.mos.ru/opendata/1499
SQL INSERT единственной строкой.
"Часы Москвы"."OSM OverPass столбы"
OverPass API по столбам освещения
[out:json];
node
[highway=street_lamp]
({{bbox}});
out;
SQL INSERT единственной строкой.
"Часы Москвы"."data.mos.ru столбы"
Данные по столбам из внешнего источника JSON из источника https://data.mos.ru/opendata/61762
d=$(unzip -p 'data-107097-2022-02-02.zip' 'data-107097-2022-02-02.json'| iconv -f cp1251 -t utf-8);
echo 'INSERT INTO "Часы Москвы"."data.mos.ru столбы" ("JSON") VALUES ('"'$d'"');' | psql;
d='';
SQL INSERT единственной строкой. Загрузка требует до 1 Гб ОЗУ.
Вычисление экспорта
Самая свежая версия всегда находится в репозитории
-- ЧАСЫ МОСКВЫ
-- ПРЕДСТАВЛЕНИЯ
-- возможная полная очистска перед пересозданием
-- DROP VIEW "Часы Москвы"."1 Часы OSM" CASCADE;
-- DROP VIEW "Часы Москвы"."1 Часы Моссвет" CASCADE;
-- DROP MATERIALIZED VIEW "Часы Москвы"."1 Столбы OSM" CASCADE;
-- Из набора открытых данных
CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы Моссвет"
AS WITH data AS (
SELECT "GeoJSON" fc
FROM "data.mos.ru Часы"
), f AS (
SELECT json_array_elements(data.fc -> 'features') ft
FROM data
)
SELECT row_number() OVER () "№",
st_geomFROMgeojson(f.ft ->> 'geometry') "φλ",
(((f.ft -> 'properties') -> 'Attributes') ->> 'global_id')::int4 "Код",
(((f.ft -> 'properties') -> 'Attributes') ->> 'PhotoClockType') "ФотоТип",
((f.ft -> 'properties') -> 'Attributes') ->> 'Location'::text "Адрес",
((f.ft -> 'properties') -> 'Attributes') ->> 'Type'::text "Тип",
(((f.ft -> 'properties') -> 'Attributes') ->> 'Power')::double precisiON "кВт",
((f.ft -> 'properties') -> 'Attributes') ->> 'AdmArea'::text "Округ",
((f.ft -> 'properties') ->> 'RowId')::int4 "Код записи",
((f.ft -> 'properties') ->> 'DatasetId')::int4 "Код таблицы",
((f.ft -> 'properties') ->> 'VersionNumber')::int4 "Версия табл."
FROM f;
CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы OSM" AS
WITH data AS (
SELECT "OSM OverPass часы"."JSON" fc
FROM "OSM OverPass часы"
), f AS (
SELECT json_array_elements(data.fc -> 'elements') ft
FROM data
)
SELECT row_number() OVER () "№",
(f.ft ->> 'id')::int8 "Код OSM",
st_setsrid(st_point((f.ft ->> 'lon')::double precision, (f.ft ->> 'lat')::double precision), 4326) "φλ",
f.ft -> 'tags'::text a,
(f.ft -> 'tags') ->> 'name'::text "Название",
(f.ft -> 'tags') ->> 'display'::text "Вывод",
(f.ft -> 'tags') ->> 'date'::text "Показ даты",
(f.ft -> 'tags') ->> 'visibility'::text "Обзор",
(f.ft -> 'tags') ->> 'support'::text "Крепление"
FROM f;
CREATE MATERIALIZED VIEW "Часы Москвы"."1 Столбы OSM"
AS WITH data AS (
SELECT "JSON" AS fc
FROM "OSM OverPass столбы"
), f AS (
SELECT json_array_elements(data.fc -> 'elements') ft
FROM data
)
SELECT row_number() OVER () "№",
f.ft ->> 'id'::text "Код OSM",
st_setsrid(st_point((f.ft ->> 'lon')::double precision, (f.ft ->> 'lat')::double precision), 4326) "φλ",
f.ft -> 'tags' ->> 'height' "Высота",
f.ft -> 'tags' ->> 'highway' "Уличное",
f.ft -> 'tags' ->> 'lamp_mount' "Крепление",
f.ft -> 'tags' ->> 'lamp_type' "Тип лампы",
f.ft -> 'tags' ->> 'light:colour' "Цвет света",
f.ft -> 'tags' ->> 'light:count' "n ламп",
f.ft -> 'tags' ->> 'mast:colour' "Цвет столба",
f.ft -> 'tags' ->> 'mast:material' "Материал столба",
f.ft -> 'tags' ->> 'note' "Заметка",
f.ft -> 'tags' ->> 'operator' "Оператор",
f.ft -> 'tags' ->> 'ref' "Код",
f.ft -> 'tags' ->> 'start_date' "Ввод в строй",
(f.ft -> 'tags')::jsonb
- 'height' - 'highway' - 'lamp_mount' - 'lamp_type' - 'light:colour' - 'light:count'
- 'mast:colour' - 'mast:material' - 'note' - 'operator' - 'ref' - 'start_date' t
FROM f;
--CREATE INDEX "1_Столбы_OSM_φλ_IDX" ON "Часы Москвы"."1 Столбы OSM" (φλ);
CREATE INDEX "1_Столбы_OSM_geo_IDX" ON "Часы Москвы"."1 Столбы OSM" ((st_transform(φλ,4326)::geography));
-- "Часы Москвы"."2 Теги на Моссвет" потом используются не все теги
CREATE OR REPLACE VIEW "Часы Москвы"."2 Теги на Моссвет" AS
SELECT "φλ",
"Код" "ref:data.mos.ru",
"Тип" "clock:model",
"кВт"::text || ' kVA'::text "rating",
'ООО «Новый город»'::text "operator",
'+7 499 2673071' "contact:phone",
'ГУП «Моссвет»' "owner",
'street'::text "visibility",
'analog'::text "display",
CASE WHEN "ч"."Тип" = 'Часы 2С на опоре'::text
THEN 'pole'::text
ELSE NULL::text
END "support",
'https://data.mos.ru/opendata/1499'::text "source",
'clock'::text "amenity",
-"№" id
FROM "Часы Москвы"."1 Часы Моссвет" "ч";
-- Часы, которые уже есть в OSM с добавлением распознанных данных Моссвета
CREATE OR REPLACE VIEW "Часы Москвы"."2 Часы с привязкой" AS
SELECT чo."Код OSM",
чo.φλ φλ_OSM,
чo.a,
чм.*,
ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) "Δ м"
FROM "Часы Москвы"."1 Часы Моссвет" чм
INNER JOIN "Часы Москвы"."1 Часы OSM" чo
ON ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) < 17;
-- Часы, опора которых уже есть в OSM с добавлением распознанных данных Моссвета
CREATE OR REPLACE VIEW "Часы Москвы"."2 Часы на опорах" AS
SELECT сo."№",
сo."Код OSM",
сo."φλ" "φλ опоры",
сo.t,
чм."φλ" "φλ часов",
чм."Код",
ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) "Δ м"
FROM "Часы Москвы"."1 Столбы OSM" сo
JOIN "Часы Москвы"."1 Часы Моссвет" чм
ON ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) < 8.0;
-- Представления для экспорта
CREATE OR REPLACE VIEW "Часы Москвы"."3 Экспорт Моссвет" AS
SELECT чм.*,
ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)) "Δ м"
FROM "Часы Москвы"."2 Теги на Моссвет" чм
LEFT JOIN "Часы Москвы"."1 Часы OSM" чo
ON ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)) < 17.0
WHERE "чo"."φλ" IS null
AND NOT ("чм"."ref:data.mos.ru" IN ( SELECT o."Код" FROM "Часы Москвы"."2 Часы на опорах" o))
AND NOT ("чм"."ref:data.mos.ru" IN ( SELECT n."ref:data.mos.ru" FROM "Часы Москвы"."∄" n))
;
-- GeoJSON экспорт
CREATE OR REPLACE VIEW "Часы Москвы"."4 geoJSON экспорт" AS
WITH features AS (
SELECT json_build_object(
'type', 'Feature',
'geometry', st_asgeojson(r."φλ")::json,
'properties', to_jsonb(r.*) - 'φλ') feature
FROM "Часы Москвы"."3 Экспорт Моссвет"r
)
SELECT json_build_object('type', 'FeatureCollection',
'features', json_agg(features.feature)
) "GeoJSON"
FROM features;
CREATE OR REPLACE VIEW "Часы Москвы"."4 osmChamge экспорт" AS
WITH nodes AS
(SELECT id,
round(ST_Y(x.φλ)::numeric,7) "lat",
round(ST_X(x.φλ)::numeric,7) "lon",
0 "version",
xmlconcat(
xmlelement(name tag, xmlattributes ( 'amenity' as k, amenity as v)),
--xmlelement(name tag, xmlattributes ( 'ref:data.mos.ru' as k, "ref:data.mos.ru" as v)),
xmlelement(name tag, xmlattributes ( 'clock:model' as k, "clock:model" as v)),
xmlelement(name tag, xmlattributes ( 'operator' as k, "operator" as v)),
xmlelement(name tag, xmlattributes ( 'owner' as k, "owner" as v)),
xmlelement(name tag, xmlattributes ( 'contact:phone' as k, "contact:phone" as v)),
xmlelement(name tag, xmlattributes ( 'rating' as k, "rating" as v)),
xmlelement(name tag, xmlattributes ( 'visibility' as k, "visibility" as v)),
xmlelement(name tag, xmlattributes ( 'display' as k, "display" as v)),
case when "support" is not null
then xmlelement(name tag, xmlattributes ( 'support' as k, "support" as v))
end ,
xmlelement(name tag, xmlattributes ( 'source' as k, "source" as v))
) "tags"
FROM "Часы Москвы"."3 Экспорт Моссвет" x
),
elem AS (
SELECT xmlelement(name node, xmlattributes (
nodes.id,
nodes."lon",
nodes."lat",
nodes."version"),
nodes.tags
) "XML"
FROM nodes
),
node_agg AS (
SELECT xmlagg("XML") "OSM nodes"
FROM elem
),
osc_create AS (
SELECT xmlelement(name create, "OSM nodes") osc_create
FROM node_agg
),
osc_modify AS (
SELECT xmlelement(name modify, null) osc_modify
),
osc_delete AS (
SELECT xmlelement(name delete, xmlattributes ('true' as "if-unused")) osc_delete
)
SELECT xmlelement(name "osmChange",
xmlattributes (0.6 as version, 'PostGIS 3.0 Часы Москвы' as generator),
osc_create.osc_create,
osc_modify.osc_modify,
osc_delete.osc_delete
) "osc XML"
FROM osc_create, osc_modify, osc_delete;
-- Справка по размеру выгрузок
SELECT count(*) FROM "Часы Москвы"."1 Столбы OSM";
SELECT count(*) FROM "Часы Москвы"."1 Часы OSM" чo;
SELECT count(*) FROM "Часы Москвы"."1 Часы Моссвет" чм;
CREATE MATERIALIZED VIEW "Часы Москвы"."1 Столбы data.mos.ru" AS
WITH data AS (
SELECT json_array_elements("JSON") fe
FROM "data.mos.ru столбы"
)
SELECT row_number() OVER () "№",
st_geomFROMgeojson(fe ->> 'geoData') "φλ",
(fe ->> 'PillarNumber') "№ столба",
(fe ->> 'PillarMark') "Марка столба",
(fe ->> 'PillarType') "Тип столба",
(fe ->> 'LightsNumber') "n ламп",
(fe ->> 'Status') "Статус",
(fe ->> 'OnTerritoryOfMoscow') "В Москве",
(fe ->> 'Owner') "Балансодержатель",
(fe ->> 'Year')::int2 "Год",
(fe ->> 'global_id')::int4 "Код",
(fe ->> 'ID')::int4 "Код записи",
--(fe ->> 'DatasetId')::int4 "Код таблицы",
--(fe ->> 'VersionNumber')::int4 "Версия табл.",
(fe ->> 'District') "Район",
(fe ->> 'AdmArea') "Округ"
FROM data;
--CREATE INDEX "1_Столбы_data_mos_ru_φλ_IDX" ON "Часы Москвы"."1 Столбы data.mos.ru" (φλ);
CREATE INDEX "1_Столбы_data_mos_ru_geo_IDX" ON "Часы Москвы"."1 Столбы data.mos.ru" ((st_transform(φλ,4326)::geography));;
-- Ниже экспериментальные вычисления по столбам
REFRESH MATERIALIZED VIEW "Часы Москвы"."1 Столбы data.mos.ru";
SELECT count(*) FROM "Часы Москвы"."1 Столбы data.mos.ru";
DROP MATERIALIZED VIEW "Часы Москвы"."2 Столбы с привязкой";
CREATE MATERIALIZED VIEW "Часы Москвы"."2 Столбы с привязкой" as
SELECT сo."Код OSM",
сo.φλ φλ_OSM,
сo."Оператор",
сo."n ламп" "n ламп OSM",
сo."Ввод в строй",
см.*,
ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(см.φλ,4326)::geography) "Δ м"
FROM "Часы Москвы"."1 Столбы data.mos.ru" см
INNER JOIN "Часы Москвы"."1 Столбы OSM" сo
ON см."Округ" = 'Южный административный округ'
AND ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(см.φλ,4326)::geography) < 7;
Результаты преобразования данных / Data Transformation Results
Скрипт выгрузки
echo 'SELECT "GeoJSON" FROM "Часы Москвы"."4 geoJSON экспорт"' | psql -A -t -q > 'Экспорт часов.geojson';
echo 'SELECT "osc XML" FROM "Часы Москвы"."4 osmChamge экспорт"' | psql -A -t -q > 'Экспорт часов.osc';
Файлы-результаты
Процесс-алгоритм слияния данных / Data Merge Workflow
Командная работа / Team Approach
В одиночку, объём данных небольшой.
Ссылки / References
Из загрузки исключены часы, уже существующие менее чем 17 метрах от предоставленных координат. Средняя точность совпадения 5 м, применено правило "3σ" для отсечения совпадения в пределах 17 метров. Исключены также часы, находящиеся ближе 5 метров к уже размеченным столбам освещения (40 шт.)
Процесс-алгоритм / Workflow
По шагам
1. GeoJSON с новыми данными загружается в JOSM
2. Отправляется отдельная правка
О размере пакета измнений
Не более 1500 точек, не более 5 Мб OSM Change XML.
Планы отката
Можно откатить по номеру правки, соседние объекты не затрагиваются.
Слияние с БД OSM / Conflation
JOSM, простое добавление точек. По существующим часам и по часам, навешенным на существующие столбы - ручная дорабокта тегов. Таких совсем немного.
ОТК - проверка качества / QA
Выборочная сверка по панорамам Mapillary, Google и Яндекс показала наличие часов в указанных местах согласно всем трём источникам. Выявлены 2 образца часов, реально находящися с другой стороны дороги.
См. Также See also
Сообщение об импорте отсылалось ДАТА и может быть найдено в архиве рассылки АДРЕС. The email to the Imports mailing list was sent on YYYY-MM-DD and can be found in the archives of the mailing list at [1].