Geocoding using OSM data and SQL/Server 2008

I’ve just completed importing the OpenStreetMap data into SQL/Server 2008 using the new geography data types. Originally I linked the data to Manifold GIS to verify the import process but have just started on my first real project using the data which is address geocoding for a GPS tracking site located in Australia. The OSM data doesn’t contain enough street number information to be useful for most areas but the aim is to provide nearest street names at no charge, versus some local commercial geocoding services that charge around ten cents per address.

Loading the OSM ‘ways’ as a series of around 250,000 linestrings seems to be giving a performance of around 40 geocoding operations per second. As you’d expect performance of the STDistance method depends heavily on the distance, so in the SQL/Server stored procedure I first start by locating streets within 100 meters, followed by 1Km, 10Km and finally 100Km. The series of queries only attempts to find street names not matched by the previous query, and for Australia these numbers seem to give a match rate of about 90%, 9%, 1% and not much for the final distance that results in good performance.

For optimisation of the spatial index I found that changing the default of medium for each level in the index grid to high gave approximately a 100% increase in performance. It makes sense that the finest level of granularity would work best when looking for the nearest distance to points. The following was what I ended up using for the spatial index definition, changing the cells per object didn’t seem to have a great deal of effect for this dataset / application so I left it at the default value of 16.

CREATE SPATIAL INDEX [spatial_IX] ON [dbo].[Ways] 
(
	[LineString]
) USING  GEOGRAPHY_GRID 
WITH (
    GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
    CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]

Using MySQL variables to limit a select query

I discovered today when writing a stored procedure for a web reporting application that MySQL doesn’t support using variables for the LIMIT clause in a SELECT statement. I didn’t really want to write a dynamic query, one workaround that I found while Googling looked promising:

SET SQL_SELECT_LIMIT = RowLimit (can be a variable)
SELECT ….
SET SQL_SELECT_LIMIT = default

However it is applied at a global level and I needed different limits within a sequence of four queries formed into a union to get the top N results for a series of categories. Because I was giving the user the limited choice of retrieving the top 10, 20, 50 or 100 items I ending up using a CASE statement with each WHEN clause containing a duplicate of the entire query. Certainly not a pretty solution but works and is reasonably maintainable by leaving the LIMIT clause on a seperate line so any changes can be cut & pased down the procedure.

Using JET OLE DB under ASP and Vista x64

I’m currently performing some work on classic ASP pages that during development are retrieving data from an existing Microsoft Access database. After receiving the following error I found that Microsoft haven’t and have no plans to release JET OLE DB drivers for 64-bit platforms. Fair enough it’s hardly the way of the future but I wanted to be able to perform some development on the pages before migrating to SQL/Server.

Provider=Microsoft.Jet.OLEDB.4.0
ADODB.Connection error ‘800a0e7a’
Provider cannot be found. It may not be properly installed.

After some digging around I found the solution was to change the IIS 7 settings to run the application in 32-bit mode. That can be performed under Vista x64 using the following steps:

  • Click Start > All programs > Administrative tools > Internet Information Server (IIS) Manager
  • Under the machine name click on Application Pools.
  • Right-mouse click on DefaultAppPool and select Advanced Settings…
  • Under the general section set “Enable 32-Bit Applications” to true.
  • Press OK and IIS should now be running in 32-bit mode, no other actions are necessary.
  • Creating compressed ZIP files using VBScript

    SQL/Server database backups tend to compress fairly well, in my experience typically down to around 25% of the original file size. However I discovered backup data compression is only a feature of SQL/Server 2008 Enterprise Edition and it’s not a feature that inspired me to spend thousands extra on a license. I’ve attached a VBScript procedure that I wrote to automatically compress my SQL/Server backups but of course it can be used for any application where you’d like to compress the entire contents of a directory automatically.

    I can’t take credit for the innovative technique of creating a ZIP header in code to make Windows create a compressed ZIP folder. That was a part of a code snippet I found elsewhere quite some time ago. For some reason the MoveHere method of Shell.NameSpace didn’t seem to remove the source file so when move mode is selected I stuck with deleting the source file afterwards. This version displays ZIP compression progress in a dialog box but you could use “ZipFile.CopyHere InFilename, 4” to disable the progress dialog box.

    ' VBScript to move or copy all files in a folder to a compressed ZIP file
    
    Option Explicit
    Const MoveMode = False
    Const BackupDir = "E:\DB Backups"
    Const TimeoutMins = 10 ' Timeout for individual file compression operation
    
    Sub MoveToZip(InFilename, OutFilename)
    	Dim FSO : Set FSO = CreateObject("Scripting.FileSystemObject")
    	Dim Timeout : Timeout = 0
    	FSO.CreateTextFile(OutFilename, true).WriteLine "PK" & Chr(5) & Chr(6) & String(18, 0)
    	Dim Shell : Set Shell = CreateObject("Shell.Application")
    	Dim ZipFile: Set ZipFile = Shell.NameSpace(OutFilename)
    	ZipFile.CopyHere InFilename
    	Do Until ZipFile.items.Count = 1  or Timeout > TimeoutMins * 600
    		Wscript.Sleep 100
    		Timeout = Timeout + 1
    	Loop
    	If MoveMode and ZipFile.items.Count = 1 Then FSO.DeleteFile(InFilename)
    	Set Shell = Nothing
    	Set FSO = Nothing
    	Set ZipFile = Nothing
    End Sub
    
    Dim FSO : set FSO = CreateObject("Scripting.FileSystemObject")
    Dim Folder : Set Folder = FSO.GetFolder(BackupDir)
    Dim Files : Set Files = Folder.Files
    Dim File
    For Each File In Files
    	If InStr(UCase(File.Name), ".ZIP") = 0 Then
    		MoveToZip BackupDir & "\" & File.Name, BackupDir & "\" & FSO.GetBaseName(File.Name) & ".zip"
    	End If
    Next
    

    MySQL: Access denied for user ‘root’@’localhost’ (using password: YES)

    After Googling and reading through all the usual reasons for this error when trying to connect to a MySQL database I was starting to get frustrated because I could connect to MySQL server using several GUI applications so knew my root password was correct. The problem only cropped up when I wanted to launch MySQL from a bash script to execute a stored procedure via a cron job. When I stepped back and took a look it turned out to be a fairly obvious problem, I was trying as per this example (obviously not the real password):

    mysql -u root -pABC$123

    I normally like to include a few special characters in my passwords and use more than eight characters to negate the possibility of them being cracked using reasonable length rainbow tables. Of course the dollar sign was causing bash environment variable substitution so it was simply a matter of prefixing the dollar sign with a backslash:

    mysql -u root -pABC\$123

    SQL convert integer to hexadecimal string

    The following is a small SQL/Server function to convert an integer to a two digit hexadecimal string. I’m using it as part of a GPS mapping application to display the poll type from an Inmarsat D+ terminal.

    CREATE FUNCTION ConvertToHex2
    (
    @pNumber int
    )
    RETURNS varchar(2)
    AS
    BEGIN
    DECLARE @HexDigits varchar(16)
    DECLARE @Digit1 int
    DECLARE @Digit2 int
    DECLARE @ResultVar varchar(2)

    IF @pNumber IS NULL
    SET @ResultVar = NULL
    ELSE BEGIN
    SET @HexDigits = ‘0123456789ABCDEF’
    SET @Digit1 = @pNumber / 16 + 1
    SET @Digit2 = @pNumber % 16 + 1
    SET @ResultVar = SUBSTRING(@HexDigits, @Digit1, 1) + SUBSTRING(@HexDigits, @Digit2, 1)
    END
    RETURN @ResultVar
    END

    SQL/Server function to format integer IP address

    The following is a small SQL/Server function to convert an IP address stored as an integer (actually bigint) into a human-readable string in the format ‘1.2.3.4’. I’m using it as part of a geolocation by IP address project.

    CREATE FUNCTION IP2String (@IPAddress bigint)
    RETURNS varchar(15)
    AS
    BEGIN
    DECLARE @IP1 bigint
    DECLARE @IP2 bigint
    DECLARE @IP3 bigint
    DECLARE @IP4 bigint
    DECLARE @ResultVar varchar(15)

    SET @IP1 = @IPAddress / 16777216
    SET @IPAddress = @IPAddress – @IP1 * 16777216
    SET @IP2 = @IPAddress / 65536
    SET @IPAddress = @IPAddress – @IP2 * 65536
    SET @IP3 = @IPAddress / 256
    SET @IPAddress = @IPAddress – @IP3 * 256
    SET @IP4 = @IPAddress

    SET @ResultVar = CAST(@IP1 AS varchar(3)) + ‘.’
    + CAST(@IP2 AS varchar(3)) + ‘.’
    + CAST(@IP3 AS varchar(3)) + ‘.’
    + CAST(@IP4 AS varchar(3))
    RETURN @ResultVar

    END

    Application.FileSearch in MS Access 2007

    I recently wrote a small application for someone using Microsoft Access 2003 and found the Application.FileSearch method of enumerating files is no longer available. The older “dir” function is still available so I replaced it with the following code to change the current drive and path and enumerate the files. Note this code will only work for local drive paths, not UNC style network paths.

    Private Sub Form_Activate()
    
    Dim i As Integer
    Dim SearchPath As String
    Dim Filename As String
    
    SearchPath = GetPath() & "Backups"
    ChDrive Mid$(SearchPath, 1, 2)
    ChDir SearchPath
    Filename = Dir("*.csv")
    While Filename <> ""
      RestoreList.AddItem (Filename)
      Filename = Dir
    Wend
    
    End Sub