Dados do GeoNames

MariaDB

Página do banco de dados geográficos GeoNames
Página do banco de dados geográficos GeoNames

Os dados do banco de dados geográficos GeoNames, contendo mais de 11 milhões de nomes geográficos, estão disponíveis sob a licença Creative Commons Attribution 4.0 para download gratuito.

Todas as feições (features) são categorizadas em uma das nove classes de feição (Administrative Boundary, Hydrographic, Area, Populated Place, Road / Railroad, Spot, Hypsographic, Undersea, Vegetation) e ainda sub-categorizadas em um dos mais de 650 códigos de feição.

Este texto mostra como baixar os dados do banco de dados geográficos do GeoNames e importar para uma tabela do MariaDB.

Download de dados do GeoNames

A página Free Gazetteer Data contém links para dados armazenados em arquivos compactados. Os arquivos XX.zip contêm feições para os países com código ISO XX. O arquivo readme.txt descreve os links presentes nesta página, os campos da tabela principal geoname e da tabela alternate names, além de outras informações.

No exemplo a seguir é usado o arquivo contendo os dados da Argentina, AR.zip, baixado desta página. Ao ser descompactado são extraídos os arquivos AR.txt e readme.txt do arquivo AR.zip.

Criação da tabela geonamesar

Para armazenar os dados da Argentina contidos no arquivo delimitado AR.txt, foi criada a tabela geonamesar, conforme especificada no arquivo readme.txt. Foi adicionada às colunas originais a coluna coordenadas, do tipo de geometria Point, para receber a longitude e a latitude do ponto geográfico.

DROP TABLE IF EXISTS `geonamesar`;

CREATE TABLE `geonamesar` (
geonameid         INTEGER,         -- id of record in geonames database
name              VARCHAR(200),    -- name of geographical point (utf8)
asciiname         VARCHAR(200),    -- name of geographical point in plain ascii characters
alternatenames    VARCHAR(10000),  -- alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table
latitude          DECIMAL(8,5),    -- latitude in decimal degrees (wgs84)
longitude         DECIMAL(8,5),    -- longitude in decimal degrees (wgs84)
feature_class     CHAR(1),         -- see http://www.geonames.org/export/codes.html 
feature_code      VARCHAR(10),     -- see http://www.geonames.org/export/codes.html
country_code      CHAR(2),         -- ISO-3166 2-letter country code, 2 characters
cc2               VARCHAR(200),    -- alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters
admin1_code       VARCHAR(20),     -- fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code
admin2_code       VARCHAR(80),     -- code for the second administrative division, a county in the US, see file admin2Codes.txt 
admin3_code       VARCHAR(20),     -- code for third level administrative division
admin4_code       VARCHAR(20),     -- code for fourth level administrative division
population        BIGINT,          -- (8 byte int) 
elevation         INTEGER,         -- in meters
dem               INTEGER,         -- digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
timezone          VARCHAR(40),     -- the iana timezone id (see file timeZone.txt)
modification_date DATE,            -- date of last modification in yyyy-MM-dd format
coordenadas       POINT,           -- longitude e latitude do ponto geográfico
PRIMARY KEY (`geonameid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Carga da tabela

Para carregar a tabela geonamesar a partir do arquivo AR.txt foi usado o script mostrado abaixo:

TRUNCATE TABLE `geonamesar`;

LOAD DATA LOCAL
	INFILE '/home/halley/geo/geonames/AR.txt'
	INTO TABLE `geonamesar`
	FIELDS TERMINATED BY '\t'
	OPTIONALLY ENCLOSED BY '\"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(geonameid, name, asciiname, alternatenames, latitude, longitude,
	 feature_class, feature_code, country_code, cc2,
	 admin1_code, admin2_code, admin3_code, admin4_code,
	 @population, @elevation, @dem, timezone, modification_date)
	 SET
	 population = IF(@population = '', 0, @population),
	 elevation = IF(@elevation = '', 0, @elevation),
	 dem = IF(@dem = '', 0, @dem),
	 coordenadas = POINT(longitude, latitude);
Consultas

Abaixo estão mostrados exemplos de consultas aos dados carregados:

MariaDB [geoespacial]> -- Coordenadas do Teatro Colón
MariaDB [geoespacial]> SELECT `name`, coordenadas,
    ->        latitude, longitude,
    ->        ST_X(coordenadas) AS X, 
    ->        ST_Y(coordenadas) AS Y
    -> FROM `geonamesar`
    -> WHERE `name` = 'Teatro Colón'
    -> AND `feature_code` = 'THTR';
name        |coordenadas              |latitude |longitude|X       |Y       |
------------|-------------------------|---------|---------|--------|--------|
Teatro Colón|POINT (-58.3831 -34.6011)|-34.60110|-58.38310|-58.3831|-34.6011|
1 row in set (0.044 sec)
MariaDB [geoespacial]> -- Coordenadas do Zoológico de Buenos Aires (atual Ecoparque)
MariaDB [geoespacial]> -- O antigo Zoológico de Buenos Aires está se transformando
MariaDB [geoespacial]> -- num Ecoparque interativo.
MariaDB [geoespacial]> SELECT `name`, coordenadas,
    ->        latitude, longitude,
    ->        ST_X(coordenadas) AS X, 
    ->        ST_Y(coordenadas) AS Y
    -> FROM `geonamesar`
    -> WHERE `name` = 'Buenos Aires Zoo'
    -> AND `feature_code` = 'ZOO';
name            |coordenadas           |latitude |longitude|X     |Y      |
----------------|----------------------|---------|---------|------|-------|
Buenos Aires Zoo|POINT (-58.42 -34.581)|-34.58100|-58.42000|-58.42|-34.581|
1 row in set (0.055 sec)
MariaDB [geoespacial]> -- Distância do Teatro Colón ao Zoológico de Buenos Aires
MariaDB [geoespacial]> -- usando a função ST_Distance do MariaDB
MariaDB [geoespacial]> SELECT ST_Distance(a.coordenadas, b.coordenadas)
    -> FROM `geonamesar` a, `geonamesar` b
    -> WHERE (a.`name` = 'Teatro Colón' AND a.`feature_code` = 'THTR')
    -> AND (b.`name` = 'Buenos Aires Zoo' AND b.`feature_code` = 'ZOO'); 
+-------------------------------------------+
| ST_Distance(a.coordenadas, b.coordenadas) |
+-------------------------------------------+
|                       0.04201928128847734 |
+-------------------------------------------+
1 row in set (0.084 sec)
MariaDB [geoespacial]> -- Distância do Teatro Colón ao Zoológico de Buenos Aires
MariaDB [geoespacial]> -- usando a função distancia, criada anteriormente, que 
MariaDB [geoespacial]> -- utiliza como raio da Terra o semieixo maior.
MariaDB [geoespacial]> SELECT distancia(a.coordenadas, b.coordenadas)
    -> FROM `geonamesar` a, `geonamesar` b
    -> WHERE (a.`name` = 'Teatro Colón' AND a.`feature_code` = 'THTR')
    -> AND (b.`name` = 'Buenos Aires Zoo' AND b.`feature_code` = 'ZOO'); 
+-----------------------------------------+
| distancia(a.coordenadas, b.coordenadas) |
+-----------------------------------------+
|                       4.054799854905418 |
+-----------------------------------------+
1 row in set (0.104 sec)
MariaDB [geoespacial]> -- Distância do Teatro Colón ao Zoológico de Buenos Aires
MariaDB [geoespacial]> -- usando a função distancia_latitude, criada anteriormente, 
MariaDB [geoespacial]> -- que calcula o raio da Terra a partir da latitude média.
MariaDB [geoespacial]> SELECT distancia_latitude(a.coordenadas, b.coordenadas)
    -> FROM `geonamesar` a, `geonamesar` b
    -> WHERE (a.`name` = 'Teatro Colón' AND a.`feature_code` = 'THTR')
    -> AND (b.`name` = 'Buenos Aires Zoo' AND b.`feature_code` = 'ZOO'); 
+--------------------------------------------------+
| distancia_latitude(a.coordenadas, b.coordenadas) |
+--------------------------------------------------+
|                                4.050443016678488 |
+--------------------------------------------------+
1 row in set (0.104 sec)

Nota - Pelo Google Maps, a distância entre o Teatro Colón, Cerrito 628, C1010 CABA, Argentina e o Ecoparque Ciudad de Buenos Aires, Av. del Libertador, C1425 CABA, Argentina, é de 3,99 km em linha reta e de 5,1 km pela Av. Gral. Las Heras.

Mapa - Buenos Aires: Teatro Colón e Buenos Aires Zoo (Ecoparque) pelas coordenadas do Geonames