Code Point Open from the ordiniance survey is a free data set of 1.7 million UK postcodes and their position in the UK.
Unforunatley, their position is in OS Grid References using the OSGB36 datum (shape of the earth). If you want to use this info in google maps / bing maps (and MapPoint) you'll need to convert these as latitudes and logitudes - but here is the kicker, converting from OS Grid references to Lat Longs can be done with some tricky maths - however, the new lat longs would still be in the OSGB36 datum! Before you can use them you'll need more math to convert them to WGS84 datum lats.
Note the excellent article here: http://doogalbellend.blogspot.co.uk/2...o-sql.html - you'll need the two dll's that Doogal references in his article (CSV Parser and .net coords)
We basically took this example and created a generic VB.net console application to do the same thing (just with a little more user feedback during the import and command line arguments for sql settings etc):
It basically imports all the CSV files from code point open into SQL Spatial data type columns (in WGS84).
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports DotNetCoords
Imports LumenWorks.Framework.IO.Csv
Module CodePointOpenImporter
Dim SQL_ServerName As String = ""
Dim SQL_Database As String = ""
Dim SQL_UserName As String = ""
Dim SQL_Password As String = ""
Dim SQL_TargetTable As String = ""
Dim CSV_Path As String = ""
Dim ApplicationPathStr As String = ""
Dim LastError As String = ""
Dim UsageExampleStr As String = "CodePointOpenImporter.exe SQL_ServerName SQL_Database SQL_UserName SQL_Password SQL_TargetTable ""C:\codepo_gb\data\CSV"""
Dim db As New SqlConnection
Dim db_Command As New SqlCommand
Dim SQL_ConnectionString As String = "Data Source={server};Initial Catalog={database};User Id={username};Password={password}"
Sub Main()
Dim CommandID As Integer = 0
Dim FileCount As Integer = 0
Dim CurrentFile As Integer = 0
'#### Parse the command line arguments
Try
Console.WriteLine("#### Code-Point Open Import Utility v1.1")
For Each aCommand As String In System.Environment.GetCommandLineArgs()
Select Case CommandID
Case 0
ApplicationPathStr = aCommand
Case 1 'Mandatory - SQL_ServerName
SQL_ServerName = Trim(aCommand)
Case 2 'Mandatory - SQL_Database
SQL_Database = Trim(aCommand)
Case 3 'Mandatory - SQL_UserName
SQL_UserName = Trim(aCommand)
Case 4 'Mandatory - SQL_Password
SQL_Password = Trim(aCommand)
Case 5 'Mandatory - SQL_TargetTable
SQL_TargetTable = Trim(aCommand)
Case 6 'Mandatory - CSV_Path
CSV_Path = Trim(aCommand)
End Select
CommandID = CommandID + 1
Next
'#### Validate Mandatory Args are not empty
If SQL_ServerName = "" Then
Console.WriteLine("ERROR: Paramater empty: SQL_ServerName" & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End If
If SQL_UserName = "" Then
Console.WriteLine("ERROR: Paramater empty: SQL_UserName" & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End If
If SQL_Password = "" Then
Console.WriteLine("ERROR: Paramater empty: SQL_Password" & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End If
If SQL_TargetTable = "" Then
Console.WriteLine("ERROR: Paramater empty: SQL_TargetTable" & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End If
If CSV_Path = "" Then
Console.WriteLine("ERROR: Paramater empty: CSV_Path" & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End If
'#### Validate the CSV_Path
If IO.Directory.Exists(CSV_Path) = False Then
Console.WriteLine("ERROR: CSV_Path is not a valid directory.")
GoTo ExitThread
End If
'#### Ready the connection string
SQL_ConnectionString = SQL_ConnectionString.Replace("{server}", SQL_ServerName)
SQL_ConnectionString = SQL_ConnectionString.Replace("{database}", SQL_Database)
SQL_ConnectionString = SQL_ConnectionString.Replace("{username}", SQL_UserName)
SQL_ConnectionString = SQL_ConnectionString.Replace("{password}", SQL_Password)
'#### Validate the connection string
If ConnectDatabase() = False Then
GoTo ExitThread
End If
Catch ex As Exception
Console.WriteLine("ERROR: reading paramaters" & vbCrLf & ex.Message & vbCrLf & "Usage: " & UsageExampleStr)
GoTo ExitThread
End Try
'#### Fetch a list of csv files from the given path
Dim files As String() = Directory.GetFiles(CSV_Path, "*.csv")
FileCount = files.Length
If FileCount = 0 Then
Console.WriteLine("ERROR: No CSV files where found in given path.")
GoTo ExitThread
End If
'#### Truncate the target table
If SqlQuery("TRUNCATE TABLE " & SQL_TargetTable) = False Then
Console.WriteLine("ERROR: Target Table could not be truncated.")
GoTo ExitThread
End If
'#### Files where found, Inform user and start to loop through each one
Console.WriteLine(files.Length.ToString() & " files found, Importing...")
For Each file As String In files
CurrentFile = CurrentFile + 1
Console.WriteLine("Importing file " & CurrentFile.ToString() & " of " & FileCount.ToString() & ": " & Path.GetFileName(file))
If ReadFile(file) = False Then
Console.WriteLine("ERROR: ReadFile() exited early: " & LastError)
GoTo ExitThread
End If
Next
'#### Close the connection to SQL
CloseDatabaseConnection()
ExitThread:
End Sub
Private Function ReadFile(ByVal file As String) As Boolean
Dim BulkInsertSQL As String = ""
Dim BulkInsertBatchCount As Integer = 0
Dim BulkInsertBatchCounter As Integer = 1
Dim BulkInsertBatchSize As Integer = 1000
Dim TotalRows As Integer = 0
Dim TotalBatches As Integer = 0
'#### Validate file
TotalRows = IO.File.ReadAllLines(file).Length
TotalBatches = Math.Ceiling(TotalRows / BulkInsertBatchSize)
'#### Open the CSV file and enumerate
Try
Using reader As New StreamReader(file)
Dim csvReader As New CsvReader(reader, False)
For Each data As String() In csvReader
Dim postcode As String = data(0)
Dim easting As Double = Double.Parse(data(2))
Dim northing As Double = Double.Parse(data(3))
'#### Convert easting/northing to lat/long
Dim osRef As New OSRef(easting, northing)
Dim latLng As LatLng = osRef.ToLatLng()
latLng.ToWGS84()
'#### Build the INSERT Query
BulkInsertSQL = BulkInsertSQL & "INSERT INTO " & SQL_TargetTable & " ([Postcode], [Spatial]) VALUES (REPLACE(LTRIM(RTRIM('" & data(0).Replace("'", "''") & "')),' ',''), geography::STPointFromText('POINT(" & latLng.Longitude.ToString() & " " & latLng.Latitude.ToString() & ")', 4326)); " & vbCrLf
BulkInsertBatchCount = BulkInsertBatchCount + 1
If BulkInsertBatchCount >= BulkInsertBatchSize Then
Console.Write(vbCr & " - Executing SQL Batch " & BulkInsertBatchCounter.ToString() & " of " & TotalBatches & " (" & BulkInsertBatchCount.ToString() & " Inserts). ")
If SqlQuery(BulkInsertSQL) = False Then
LastError = "Database Error"
Return False
Else
BulkInsertSQL = ""
BulkInsertBatchCount = 0
BulkInsertBatchCounter = BulkInsertBatchCounter + 1
End If
End If
Next
End Using
'#### Insert into SQL
If BulkInsertBatchCount > 0 Then
Console.Write(vbCr & " - Executing SQL Tail Batch " & BulkInsertBatchCounter.ToString() & " of " & TotalBatches & " (" & BulkInsertBatchCount.ToString() & " Inserts). ")
If SqlQuery(BulkInsertSQL) = False Then
LastError = "Database Error"
Return False
Else
Console.Write(vbCrLf)
Return True
End If
End If
Catch ex As Exception
LastError = ex.Message
Return False
End Try
End Function
Public Function ConnectDatabase() As Boolean
'#### Prepare the database connections
Try
db.ConnectionString = SQL_ConnectionString
db.Open()
Return True
Catch ex As Exception
Console.WriteLine("DB_ERROR_WHEN_CONNECTING: " & ex.Message & vbCrLf & "ConnectionString: " & SQL_ConnectionString)
Return False
End Try
End Function
Public Sub CloseDatabaseConnection()
Try
If db.State = ConnectionState.Open Then
db.Close()
End If
Catch ex As Exception
Console.WriteLine("DB_INFO: " & ex.Message)
End Try
End Sub
Public Function SqlQuery(ByVal StrQuery As String) As Boolean
Try
If db.State <> ConnectionState.Open Then
Console.WriteLine("DB_ERROR: Call to SqlQuery() whilst connection is closed, attempting to re-open connection before running the following query: " & vbCrLf)
ConnectDatabase()
If db.State <> ConnectionState.Open Then
Console.WriteLine("DB_ERROR: Failed to restore SQL Connection, aborting query: " & vbCrLf)
Return False
End If
End If
db_Command.CommandTimeout = 120
db_Command.Connection = db
db_Command.CommandText = StrQuery
db_Command.ExecuteNonQuery()
Return True
Catch ex As Exception
Console.WriteLine("DB_ERROR: " & ex.Message & vbCrLf)
Return False
End Try
End Function
End Module
The target table in our case is a SQL Server 2008 table with a Geography data type & two computed columns to present the longs & lats:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PostCodes](
[PostCode] [varchar](10) NOT NULL,
[Spatial] [geography] NULL,
[Longitude] AS ([Spatial].[Long]),
[Latitude] AS ([Spatial].[Lat]),
CONSTRAINT [PK_tbl_PostCodes] PRIMARY KEY CLUSTERED
(
[PostCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Edited by admin on 21-10-2012 00:00
Jordon Pilling | Heavencore Administrator
|
