Johannes Kebeck's Blog
03-30-2008, 11:00 AM
<h1>Introduction</h1> <p>In my previous posting I mentioned that there are business listings in Virtual Earth and that enterprise customers can additionally leverage the point of interest databases in the MapPoint Web Service. However, there are a lot of other interesting sources on the web of which some are even available for free. One of them is <a href="http://www.geonames.org">GeoNames</a>. <p>"The GeoNames geographical database is available for download free of charge under a <a href="http://creativecommons.org/licenses/by/3.0/">creative commons attribution license</a>. It contains over eight million geographical names and consists of 6.5 million unique features whereof 2.2 million populated places and 1.8 million alternate names. All features are categorized into one out of nine feature classes and further subcategorised into one out of 645 <a href="http://www.geonames.org/export/codes.html">feature codes</a>. The data is accessible free of charge through a number of <a href="http://www.geonames.org/export/#ws">webservices</a> and a daily <a href="http://www.geonames.org/export/#dump">database export</a>." <p>In this example I want to look at the GeoNames database as a source for additional points of interest. Let's take the beaches which are neither in Virtual Earth nor in the MapPoint Web Service. In GeoNames there are ~ 7,000 beaches. <h1>Importing the GeoNames database into SQL Server</h1> <p>Start by downloading the allCountries.zip from the GeoNames <a href="http://download.geonames.org/export/dump/">download site</a>. Although the data dump is available as a tab-delimited text-file in UTF-8 encoding the import into SQL Server is not straight forward since the SQL Server Integration Service expects the data in UTF-16 formats. Now you could just open the file in Visual Studio and save with a different encoding but since the data file for all countries has a size ~750 MB that wouldn't be a lot of fun. Fortunately there is a great tool which can help us out here: <a href="http://nant.sourceforge.net/">NAnt</a>. <p>After you downloaded the binaries: <ul> <li>Create a file called <code>nant.bat</code> in a directory that is included in the system environment variable "<code>PATH"</code> e.g. "C:\WINDOWS" <li>Add the following to <code>nant.bat</code>: <br>@echo off<br>"[Your Path]\NAnt.exe" %* <li>To convert the geonames file allCountries.txt from UTF-8 to UTF-16 create a simple XML-file as NAnt build-file like this<pre><span style="color:blue"><?</span><span style="color:#a31515">xml </span><span style="color:red">version</span><span style="color:blue">=</span>"<span style="color:blue">1.0</span>"<span style="color:blue">?>
<</span><span style="color:#a31515">project </span><span style="color:red">name</span><span style="color:blue">=</span>"<span style="color:blue">geoname</span>" <span style="color:red">default</span><span style="color:blue">=</span>"<span style="color:blue">build</span>" <span style="color:red">basedir</span><span style="color:blue">=</span>"<span style="color:blue">.</span>"<span style="color:blue">>
<</span><span style="color:#a31515">target </span><span style="color:red">name</span><span style="color:blue">=</span>"<span style="color:blue">build</span>"<span style="color:blue">>
<</span><span style="color:#a31515">copy </span><span style="color:red">file</span><span style="color:blue">=</span>"<span style="color:blue">allCountries.txt</span>"
<span style="color:red">todir</span><span style="color:blue">=</span>"<span style="color:blue">Converted</span>" <span style="color:red">inputencoding</span><span style="color:blue">=</span>"<span style="color:blue">UTF-8</span>"
<span style="color:red">outputencoding</span><span style="color:blue">=</span>"<span style="color:blue">UNICODE</span>"<span style="color:blue">/>
</</span><span style="color:#a31515">target</span><span style="color:blue">>
</</span><span style="color:#a31515">project</span><span style="color:blue">><br></span></pre>
<li>Then run the command "nant -buildfile:NantBuild.xml"</ul>
<p>Now that we have the GeoNames-file in a format that SQL Server understands create a new table in your SQL Server database:<pre><span style="color:blue">CREATE TABLE </span>geoname (
geonameid int <span style="color:blue">PRIMARY KEY</span>,
name nvarchar(200),
asciiname nvarchar(200),
alternatenames nvarchar(4000),
latitude decimal(18,15),
longitude decimal(18,15),
fclass nchar(1),
fcode nvarchar(10),
country nvarchar(2),
cc2 nvarchar(60),
admin1 nvarchar(20),
admin2 nvarchar(80),
admin3 nvarchar(20),
admin4 nvarchar(20),
population int,
elevation int,
gtopo30 int,
timezone nvarchar(40),
moddate date) </pre><a href="http://11011.net/software/vspaste"></a>
<p>Now bring up the "Import Data" Wizard in the SQL Server Management Studio and select the "Flat File Source".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasg2xPKc_AAsRHjWdao8VOKhQPAQ70uNGwZt2aC Beqv5DtnTmlLUT261TGKM3XYd5lo?PARTNER=WRITER">
http://blufiles.storage.msn.com/y1pLfDzppuxasgHLwCZdHd2ZO0FSIuKDmBQaDqhsNtsAna8yjl cwf7rbbnjB5eOaMp4k3P5QGVFGFY?PARTNER=WRITER</a>
<p>Select "Tab" as the column delimiter.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxashoWrLNDXKm7adA4nfo6OLkuSXgi2VKXdF210D cmo0EAg_ZSuOaYdP_pRWnJp3wG_I?PARTNER=WRITER">
http://blufiles.storage.msn.com/y1pLfDzppuxasjIbFBT7X0JTTx1sF0PYrWgsoAnO2FGNTqSwiu yEN1J1mFTnQKZEJ_Kkzj3qoKgbs0?PARTNER=WRITER</a>
<p>In the advanced options click on "Suggest Types" and make sure that the types in the data source correspond with the SQL data types. The click "Next".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasjyNCp5dPQ78eJc4_9W2jBxnxZwrTp2ChB7YDA aFCZ182WN3Qs-45gTiE1u3V6C3eg?PARTNER=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxasgnU8n7KTqHhj18XNUn2Pi3jVSTl7Z-aIWqfhH2nYlNWn6JzCU9lB5BLVQgD9EoRTM?PARTNER=WRITER" width=475 border=0></a>
<p>Select the database you want to import into and click "Next again".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasj2-K7KDDjaHSflu8h1XKa-jSmmoyx2ZraJiaQmxHM0VcepSpIUQNwHfJi8GbNsUn4?PARTNE R=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxashv6Hro6eLChBXrcW-I6hzum03VhPdN3Jicqu4nCQfUWuJnmxSEJNJta3tsnRmzv8w?P ARTNER=WRITER" width=475 border=0></a>
<p>Now select the table you created before and click "Next" until the import starts.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasixtJinU3z0FemKeGrnLg5sC65cFnt5Nf-i7h0Qe4ShzuWTPNCGjwjScbu_EqE4mYQ?PARTNER=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxasjLV9AD9RaAdYlY-iyy2hdyQC5i0BBmlmtvnJYwv-BGo2wK1RwqTa2fL4NlyiF-qQg?PARTNER=WRITER" width=475 border=0></a>
<p>Now that we have the data in our own database we can create a Virtual Earth application with database access as described in <a href="http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!1822.entry">one of my previous posting</a>.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasggkctw_TS9MVOKkILMNOYA0e8qBk3EmKeLOqD 6zEqdMjliAk9nTfq4ikfarn7mvxs?PARTNER=WRITER">
http://eayyzw.blu.livefilestore.com/y1psLOdC392akrfJJvWAisExFGY12rD0r0DFubEa4Cka6Q_1pd OOxYjGPy1pp5qAidhEbFfETH_FQqxOOu7FCdkxQ?PARTNER=WR ITER</a>
<p>You will fin the sample application here:
<p>
<p>This sample application also contains some SQL statements and the sample code to spatially enable the database if you have a SQL Server 2008. The making of has been described in <a href="http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!3642.entry">one of my previous postings</a>.<br>
<p>
<div style="padding-right:0px;display:inline;padding-left:0px;float:none;padding-bottom:0px;margin:0px;padding-top:0px">Technorati Tags: <a href="http://technorati.com/tags/Virtual Earth" rel=tag>Virtual Earth</a>,<a href="http://technorati.com/tags/Geonames" rel=tag>Geonames</a>,<a href="http://technorati.com/tags/SQL Server" rel=tag>SQL Server</a>,<a href="http://technorati.com/tags/SQL Server 2008" rel=tag>SQL Server 2008</a>,<a href="http://technorati.com/tags/NAnt" rel=tag>NAnt</a></div><img src="http://c.services.spaces.live.com/CollectionWebService/c.gif?cid=4819404664324524694&page=RSS%3a+Integrating+Virtual+Earth+and+GeoNames&referrer=" width="1px" height="1px" border="0" alt=""><img style="position:absolute" alt="" width="0px" height="0px" src="http://c.live.com/c.gif?NC=31263&NA=1149&PI=73329&RF=&DI=3919&PS=85545&TP=johanneskebeck.spaces.live.com>1=JohannesKebeck">
Click here to view the article. (http://JohannesKebeck.spaces.live.com/Blog/cns!42E1F70205EC8A96!3782.entry)
<</span><span style="color:#a31515">project </span><span style="color:red">name</span><span style="color:blue">=</span>"<span style="color:blue">geoname</span>" <span style="color:red">default</span><span style="color:blue">=</span>"<span style="color:blue">build</span>" <span style="color:red">basedir</span><span style="color:blue">=</span>"<span style="color:blue">.</span>"<span style="color:blue">>
<</span><span style="color:#a31515">target </span><span style="color:red">name</span><span style="color:blue">=</span>"<span style="color:blue">build</span>"<span style="color:blue">>
<</span><span style="color:#a31515">copy </span><span style="color:red">file</span><span style="color:blue">=</span>"<span style="color:blue">allCountries.txt</span>"
<span style="color:red">todir</span><span style="color:blue">=</span>"<span style="color:blue">Converted</span>" <span style="color:red">inputencoding</span><span style="color:blue">=</span>"<span style="color:blue">UTF-8</span>"
<span style="color:red">outputencoding</span><span style="color:blue">=</span>"<span style="color:blue">UNICODE</span>"<span style="color:blue">/>
</</span><span style="color:#a31515">target</span><span style="color:blue">>
</</span><span style="color:#a31515">project</span><span style="color:blue">><br></span></pre>
<li>Then run the command "nant -buildfile:NantBuild.xml"</ul>
<p>Now that we have the GeoNames-file in a format that SQL Server understands create a new table in your SQL Server database:<pre><span style="color:blue">CREATE TABLE </span>geoname (
geonameid int <span style="color:blue">PRIMARY KEY</span>,
name nvarchar(200),
asciiname nvarchar(200),
alternatenames nvarchar(4000),
latitude decimal(18,15),
longitude decimal(18,15),
fclass nchar(1),
fcode nvarchar(10),
country nvarchar(2),
cc2 nvarchar(60),
admin1 nvarchar(20),
admin2 nvarchar(80),
admin3 nvarchar(20),
admin4 nvarchar(20),
population int,
elevation int,
gtopo30 int,
timezone nvarchar(40),
moddate date) </pre><a href="http://11011.net/software/vspaste"></a>
<p>Now bring up the "Import Data" Wizard in the SQL Server Management Studio and select the "Flat File Source".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasg2xPKc_AAsRHjWdao8VOKhQPAQ70uNGwZt2aC Beqv5DtnTmlLUT261TGKM3XYd5lo?PARTNER=WRITER">
http://blufiles.storage.msn.com/y1pLfDzppuxasgHLwCZdHd2ZO0FSIuKDmBQaDqhsNtsAna8yjl cwf7rbbnjB5eOaMp4k3P5QGVFGFY?PARTNER=WRITER</a>
<p>Select "Tab" as the column delimiter.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxashoWrLNDXKm7adA4nfo6OLkuSXgi2VKXdF210D cmo0EAg_ZSuOaYdP_pRWnJp3wG_I?PARTNER=WRITER">
http://blufiles.storage.msn.com/y1pLfDzppuxasjIbFBT7X0JTTx1sF0PYrWgsoAnO2FGNTqSwiu yEN1J1mFTnQKZEJ_Kkzj3qoKgbs0?PARTNER=WRITER</a>
<p>In the advanced options click on "Suggest Types" and make sure that the types in the data source correspond with the SQL data types. The click "Next".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasjyNCp5dPQ78eJc4_9W2jBxnxZwrTp2ChB7YDA aFCZ182WN3Qs-45gTiE1u3V6C3eg?PARTNER=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxasgnU8n7KTqHhj18XNUn2Pi3jVSTl7Z-aIWqfhH2nYlNWn6JzCU9lB5BLVQgD9EoRTM?PARTNER=WRITER" width=475 border=0></a>
<p>Select the database you want to import into and click "Next again".
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasj2-K7KDDjaHSflu8h1XKa-jSmmoyx2ZraJiaQmxHM0VcepSpIUQNwHfJi8GbNsUn4?PARTNE R=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxashv6Hro6eLChBXrcW-I6hzum03VhPdN3Jicqu4nCQfUWuJnmxSEJNJta3tsnRmzv8w?P ARTNER=WRITER" width=475 border=0></a>
<p>Now select the table you created before and click "Next" until the import starts.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasixtJinU3z0FemKeGrnLg5sC65cFnt5Nf-i7h0Qe4ShzuWTPNCGjwjScbu_EqE4mYQ?PARTNER=WRITER"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=484 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxasjLV9AD9RaAdYlY-iyy2hdyQC5i0BBmlmtvnJYwv-BGo2wK1RwqTa2fL4NlyiF-qQg?PARTNER=WRITER" width=475 border=0></a>
<p>Now that we have the data in our own database we can create a Virtual Earth application with database access as described in <a href="http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!1822.entry">one of my previous posting</a>.
<p><a href="http://blufiles.storage.msn.com/y1pLfDzppuxasggkctw_TS9MVOKkILMNOYA0e8qBk3EmKeLOqD 6zEqdMjliAk9nTfq4ikfarn7mvxs?PARTNER=WRITER">
http://eayyzw.blu.livefilestore.com/y1psLOdC392akrfJJvWAisExFGY12rD0r0DFubEa4Cka6Q_1pd OOxYjGPy1pp5qAidhEbFfETH_FQqxOOu7FCdkxQ?PARTNER=WR ITER</a>
<p>You will fin the sample application here:
<p>
<p>This sample application also contains some SQL statements and the sample code to spatially enable the database if you have a SQL Server 2008. The making of has been described in <a href="http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!3642.entry">one of my previous postings</a>.<br>
<p>
<div style="padding-right:0px;display:inline;padding-left:0px;float:none;padding-bottom:0px;margin:0px;padding-top:0px">Technorati Tags: <a href="http://technorati.com/tags/Virtual Earth" rel=tag>Virtual Earth</a>,<a href="http://technorati.com/tags/Geonames" rel=tag>Geonames</a>,<a href="http://technorati.com/tags/SQL Server" rel=tag>SQL Server</a>,<a href="http://technorati.com/tags/SQL Server 2008" rel=tag>SQL Server 2008</a>,<a href="http://technorati.com/tags/NAnt" rel=tag>NAnt</a></div><img src="http://c.services.spaces.live.com/CollectionWebService/c.gif?cid=4819404664324524694&page=RSS%3a+Integrating+Virtual+Earth+and+GeoNames&referrer=" width="1px" height="1px" border="0" alt=""><img style="position:absolute" alt="" width="0px" height="0px" src="http://c.live.com/c.gif?NC=31263&NA=1149&PI=73329&RF=&DI=3919&PS=85545&TP=johanneskebeck.spaces.live.com>1=JohannesKebeck">
Click here to view the article. (http://JohannesKebeck.spaces.live.com/Blog/cns!42E1F70205EC8A96!3782.entry)