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 (из двух частей)
Анализ и называние данных источника
1 -- Из набора открытых данных
2 CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы Моссвет"
3 AS WITH data AS (
4 SELECT "GeoJSON" fc
5 FROM "data.mos.ru Часы"
6 ), f AS (
7 SELECT json_array_elements(data.fc -> 'features') ft
8 FROM data
9 )
10 SELECT row_number() OVER () "№",
11 st_geomFROMgeojson(f.ft ->> 'geometry') "φλ",
12 (((f.ft -> 'properties') -> 'Attributes') ->> 'global_id')::int4 "Код",
13 (((f.ft -> 'properties') -> 'Attributes') ->> 'PhotoClockType') "ФотоТип",
14 ((f.ft -> 'properties') -> 'Attributes') ->> 'Location'::text "Адрес",
15 ((f.ft -> 'properties') -> 'Attributes') ->> 'Type'::text "Тип",
16 (((f.ft -> 'properties') -> 'Attributes') ->> 'Power')::double precisiON "кВт",
17 ((f.ft -> 'properties') -> 'Attributes') ->> 'AdmArea'::text "Округ",
18 ((f.ft -> 'properties') ->> 'RowId')::int4 "Код записи",
19 ((f.ft -> 'properties') ->> 'DatasetId')::int4 "Код таблицы",
20 ((f.ft -> 'properties') ->> 'VersionNumber')::int4 "Версия табл."
21 FROM f;
Формирование тегов
См. представление "Часы Москвы"."2 Теги на Моссвет" в главе преобразования данных
1 -- "Часы Москвы"."2 Теги на Моссвет" потом используются не все теги
2 CREATE OR REPLACE VIEW "Часы Москвы"."2 Теги на Моссвет"
Теги набора данных / Changeset Tags
source = https://data.mos.ru/opendata/1499
Преобразование данных / Data Transformation
PostGIS скрипты. Загрузка cуществующих данных.
Подготовка таблиц
1 -- ЧАСЫ МОСКВЫ
2 -- Таблицы
3
4 CREATE TABLE "Часы Москвы"."data.mos.ru Часы" (
5 "GeoJSON" jsON NOT NULL
6 );
7 COMMENT ON TABLE "Часы Москвы"."data.mos.ru Часы" IS 'https://data.mos.ru/opendata';
8
9
10 CREATE TABLE "Часы Москвы"."OSM OverPass столбы" (
11 "JSON" jsON NOT NULL
12 );
13 COMMENT ON TABLE "Часы Москвы"."OSM OverPass столбы" IS '[out:json];
14 node
15 [highway=street_lamp]
16 ({{bbox}});
17 out;';
18
19 CREATE TABLE "Часы Москвы"."OSM OverPass часы" (
20 "JSON" jsON NOT NULL
21 );
22 COMMENT ON TABLE "Часы Москвы"."OSM OverPass часы" IS '[out:json];
23 node
24 [amenity=clock]
25 ({{bbox}});
26 out;';
27
28 CREATE TABLE "Часы Москвы"."∄" (
29 "ref:data.mos.ru" int4 NOT NULL
30 );
31 COMMENT ON TABLE "Часы Москвы"."∄" IS 'Не существуют, ошибки в данных';
32
33 -- Добавочная таблица по желанию
34 CREATE TABLE "Часы Москвы"."data.mos.ru столбы" (
35 "JSON" jsON NOT NULL
36 );
37 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
1 d=$(unzip -p 'data-107097-2022-02-02.zip' 'data-107097-2022-02-02.json'| iconv -f cp1251 -t utf-8);
2 echo 'INSERT INTO "Часы Москвы"."data.mos.ru столбы" ("JSON") VALUES ('"'$d'"');' | psql;
3 d='';
SQL INSERT единственной строкой. Загрузка требует до 1 Гб ОЗУ.
Вычисление экспорта
Самая свежая версия всегда находится в репозитории
1 -- ЧАСЫ МОСКВЫ
2 -- ПРЕДСТАВЛЕНИЯ
3 -- возможная полная очистска перед пересозданием
4 -- DROP VIEW "Часы Москвы"."1 Часы OSM" CASCADE;
5 -- DROP VIEW "Часы Москвы"."1 Часы Моссвет" CASCADE;
6 -- DROP MATERIALIZED VIEW "Часы Москвы"."1 Столбы OSM" CASCADE;
7
8
9 -- Из набора открытых данных
10 CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы Моссвет"
11 AS WITH data AS (
12 SELECT "GeoJSON" fc
13 FROM "data.mos.ru Часы"
14 ), f AS (
15 SELECT json_array_elements(data.fc -> 'features') ft
16 FROM data
17 )
18 SELECT row_number() OVER () "№",
19 st_geomFROMgeojson(f.ft ->> 'geometry') "φλ",
20 (((f.ft -> 'properties') -> 'Attributes') ->> 'global_id')::int4 "Код",
21 (((f.ft -> 'properties') -> 'Attributes') ->> 'PhotoClockType') "ФотоТип",
22 ((f.ft -> 'properties') -> 'Attributes') ->> 'Location'::text "Адрес",
23 ((f.ft -> 'properties') -> 'Attributes') ->> 'Type'::text "Тип",
24 (((f.ft -> 'properties') -> 'Attributes') ->> 'Power')::double precisiON "кВт",
25 ((f.ft -> 'properties') -> 'Attributes') ->> 'AdmArea'::text "Округ",
26 ((f.ft -> 'properties') ->> 'RowId')::int4 "Код записи",
27 ((f.ft -> 'properties') ->> 'DatasetId')::int4 "Код таблицы",
28 ((f.ft -> 'properties') ->> 'VersionNumber')::int4 "Версия табл."
29 FROM f;
30
31 CREATE OR REPLACE VIEW "Часы Москвы"."1 Часы OSM" AS
32 WITH data AS (
33 SELECT "OSM OverPass часы"."JSON" fc
34 FROM "OSM OverPass часы"
35 ), f AS (
36 SELECT json_array_elements(data.fc -> 'elements') ft
37 FROM data
38 )
39 SELECT row_number() OVER () "№",
40 (f.ft ->> 'id')::int8 "Код OSM",
41 st_setsrid(st_point((f.ft ->> 'lon')::double precision, (f.ft ->> 'lat')::double precision), 4326) "φλ",
42 f.ft -> 'tags'::text a,
43 (f.ft -> 'tags') ->> 'name'::text "Название",
44 (f.ft -> 'tags') ->> 'display'::text "Вывод",
45 (f.ft -> 'tags') ->> 'date'::text "Показ даты",
46 (f.ft -> 'tags') ->> 'visibility'::text "Обзор",
47 (f.ft -> 'tags') ->> 'support'::text "Крепление"
48 FROM f;
49
50 CREATE MATERIALIZED VIEW "Часы Москвы"."1 Столбы OSM"
51 AS WITH data AS (
52 SELECT "JSON" AS fc
53 FROM "OSM OverPass столбы"
54 ), f AS (
55 SELECT json_array_elements(data.fc -> 'elements') ft
56 FROM data
57 )
58 SELECT row_number() OVER () "№",
59 f.ft ->> 'id'::text "Код OSM",
60 st_setsrid(st_point((f.ft ->> 'lon')::double precision, (f.ft ->> 'lat')::double precision), 4326) "φλ",
61 f.ft -> 'tags' ->> 'height' "Высота",
62 f.ft -> 'tags' ->> 'highway' "Уличное",
63 f.ft -> 'tags' ->> 'lamp_mount' "Крепление",
64 f.ft -> 'tags' ->> 'lamp_type' "Тип лампы",
65 f.ft -> 'tags' ->> 'light:colour' "Цвет света",
66 f.ft -> 'tags' ->> 'light:count' "n ламп",
67 f.ft -> 'tags' ->> 'mast:colour' "Цвет столба",
68 f.ft -> 'tags' ->> 'mast:material' "Материал столба",
69 f.ft -> 'tags' ->> 'note' "Заметка",
70 f.ft -> 'tags' ->> 'operator' "Оператор",
71 f.ft -> 'tags' ->> 'ref' "Код",
72 f.ft -> 'tags' ->> 'start_date' "Ввод в строй",
73 (f.ft -> 'tags')::jsonb
74 - 'height' - 'highway' - 'lamp_mount' - 'lamp_type' - 'light:colour' - 'light:count'
75 - 'mast:colour' - 'mast:material' - 'note' - 'operator' - 'ref' - 'start_date' t
76 FROM f;
77 --CREATE INDEX "1_Столбы_OSM_φλ_IDX" ON "Часы Москвы"."1 Столбы OSM" (φλ);
78 CREATE INDEX "1_Столбы_OSM_geo_IDX" ON "Часы Москвы"."1 Столбы OSM" ((st_transform(φλ,4326)::geography));
79
80 -- "Часы Москвы"."2 Теги на Моссвет" потом используются не все теги
81 CREATE OR REPLACE VIEW "Часы Москвы"."2 Теги на Моссвет" AS
82 SELECT "φλ",
83 "Код" "ref:data.mos.ru",
84 "Тип" "clock:model",
85 "кВт"::text || ' kVA'::text "rating",
86 'ООО «Новый город»'::text "operator",
87 '+7 499 2673071' "contact:phone",
88 'ГУП «Моссвет»' "owner",
89 'street'::text "visibility",
90 'analog'::text "display",
91 CASE WHEN "ч"."Тип" = 'Часы 2С на опоре'::text
92 THEN 'pole'::text
93 ELSE NULL::text
94 END "support",
95 'https://data.mos.ru/opendata/1499'::text "source",
96 'clock'::text "amenity",
97 -"№" id
98 FROM "Часы Москвы"."1 Часы Моссвет" "ч";
99
100 -- Часы, которые уже есть в OSM с добавлением распознанных данных Моссвета
101 CREATE OR REPLACE VIEW "Часы Москвы"."2 Часы с привязкой" AS
102 SELECT чo."Код OSM",
103 чo.φλ φλ_OSM,
104 чo.a,
105 чм.*,
106 ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) "Δ м"
107 FROM "Часы Москвы"."1 Часы Моссвет" чм
108 INNER JOIN "Часы Москвы"."1 Часы OSM" чo
109 ON ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) < 17;
110
111 -- Часы, опора которых уже есть в OSM с добавлением распознанных данных Моссвета
112 CREATE OR REPLACE VIEW "Часы Москвы"."2 Часы на опорах" AS
113 SELECT сo."№",
114 сo."Код OSM",
115 сo."φλ" "φλ опоры",
116 сo.t,
117 чм."φλ" "φλ часов",
118 чм."Код",
119 ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) "Δ м"
120 FROM "Часы Москвы"."1 Столбы OSM" сo
121 JOIN "Часы Москвы"."1 Часы Моссвет" чм
122 ON ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(чм.φλ,4326)::geography) < 8.0;
123
124 -- Представления для экспорта
125
126 CREATE OR REPLACE VIEW "Часы Москвы"."3 Экспорт Моссвет" AS
127 SELECT чм.*,
128 ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)) "Δ м"
129 FROM "Часы Москвы"."2 Теги на Моссвет" чм
130 LEFT JOIN "Часы Москвы"."1 Часы OSM" чo
131 ON ST_Distance(st_transform(чo.φλ,4326)::geography, st_transform(чм.φλ,4326)) < 17.0
132 WHERE "чo"."φλ" IS null
133 AND NOT ("чм"."ref:data.mos.ru" IN ( SELECT o."Код" FROM "Часы Москвы"."2 Часы на опорах" o))
134 AND NOT ("чм"."ref:data.mos.ru" IN ( SELECT n."ref:data.mos.ru" FROM "Часы Москвы"."∄" n))
135 ;
136
137 -- GeoJSON экспорт
138 CREATE OR REPLACE VIEW "Часы Москвы"."4 geoJSON экспорт" AS
139 WITH features AS (
140 SELECT json_build_object(
141 'type', 'Feature',
142 'geometry', st_asgeojson(r."φλ")::json,
143 'properties', to_jsonb(r.*) - 'φλ') feature
144 FROM "Часы Москвы"."3 Экспорт Моссвет"r
145 )
146 SELECT json_build_object('type', 'FeatureCollection',
147 'features', json_agg(features.feature)
148 ) "GeoJSON"
149 FROM features;
150
151 CREATE OR REPLACE VIEW "Часы Москвы"."4 osmChamge экспорт" AS
152 WITH nodes AS
153 (SELECT id,
154 round(ST_Y(x.φλ)::numeric,7) "lat",
155 round(ST_X(x.φλ)::numeric,7) "lon",
156 0 "version",
157 xmlconcat(
158 xmlelement(name tag, xmlattributes ( 'amenity' as k, amenity as v)),
159 --xmlelement(name tag, xmlattributes ( 'ref:data.mos.ru' as k, "ref:data.mos.ru" as v)),
160 xmlelement(name tag, xmlattributes ( 'clock:model' as k, "clock:model" as v)),
161 xmlelement(name tag, xmlattributes ( 'operator' as k, "operator" as v)),
162 xmlelement(name tag, xmlattributes ( 'owner' as k, "owner" as v)),
163 xmlelement(name tag, xmlattributes ( 'contact:phone' as k, "contact:phone" as v)),
164 xmlelement(name tag, xmlattributes ( 'rating' as k, "rating" as v)),
165 xmlelement(name tag, xmlattributes ( 'visibility' as k, "visibility" as v)),
166 xmlelement(name tag, xmlattributes ( 'display' as k, "display" as v)),
167 case when "support" is not null
168 then xmlelement(name tag, xmlattributes ( 'support' as k, "support" as v))
169 end ,
170 xmlelement(name tag, xmlattributes ( 'source' as k, "source" as v))
171 ) "tags"
172 FROM "Часы Москвы"."3 Экспорт Моссвет" x
173 ),
174 elem AS (
175 SELECT xmlelement(name node, xmlattributes (
176 nodes.id,
177 nodes."lon",
178 nodes."lat",
179 nodes."version"),
180 nodes.tags
181 ) "XML"
182 FROM nodes
183 ),
184 node_agg AS (
185 SELECT xmlagg("XML") "OSM nodes"
186 FROM elem
187 ),
188 osc_create AS (
189 SELECT xmlelement(name create, "OSM nodes") osc_create
190 FROM node_agg
191 ),
192 osc_modify AS (
193 SELECT xmlelement(name modify, null) osc_modify
194 ),
195 osc_delete AS (
196 SELECT xmlelement(name delete, xmlattributes ('true' as "if-unused")) osc_delete
197 )
198 SELECT xmlelement(name "osmChange",
199 xmlattributes (0.6 as version, 'PostGIS 3.0 Часы Москвы' as generator),
200 osc_create.osc_create,
201 osc_modify.osc_modify,
202 osc_delete.osc_delete
203 ) "osc XML"
204 FROM osc_create, osc_modify, osc_delete;
205
206 -- Справка по размеру выгрузок
207 SELECT count(*) FROM "Часы Москвы"."1 Столбы OSM";
208 SELECT count(*) FROM "Часы Москвы"."1 Часы OSM" чo;
209 SELECT count(*) FROM "Часы Москвы"."1 Часы Моссвет" чм;
210
211 CREATE MATERIALIZED VIEW "Часы Москвы"."1 Столбы data.mos.ru" AS
212 WITH data AS (
213 SELECT json_array_elements("JSON") fe
214 FROM "data.mos.ru столбы"
215 )
216 SELECT row_number() OVER () "№",
217 st_geomFROMgeojson(fe ->> 'geoData') "φλ",
218 (fe ->> 'PillarNumber') "№ столба",
219 (fe ->> 'PillarMark') "Марка столба",
220 (fe ->> 'PillarType') "Тип столба",
221 (fe ->> 'LightsNumber') "n ламп",
222 (fe ->> 'Status') "Статус",
223 (fe ->> 'OnTerritoryOfMoscow') "В Москве",
224 (fe ->> 'Owner') "Балансодержатель",
225 (fe ->> 'Year')::int2 "Год",
226 (fe ->> 'global_id')::int4 "Код",
227 (fe ->> 'ID')::int4 "Код записи",
228 --(fe ->> 'DatasetId')::int4 "Код таблицы",
229 --(fe ->> 'VersionNumber')::int4 "Версия табл.",
230 (fe ->> 'District') "Район",
231 (fe ->> 'AdmArea') "Округ"
232 FROM data;
233 --CREATE INDEX "1_Столбы_data_mos_ru_φλ_IDX" ON "Часы Москвы"."1 Столбы data.mos.ru" (φλ);
234 CREATE INDEX "1_Столбы_data_mos_ru_geo_IDX" ON "Часы Москвы"."1 Столбы data.mos.ru" ((st_transform(φλ,4326)::geography));;
235
236 -- Ниже экспериментальные вычисления по столбам
237 REFRESH MATERIALIZED VIEW "Часы Москвы"."1 Столбы data.mos.ru";
238 SELECT count(*) FROM "Часы Москвы"."1 Столбы data.mos.ru";
239
240
241 DROP MATERIALIZED VIEW "Часы Москвы"."2 Столбы с привязкой";
242 CREATE MATERIALIZED VIEW "Часы Москвы"."2 Столбы с привязкой" as
243 SELECT сo."Код OSM",
244 сo.φλ φλ_OSM,
245 сo."Оператор",
246 сo."n ламп" "n ламп OSM",
247 сo."Ввод в строй",
248 см.*,
249 ST_Distance(st_transform(сo.φλ,4326)::geography, st_transform(см.φλ,4326)::geography) "Δ м"
250 FROM "Часы Москвы"."1 Столбы data.mos.ru" см
251 INNER JOIN "Часы Москвы"."1 Столбы OSM" сo
252 ON см."Округ" = 'Южный административный округ'
253 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].