RU:Москва/Импорт уличных часов Моссвет

From OpenStreetMap Wiki
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].