Geographic Data Worldwide. Part 1
For various reasons, in several client projects we have had to create a list of countries, cities, and languages. Now that we are working on the Company Tool (A tool for tracking and controlling operations for small and medium-sized companies) we ran into the same issue, and even a bigger one, because we need the list of all the countries and cities in the world, so we decided to look a little more carefully to see if there was something we could do to avoid repeating ourselves and solve this problem in a better way. This article is the product of that research and the code we built.
Information on All the Cities.
The first thing was to get the list of all the countries and cities in the world. That had to exist somewhere, so after a while on the internet we found that we could copy it by hand from Wikipedia or consume a service called GeoNames, which includes not only Wikipedia but many more sources. This service exposes Web Services with JSON or XML where you can run a series of queries associated with any city or country in the world. However, it has usage limits (30,000 requests per day and 2,000 per hour) and we did not want to depend on an external service, no matter how reliable it seems, so we took the other option and downloaded the data. Now, processing 2 GB of data was not that simple.
The second thing was to load it into our SQL 2008 R2 so we could run queries, explore the georeferencing capabilities (geographic data) included in SQL, and build our own service.
Loading GeoNames Data into SQL Server 2008 R2.
First of all, you have to transform the file, which came in UTF8 format, to UTF16. Some recommendations on the Internet say it can be imported with the SQL wizard and the file encoding option, but it did not work even though we tried several times, so we used this tool, which converts the file from UTF8 to UTF16 and worked perfectly.
Then we built a table in the database with the structure of the file:
--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)
Then we inserted the data from the new file, converted to UTF16, into our database.
--BULK INSERT Core.GeoNames
--FROM 'C:ReferenceGeoNamesutf16Allcountries.txt'
--WITH( DATAFILETYPE = 'widechar',
-- FIELDTERMINATOR = 't',
-- ROWTERMINATOR = 'n')
--Select top 1000 * from Core.GeoNames
At this point we already have the data loaded and we could query it. We even have some fields with latitude and longitude, but we are not taking advantage of the power of SQL Server 2008 R2 geographic data (even in its Express version, which is free). So we modified the GeoNames table to include a geographic field. (Geographic fields include the curvature of the earth in their analysis; geometric fields do not, so if you want to analyze small things like a warehouse, a storeroom, etc., using their GPS coordinates and georeferencing, you should use geometric fields, but that is a topic for another post)
--ALTER TABLE Core.GeoNames
-- ADD geog GEOGRAPHY NULL
--GO
Once the geographic field is created, you have to update it with the information for each point. Note here how the field is built using the POINT function, to which we pass the longitude and latitude. (Curiously, if you later integrate this with Google Maps, they use latitude and longitude)
--UPDATE Core.GeoNames
-- SET geog = GEOGRAPHY::STGeomFromText
-- ('POINT(' + CAST(longitude AS CHAR(20))
-- + ' ' + CAST(latitude AS CHAR(20)) + ')',4326)
A couple of indexes are good and necessary for queries over 9 million records to have acceptable performance.
--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
Descriptive information about countries, regions, and languages is also available in the files:
countryInfo.txt, admin2Codes.txt, admin1CodesASCII.txt, iso-languagecodes.txt, timeZones.txt
With their corresponding 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]
Enjoying this? Get more like it, once a week.
--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]
This information lets us create a data structure from which we can query the states of a country (departments, provinces), the cities that are part of that state, and many more proximity queries, for example, what is the city with more than 15,000 inhabitants closest to my current point.
Some quick queries to understand the data structure generated:
List of Countries: (best to get it from the countryInfo table):
Select * from CoreDB.Core.GeoNames Where
Feature_code = 'PCLI'
Order by Name
List of Departments (regions, provinces) of a country:
--deptos o primer nivel
Select top 100 * from CoreDB.Core.GeoNames Where
Country_code ='CO' and Feature_code = 'ADM1'
Order By name
Where CO is the country code.
List of the cities that belong to a department
--Ciudades o segundo nivel
Select * from CoreDB.Core.GeoNames Where
Country_code ='CO' and Admin1_code = 02 and Feature_code = 'ADM2'
Order By name
Where CO is the country code, in this case Colombia, and Admin1_code is the code of the department (region, province) selected previously.
With this data we are ready to build a services layer that exposes this information; we will do that in the second part.
Juan Peláez
Software Architect
3Metas Corp.
www.juanpelaez.com
References:
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
Note: Some of these operations — processing the file, creating indexes, etc. — take several minutes, more than 10, on my desktop machine, which is a good machine.
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
Need Expert Help?
Let's discuss how I can help with your technical challenges
Schedule a Consultation