View Thread
 Print Thread
Importing Ordininance Survey Northings / Eastings into Latitudes & Logitudes
admin
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
 
Jump to Forum