Todas las Ciudades del Planeta. Parte 1

Por diversas razones en varios proyectos para clientes hemos tenido que crear una lista de países, ciudades, idiomas. Ahora que estamos trabajando en the Company Tool (Una herramienta de seguimiento y control de la operación para compañías pequeñas y medianas) nos encontramos con el mismo asunto e incluso ampliado porque necesitamos la lista de todos los países y ciudades del mundo, así que decidimos revisar un poco mejor si hay algo que podíamos hacer para no repetirnos y tener este problema resulto de una mejor forma. Este artículo es el producto de esa investigación y el código que construimos.

Información de todos las Ciudades.

Lo primero era obtener la lista de todos los países y ciudades del mundo, eso tenía que existir en alguna parte así que luego de un rato en internet encontramos que podíamos copiarla a mano de Wikipedia o consumir un servicio llamado GeoNames que no solo incluye Wikipedia sino muchas más fuentes, este servicio expone unos Servicios Web (web services) con JSON o XML donde se pueden realizar una serie de consultas asociadas a cualquier ciudad o país del mundo, sin embargo tiene unos limites de uso (30.000 peticiones por día y 2.000 por hora) y no queríamos depender de un servicio externo por más que este parece bastante confiable, tomamos entonces la otra opción y descargamos la data. Ahora bien procesar 2 GB de datos no resultó tan sencillo.

Lo segundo era cargarlos a nuestro SQL 2008 R2 para poder hacer consultas, explorar las capacidades de georeferenciación (Datos geográficos) incluidos en SQL y construir nuestro propio servicio.

Cargando la Data de GeoNames en SQL Server 2008 R2.

Antes que nada hay que transformar el archivo que venia en formato UTF8 a UTF16. Algunas recomendaciones en Internet dicen que puede importarse con el asistente de SQL y la opción de encoding del archivo, pero no funcionó aunque le dimos varias vueltas así que utilizamos esta herramienta, que convierte el archivo desde UTF8 a UTF16 y que funcionó perfectamente.

Luego construimos una tabla en la base de datos con la estructura del archivo:

--CREATE TABLE Core.GeoNames(
--geonameid int NOT NULL,
--name nvarchar(200) NULL,
--asciiname nvarchar(200) NULL,
--alternatenames nvarchar(max) NULL,
--latitude float NULL,
--longitude float NULL,
--feature_class char(2) NULL,
--feature_code nvarchar(10) NULL,
--country_code char(3) NULL,
--cc2 char(60) NULL,
--admin1_code nvarchar(20) NULL,
--admin2_code nvarchar(80) NULL,
--admin3_code nvarchar(20) NULL,
--admin4_code nvarchar(20) NULL,
--population bigint NULL,
--elevation int NULL,
--gtopo30 int NULL,
--timezone char(31) NULL,
--modification_date date NULL)

Luego insertamos la data desde el nuevo archivo convertido en UTF16 en nuestra base de datos.

--BULK INSERT Core.GeoNames
--FROM 'C:ReferenceGeoNamesutf16Allcountries.txt'
--WITH( DATAFILETYPE = 'widechar',
-- FIELDTERMINATOR = 't',
-- ROWTERMINATOR = 'n')


--Select top 1000 * from Core.GeoNames

En este punto ya tenemos la data cargada y podíamos hacer consultas sobre ella, incluso tenemos unos campos con latitud y longitud pero no estamos aprovechando el poder de los datos geográficos de SQL Server 2008 R2 (incluso en su versión Express que es gratuita). Así que modificamos la tabla de GeoNames para incluir un campo geográfico. (Los campos geográficos incluyen en su análisis la curvatura de la tierra, los campos geométricos no, así que si uno quiere analizar cosas pequeñas como una bodega, almacén, etc, usando sus coordenadas gps y georeferenciacion debe usar campos geométricos, pero ese es tema de otro post)


--ALTER TABLE Core.GeoNames
-- ADD geog GEOGRAPHY NULL
--GO

Creado el campo geográfico hay que actualizarlo con la información de cada punto, nótese aquí como se construye el campo utilizando la función POINT a la que le pasamos la longitud y latitud. (Curiosamente si se integra luego esto con GoogleMaps ellos utilizan latitud y longitud)

--UPDATE Core.GeoNames
-- SET geog = GEOGRAPHY::STGeomFromText
-- ('POINT(' + CAST(longitude AS CHAR(20))
-- + ' ' + CAST(latitude AS CHAR(20)) + ')',4326)

Un par de índices son buenos y necesarios para las consultas sobre 9 millones de registros tengan un rendimiento aceptable.

--ALTER TABLE Core.GeoNames
-- ADD CONSTRAINT pk_geonames_geonameid
-- PRIMARY KEY (geonameid)
--GO


--CREATE SPATIAL INDEX geonames_mmmm16_sidx
-- ON Core.GeoNames(geog)
-- USING GEOGRAPHY_GRID
-- WITH (
-- GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),
-- CELLS_PER_OBJECT = 16,
-- PAD_INDEX = ON
-- )
--GO

La información descriptiva de los países, regiones e idiomas también esta disponible en los archivos:

countryInfo.txt, admin2Codes.txt, admin1CodesASCII.txt, iso-languagecodes.txt, timeZones.txt

Con sus correspondientes scripts:


--CREATE TABLE [Core].[TimeZones](
-- [CountryCode] [nvarchar](255) NULL,
-- [TimeZoneId] [nvarchar](255) NULL,
-- [GMT offset 1# Jan 2012] [float] NULL,
-- [DST offset 1# Jul 2012] [float] NULL,
-- [rawOffset (independant of DST)] [float] NULL
--) ON [PRIMARY]


--CREATE TABLE [Core].[Iso-LanguageCodes](
-- [ISO 639-3] [nvarchar](255) NULL,
-- [ISO 639-2] [nvarchar](255) NULL,
-- [ISO 639-1] [nvarchar](255) NULL,
-- [Language Name] [nvarchar](255) NULL
--) ON [PRIMARY]


--CREATE TABLE [Core].[countryInfo](
-- [ISO] [nvarchar](255) NULL,
-- [ISO3] [nvarchar](255) NULL,
-- [ISO-Numeric] [float] NULL,
-- [fips] [nvarchar](255) NULL,
-- [Country] [nvarchar](255) NULL,
-- [Capital] [nvarchar](255) NULL,
-- [Area] [float] NULL,
-- [Population] [float] NULL,
-- [Continent] [nvarchar](255) NULL,
-- [tld] [nvarchar](255) NULL,
-- [CurrencyCode] [nvarchar](255) NULL,
-- [CurrencyName] [nvarchar](255) NULL,
-- [Phone] [float] NULL,
-- [Postal Code Format] [nvarchar](255) NULL,
-- [Postal Code Regex] [nvarchar](255) NULL,
-- [Languages] [nvarchar](255) NULL,
-- [geonameid] [float] NULL,
-- [neighbours] [nvarchar](255) NULL,
-- [EquivalentFipsCode] [nvarchar](255) NULL
--) ON [PRIMARY]


--CREATE TABLE [Core].[AdminCodes](
-- [adminCode] [nvarchar](255) NULL,
-- [name] [nvarchar](255) NULL,
-- [fullName] [nvarchar](255) NULL,
-- [geonamesId] [float] NULL
--) ON [PRIMARY]

Esta información nos permite crear una estructura de datos desde la cual podemos consultar los estados de un país (departamentos, provincias), las ciudades que hacen parte de ese estado y muchas más consultas sobre proximidad, por ejemplo cual es la ciudad de más de 15.000 habitantes más cercana a mi punto actual.

Unas consultas rápidas para entender la estructura de datos generada:

Lista de Países: (mejor obtenerla de la tabla countryInfo):


Select * from CoreDB.Core.GeoNames Where
Feature_code = 'PCLI'
Order by Name

Lista de Departamentos (regiones, provincias) de un país:


--deptos o primer nivel
Select top 100 * from CoreDB.Core.GeoNames Where
Country_code ='CO' and Feature_code = 'ADM1'
Order By name

Donde CO es el código del país.
Lista de las ciudades que pertenecen a un depto


--Ciudades o segundo nivel
Select * from CoreDB.Core.GeoNames Where
Country_code ='CO' and Admin1_code = 02 and Feature_code = 'ADM2'
Order By name

Donde CO es el código del país, en este caso Colombia, y Admin1_code es el código del departamento (región, provincia) seleccionado anteriormente.

Con estos datos estamos listos para construir una capa de servicios que exponga esta información, eso lo haremos en la segunda parte.

 

Juan Peláez
Arquitecto de software
3Metas Corp.
www.juanpelaez.com

 

Referencias:

http://www.geonames.org/
http://www.geonames.org/export/codes.html
http://midnightprogrammer.net/post/Integrate-Bing-Maps-With-Geonames-Database-And-ASPNET.aspx
http://forum.geonames.org/gforum/posts/list/817.page
http://blogs.msdn.com/b/edkatibah/archive/2009/01/13/loading-geonames-data-into-sql-server-2008-yet-another-way.aspx

 

Nota: Algunas de estas operaciones de procesamiento del archivo, creación de índices, etc., toman varios minutos, más de 10, en mi maquina de escritorio que es una buena maquina.

Continent codes :
AF : Africa geonameId=6255146
AS : Asia geonameId=6255147
EU : Europe geonameId=6255148
NA : North America geonameId=6255149
OC : Oceania geonameId=6255151
SA : South America geonameId=6255150
AN : Antarctica geonameId=6255152