Этот пост изначально написал Александр Рубин
В моем предыдущем посте я показал некоторые новые функции MySQL 5.6, которые могут быть очень полезны при создании приложений с гео-поддержкой. В этой статье я покажу, как мы можем получить ГИС-данные с открытым исходным кодом, преобразовать их в MySQL и использовать в наших приложениях с поддержкой GEO. Я также выступил на конференции Percona Live по этой теме .
Источники данных (США)
Для США мы можем рассмотреть 2 основных источника данных:
1. Почтовые индексы с широтой, долготой и границами почтового индекса (многоугольник). Это можно скачать с веб-сайта переписи США : прямая ссылка на почтовые индексы США
2. Точки интересов, дороги, границы и т. Д. Веб-сайт Openstreatmap предоставляет отличный источник данных ГИС. Данные по Северной Америке можно скачать здесь (часто обновляется)
Форматы данных и конвертация
Данные переписи США хранятся в формате Shapefile (.shp, .shx, .dbf). Openstreetmap использует свой собственный формат XML ( OSM ) и / или двоичный формат Protocolbuffer . Мы можем преобразовать это в MySQL с помощью сервера GDAL (в Linux) и утилиты ogr2ogr. Для преобразования Shapefile подойдет любая версия GDAL, однако для OSM / PBF нам нужно будет использовать v. 1.10. Самый простой способ получить GDAL 1.10 — использовать Ubuntu + ubuntugis-unstable repo.
Вот команды, которые я использовал для установки:
apt-add-repository ppa:ubuntugis/ubuntugis-unstable apt-get update apt-get install gdal-bin
Это установит сервер gdal. Убедитесь, что это последняя версия и поддерживается формат OSM:
ogr2ogr --version GDAL 1.10.1, released 2013/08/26 ogrinfo --formats|grep OSM -> "OSM" (readonly)
Теперь мы можем преобразовать его в MySQL. Во-первых, убедитесь, что MySQL имеет механизм хранения по умолчанию = MyISAM (да, GDAL будет использовать MyISAM для возможности добавления пространственного индекса), а max_allowed_packet достаточно большой:
mysql -e "set global max_allowed_packet = 16777216*10; set global default_storage_engine = MyISAM; "
Конвертация почтовых индексов и границ
Теперь мы можем начать конвертацию:
# ogr2ogr -overwrite -progress -f "MySQL" MYSQL:zcta,user=root tl_2013_us_zcta510.shp 0...10...20...30...40...50...60...70...80...90...100 - done.
Единственное, что нам нужно указать, это имя БД и имя пользователя (при условии, что он будет писать на локальный хост, в противном случае укажите хост MySQL). ogr2org создаст все необходимые таблицы.
mysql> use zcta Database changed mysql> show tables; +--------------------+ | Tables_in_zcta | +--------------------+ | geometry_columns | | spatial_ref_sys | | tl_2013_us_zcta510 | +--------------------+ 3 rows in set (0.00 sec)
Geometry_columns иatial_ref_sys являются только справочными таблицами. Все почтовые индексы и границы будут храниться в таблице tl_2013_us_zcta510:
mysql> show create table tl_2013_us_zcta510\G
*************************** 1. row ***************************
Table: tl_2013_us_zcta510
Create Table: CREATE TABLE `tl_2013_us_zcta510` (
`OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL,
`zcta5ce10` varchar(5) DEFAULT NULL,
`geoid10` varchar(5) DEFAULT NULL,
`classfp10` varchar(2) DEFAULT NULL,
`mtfcc10` varchar(5) DEFAULT NULL,
`funcstat10` varchar(1) DEFAULT NULL,
`aland10` double DEFAULT NULL,
`awater10` double DEFAULT NULL,
`intptlat10` varchar(11) DEFAULT NULL,
`intptlon10` varchar(12) DEFAULT NULL,
UNIQUE KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=MyISAM AUTO_INCREMENT=33145 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Пример 1. Выбор границ почтового индекса для данного почтового индекса (Дарем, Северная Каролина):
mysql> select astext(shape) from zcta.tl_2013_us_zcta510 where zcta5ce10='27701'\G *************************** 1. row *************************** astext(shape): POLYGON((-78.902351 35.988107,-78.902436 35.988116,-78.902597 35.98814,-78.902725 35.988147,-78.902992 35.988143,-78.903117 35.988129,... -78.902351 35.988107))
Пример 2. Найти почтовый индекс для данной точки (Lat, Lon): штаб-квартира Percona в Дареме, Северная Каролина
mysql> SELECT zcta5ce10 as ZIP FROM tl_2013_us_zcta510 WHERE st_contains(shape, POINT(-78.90423, 36.004122)); +-------+ | ZIP | +-------+ | 27701 | +-------+ 1 row in set (0.00 sec)
Преобразование данных OpenStreetMap (OSM)
Преобразование OSM одинаково:
ogr2ogr -overwrite -progress -f "MySQL" MYSQL:osm,user=root north-america-latest.osm.pbf
Обратите внимание, что для конвертации потребуется много времени (8-12+ часов, зависит от аппаратного обеспечения).
Таблицы:
mysql> use osm Database changed mysql> show tables; +------------------+ | Tables_in_osm | +------------------+ | geometry_columns | | lines | | multilinestrings | | multipolygons | | other_relations | | points | | spatial_ref_sys | +------------------+ 7 rows in set (0.00 sec)
Точки интереса хранятся в таблице «точек». Таблицы Lines и Multilinestrings содержат улицы, пешеходные тропы, велосипедные дорожки и т.д .:
mysql> show create table points\G
*************************** 1. row ***************************
Table: points
Create Table: CREATE TABLE `points` (
`OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL,
`osm_id` text,
`name` text,
`barrier` text,
`highway` text,
`ref` text,
`address` text,
`is_in` text,
`place` text,
`man_made` text,
`other_tags` text,
UNIQUE KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=MyISAM AUTO_INCREMENT=13660668 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Во-первых, я выбрал границы почтового индекса в переменной MySQL (я мог бы использовать подзапрос, в MySQL 5.6 производительность будет очень похожа; это немного за пределами текущей темы сообщения в блоге, поэтому я не буду сравнивать 2 метода Вот).
Во-вторых, я использовал эту переменную, чтобы найти все точки, которые будут вписываться в наши границы, и отфильтровать по «amenity» => «cafe». Я должен использовать как «% ..%» здесь, но я полагаюсь на пространственный индекс здесь. Объясните план:
mysql> explain SELECT name, st_distance(shape, centroid(@shape) ) as dist FROM osm.points WHERE st_within(shape, @shape) and other_tags like '%"amenity"=>"cafe"%' limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: points
type: range
possible_keys: SHAPE
key: SHAPE
key_len: 34
ref: NULL
rows: 10
Extra: Using where
Вывод
Использование пространственных данных с открытым исходным кодом — отличный способ обогатить ваше приложение и добавить новые функции. Вы можете сохранить эти данные в MySQL, чтобы приложение могло выполнить соединение с существующими данными. Например, если вы храните почтовый индекс для пользователя, вы можете использовать данные OpenStreetMap, чтобы показать соответствующий контент для этого пользователя. Я также приведу больше примеров в моем Talk @ Percona Live 2014, а также поделюсь им в этом блоге в следующем посте.
Я также создал публичный Amazon AMI: GIS-MySQL-Ubuntu — ami-ddfdf5b4. AMI имеет почтовый индекс и данные OSM в MySQL 5.6, а также установленный сервер GDAL (в / data, смонтированный на EBS). Пожалуйста, не стесняйтесь попробовать. Как всегда, я ценю любые комментарии / вопросы / мысли / и т.д.