Dados da Geofabrik

MariaDB

Geofabrik
Geofabrik

A Geofabrik foi criada com a convicção de que geodados gratuitos, criados por projetos como o OpenStreetMap, se tornarão cada vez mais atraentes para uso comercial.

Os dados gratuitos mantidos pela comunidade, como os produzidos pelo projeto OpenStreetMap, são uma alternativa real aos dados oferecidos pelos principais participantes deste negócio. Esses dados não são apenas gratuitos, mas também vêm com menos restrições de licença do que os demais ofertados.

O servidor de download em https://download.geofabrik.de disponibiliza dados extraídos do conjunto de dados do OpenStreetMap para download gratuito. A maioria desses arquivos é atualizada todos os dias - qualquer alteração enviada para o OpenStreetMap deverá estar no servidor de download no dia seguinte.

Os dados no servidor de download são organizados por região. O diretório raiz contém arquivos que contêm dados de um continente inteiro, e para todos os continentes, exceto Antártica, há subdiretórios onde são encontrados arquivos individuais para vários países. Alguns países têm seus próprios subdiretórios com dados para subdivisões administrativas.

No caso do Brasil existem arquivos distintos para as regiões Centro-Oeste, Nordeste, Norte, Sudeste e Sul. Aqui serão baixados os dados das cinco regiões com a extensão .osm.pbf para posterior carga no SGBD MariaBD.

Carga dos dados da Geofabrik no MariaDB

Foram carregados os cinco arquivos baixados do Geofabrik em suas respectivas tabelas através do programa utilitário GDAL ogr2ogr, que não só carrega os dados nas tabelas, mas também cria as tabelas com base nas informações dos arquivos de origem, usando os comandos:

$ ogr2ogr -f MySQL MySQL:geoespacial,host=localhost,user=nome-usuario,password=senha-usuario norte-latest.osm.pbf -nln norte -skipfailures
$ ogr2ogr -f MySQL MySQL:geoespacial,host=localhost,user=nome-usuario,password=senha-usuario nordeste-latest.osm.pbf -nln nordeste -skipfailures
$ ogr2ogr -f MySQL MySQL:geoespacial,host=localhost,user=nome-usuario,password=senha-usuario sul-latest.osm.pbf -nln sul -skipfailures
$ ogr2ogr -f MySQL MySQL:geoespacial,host=localhost,user=nome-usuario,password=senha-usuario sudeste-latest.osm.pbf -nln sudeste -skipfailures
$ ogr2ogr -f MySQL MySQL:geoespacial,host=localhost,user=nome-usuario,password=senha-usuario centro-oeste-latest.osm.pbf -nln centro_oeste -skipfailures -lco SPATIAL_INDEX=NO

A carga dessas tabelas é muito lenta, pode demorar horas cada uma, dependendo do equipamento usado. Na carga dos dados do Centro foi utilizada a opção de criação de camada SPATIAL_INDEX=NO, para não haver a criação do índice espacial nao coluna contendo a geometria, permitir geometrias nulas e, possivelmente, carregar mais rápido.

Estrutura das tabelas criadas

As cinco tabelas criadas no banco de dados geoespacial possuem a mesma estrutura, uma vez que os arquivos de origem possuem estruturas idênticas. Abaixo está mostrada a estrutura da tabela norte:

MariaDB [geoespacial]> DESCRIBE norte;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| OGR_FID     | int(11)  | NO   | PRI | NULL    | auto_increment |
| SHAPE       | geometry | NO   | MUL | NULL    |                |
| osm_id      | text     | YES  |     | NULL    |                |
| name        | text     | YES  |     | NULL    |                |
| barrier     | text     | YES  |     | NULL    |                |
| highway     | text     | YES  |     | NULL    |                |
| ref         | text     | YES  |     | NULL    |                |
| address     | text     | YES  |     | NULL    |                |
| is_in       | text     | YES  |     | NULL    |                |
| place       | text     | YES  |     | NULL    |                |
| man_made    | text     | YES  |     | NULL    |                |
| other_tags  | text     | YES  |     | NULL    |                |
| waterway    | text     | YES  |     | NULL    |                |
| aerialway   | text     | YES  |     | NULL    |                |
| z_order     | int(11)  | YES  |     | NULL    |                |
| type        | text     | YES  |     | NULL    |                |
| osm_way_id  | text     | YES  |     | NULL    |                |
| aeroway     | text     | YES  |     | NULL    |                |
| amenity     | text     | YES  |     | NULL    |                |
| admin_level | text     | YES  |     | NULL    |                |
| boundary    | text     | YES  |     | NULL    |                |
| building    | text     | YES  |     | NULL    |                |
| craft       | text     | YES  |     | NULL    |                |
| geological  | text     | YES  |     | NULL    |                |
| historic    | text     | YES  |     | NULL    |                |
| land_area   | text     | YES  |     | NULL    |                |
| landuse     | text     | YES  |     | NULL    |                |
| leisure     | text     | YES  |     | NULL    |                |
| military    | text     | YES  |     | NULL    |                |
| natural     | text     | YES  |     | NULL    |                |
| office      | text     | YES  |     | NULL    |                |
| shop        | text     | YES  |     | NULL    |                |
| sport       | text     | YES  |     | NULL    |                |
| tourism     | text     | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
34 rows in set (0.001 sec)

e abaixo está mostrada a estrutura da tabela centro_oeste criada com a opção de criação de camada SPATIAL_INDEX=NO:

MariaDB [geoespacial]> DESCRIBE `centro_oeste`;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| OGR_FID     | int(11)  | NO   | PRI | NULL    | auto_increment |
| SHAPE       | geometry | YES  |     | NULL    |                |
| osm_id      | text     | YES  |     | NULL    |                |
| name        | text     | YES  |     | NULL    |                |
| barrier     | text     | YES  |     | NULL    |                |
| highway     | text     | YES  |     | NULL    |                |
| ref         | text     | YES  |     | NULL    |                |
| address     | text     | YES  |     | NULL    |                |
| is_in       | text     | YES  |     | NULL    |                |
| place       | text     | YES  |     | NULL    |                |
| man_made    | text     | YES  |     | NULL    |                |
| other_tags  | text     | YES  |     | NULL    |                |
| waterway    | text     | YES  |     | NULL    |                |
| aerialway   | text     | YES  |     | NULL    |                |
| z_order     | int(11)  | YES  |     | NULL    |                |
| type        | text     | YES  |     | NULL    |                |
| osm_way_id  | text     | YES  |     | NULL    |                |
| aeroway     | text     | YES  |     | NULL    |                |
| amenity     | text     | YES  |     | NULL    |                |
| admin_level | text     | YES  |     | NULL    |                |
| boundary    | text     | YES  |     | NULL    |                |
| building    | text     | YES  |     | NULL    |                |
| craft       | text     | YES  |     | NULL    |                |
| geological  | text     | YES  |     | NULL    |                |
| historic    | text     | YES  |     | NULL    |                |
| land_area   | text     | YES  |     | NULL    |                |
| landuse     | text     | YES  |     | NULL    |                |
| leisure     | text     | YES  |     | NULL    |                |
| military    | text     | YES  |     | NULL    |                |
| natural     | text     | YES  |     | NULL    |                |
| office      | text     | YES  |     | NULL    |                |
| shop        | text     | YES  |     | NULL    |                |
| sport       | text     | YES  |     | NULL    |                |
| tourism     | text     | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
34 rows in set (0.001 sec)

A única diferença na estrutura com relação às outras tabelas é não haver índice para a coluna SHAPE, que pode ser criado após a carga dos dados conforme mostrado abaixo:

MariaDB [geoespacial]> ALTER TABLE `centro_oeste` MODIFY COLUMN `SHAPE` geometry NOT NULL;
Query OK, 0 rows affected (11.200 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [geoespacial]> ALTER TABLE `centro_oeste` ADD SPATIAL INDEX idx_shape (`SHAPE`);
Query OK, 0 rows affected (37.876 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ficando a estrutura da tabela centro_oeste conforme mostrado abaixo:

MariaDB [geoespacial]> DESCRIBE `centro_oeste`;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| OGR_FID     | int(11)  | NO   | PRI | NULL    | auto_increment |
| SHAPE       | geometry | NO   | MUL | NULL    |                |
| ...         | ...      | ...  | ... | ...     | ...            |
+-------------+----------+------+-----+---------+----------------+
34 rows in set (0.001 sec)

ou seja, idêntica às demais que foram carregadas sem a opção de criação de camada SPATIAL_INDEX=NO.

Geodados por região

A consulta abaixo mostra o número de geodados que foram carregados no banco de dados para cada região:

MariaDB [geoespacial]> SELECT "Norte" As Região, COUNT(*) AS Geodados FROM `norte` UNION SELECT "Nordeste", COUNT(*) FROM `nordeste` UNION SELECT "Sul", COUNT(*) FROM `sul` UNION SELECT "Sudeste", COUNT(*) FROM `sudeste` UNION SELECT "Centro-Oeste", COUNT(*) FROM `centro_oeste`;
+--------------+----------+
| Região       | Geodados |
+--------------+----------+
| Norte        |   706285 |
| Nordeste     |  3005607 |
| Sul          |  3974563 |
| Sudeste      |  7204041 |
| Centro-Oeste |  1061930 |
+--------------+----------+
5 rows in set (10.131 sec)

Alguns poucos geodados não foram carregados por possuírem caracteres não permitidos como: 🏠🐢🐟🍔👇🚛🎵🐅🍻🍺🍹🍸🍷🍾, entre outros.

Exemplo de consulta
MariaDB [geoespacial]> -- Igrejas Presbiterianas na Região Norte
MariaDB [geoespacial]> SELECT name, SHAPE, Y(SHAPE) as Latitude, X(SHAPE) as Longitude
    -> FROM norte
    -> WHERE name = 'Igreja Presbiteriana'
    -> AND NOT osm_id IS NULL;
name                |SHAPE                         |Latitude  |Longitude  |
--------------------|------------------------------|----------|-----------|
Igreja Presbiteriana|POINT (-46.7668146 -1.0594332)|-1.0594332|-46.7668146|
Igreja Presbiteriana|POINT (-59.9656849 -3.0530865)|-3.0530865|-59.9656849|
Igreja Presbiteriana|POINT (-59.9913744 -3.130927) | -3.130927|-59.9913744|
Igreja Presbiteriana|POINT (-47.3529688 -3.0105983)|-3.0105983|-47.3529688|
Igreja Presbiteriana|POINT (-49.6421095 -3.0322033)|-3.0322033|-49.6421095|
Igreja Presbiteriana|POINT (-60.7271227 2.827582)  |  2.827582|-60.7271227|
Igreja Presbiteriana|POINT (-60.6710926 2.825701)  |  2.825701|-60.6710926|
igreja presbiteriana|POINT (-48.4478761 -1.4533093)|-1.4533093|-48.4478761|
8 rows in set (1.093 sec)
Referências

Mapa - Igrejas Presbiterianas na Região Norte