SQL Server Import of Country, State, Zip, From GeoNames.Org

A few post have been made with complex solutions for importing information from GeoNames.org into SQL Server.  These involved downloading converters to change the file from UTF-8 encoding and downloading additional software (EditPad Pro) to complete the conversion to UTF-16.

A much simpler way is to open the txt file in Microsoft Excel. Let Excel perform it’s magic on the tab delimited file.

Add the headers to the top row:

The main 'geoname' table has the following fields :
---------------------------------------------------
geonameid         : integer id of record in geonames database
name              : name of geographical point (utf8) varchar(200)
asciiname         : name of geographical point in plain ascii characters, varchar(200)
alternatenames    : alternatenames, comma separated varchar(5000)
latitude          : latitude in decimal degrees (wgs84)
longitude         : longitude in decimal degrees (wgs84)
feature class     : see http://www.geonames.org/export/codes.html, char(1)
feature code      : see http://www.geonames.org/export/codes.html, varchar(10)
country code      : ISO-3166 2-letter country code, 2 characters
cc2               : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code       : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code       : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code       : code for third level administrative division, varchar(20)
admin4 code       : code for fourth level administrative division, varchar(20)
population        : bigint (4 byte int)
elevation         : in meters, integer
gtopo30           : average elevation of 30'x30' (ca 900mx900m) area in meters, integer
timezone          : the timezone id (see file timeZone.txt)
modification date : date of last modification in yyyy-MM-dd format

Then save the Excel file. Use SQL Server import to import the Excel file and your done.

Use PRG pattern for data modification

Use PRG pattern for data modification

“PRG stands for Post-Redirect-Get to avoid the classic browser warning when refreshing a page after post.  Whenever you make a POST request, once the request complets do a redirect so that a GET request is fired.  In this way when the user refresh the page, the last GET request will be executed rather than the POST thereby avoiding unnecessary usability issue.”

Entity Framework Links

Mozilla Development Center https://developer.mozilla.org/en/Web_Development 

MSDN – Entity Framework http://msdn.microsoft.com/en-us/library/bb386964.aspx 

Entity Frameworkhttp://naspinski.net/post/Getting-started-with-Linq-To-Entities.aspx 

MSDN – The ADO.NET Entity Frameworkhttp://msdn.microsoft.com/en-us/data/aa937723.aspx 

MSDN – .Net Framework Class Library http://msdn.microsoft.com/en-us/library/ms229335.aspx 

MSDN – Data Development Videos (Entity Framework) – http://msdn.microsoft.com/en-us/data/cc300162.aspx 

MSDN – When to use Linq to SQL vs When to use Linq to Entity – http://msdn.microsoft.com/en-us/library/cc161164.aspx

Eight Entity Framework Tutorials on DataDeveloper.NET – http://thedatafarm.com/blog/data-access/eight-entity-framework-tutorials-on-datadeveloper-net/ 

Application Scenarios (Entity Framework) – http://msdn.microsoft.com/en-us/data/bb738689.aspx 
http://code.msdn.microsoft.com/EFDocSamples2008

XtraReport Bindings

Change[sourcecode language='vb.net']Private WithEvents bindingSource1 As System.Windows.Forms.BindingSource[/sourcecode]

To [sourcecode language='vb.net']Public WithEvents bindingSource1 As System.Windows.Forms.BindingSource[/sourcecode]

Also the bindingSource Datasource needs to be changed in the report code.

[sourcecode language=”vb.net”]Me.bindingSource1.DataSource = GetType(TypeofObject)[/sourcecode]

For the report viewer, again bind the bindingSource Datasource

[sourcecode language=”vb.net”]Dim objects As objects
objects = GetAllObjects()
Dim Report1 As New XtraReport1
Report1.bindingSource1.DataSource = objects
ReportViewer1.Report = Report1[/sourcecode]