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.

@pNumber int
RETURNS varchar(2)
DECLARE @HexDigits varchar(16)
DECLARE @Digit1 int
DECLARE @Digit2 int
DECLARE @ResultVar varchar(2)

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

OSM (OpenStreeMap) planet.osm statistics

I’ve recently started importing the planet.osm file into a postgresql database using the osm2pgsql utility. When I started I couldn’t find any up-to-date statistics on the number of primitives in the database to estimate the time it would take so I’ve posted them here. I’m running on a machine with only 2GB RAM allocated under VMWare and a slow 7200RPM 1TB drive and it looks like the process will take around 5 days to complete. The final stages of creating indexes on the tables seems to be taking as long as the initial import.

Reading in file: /home/mapnik/osm/planet-090916.osm.bz2
Processing: Node(434807k) Way(33142k) Relation(212k)
Node stats: total(434807934), max(497541099)
Way stats: total(33142281), max(40900619)
Relation stats: total(212012), max(253016)

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 ‘’. I’m using it as part of a geolocation by IP address project.

CREATE FUNCTION IP2String (@IPAddress bigint)
RETURNS varchar(15)
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


SatSleuth Electronic Circuit Diagrams

I maintain a list of electronic circuit diagrams at http://www.satsleuth.com/schematics.htm and have just added links to the individual categories on the blogroll. As of today it contains 2876 circuit diagrams and with everything on a single page it’s a convenient resource for searching or just for a general browse for your next Sunday afternoon project. On the page there is also now a way to add links to your own circuit diagrams, although the current hit rate is about 98% SPAM to 2% genuine submissions.

Soon I’ll need to update the other web pages to indicate the project status. The Satsleuth GPS tracking pages were a placeholder for a new company / product but we ended up offloading marketing to another company that sell it under a different product name. Other than the circuit diagrams the pages have remained unchanged for quite some time so I’ll update the pages to showcase some turnkey GPS tracking and asset management solutions that I have available for licensing to solution providers.

ACARS Reception with a WinRadio G305e

During an idle moment I downloaded the PlanePlottter software published by COAA and hooked it up to my WinRadio G305e receiver. Much to my suprise I’m getting fairly decent results with an antenna just sitting inside without a proper ground plane, not a great deal of activity because the Hobart (Tasmania) airport is hardly buzzing with activity but I have managed to pick up one signal close to 200KM away. When the weather fines up a bit I’ll try a proper antenna installation and see how it goes, at the moment I noticed I’m getting quite a few CRC errors and coverage is fairly directional as I’d expect with its current location.

So far the best ACARS settings for the WinRadio seem to be 131.550MHz (Australian / International ACARS frequency) in AM mode with the IF bandwidth set to 14KHz, smaller IF bandwidths seemed to get less reliable. I’ve got the RF section set for fast AGC with the preamp enabled but disabled the SAGC feature and any form of audio filtering. Setting the audio level also seems fairly critical for best performance, using the signal view in PlanePlotter I have it set so the noise floor is not a great deal larger than the graticules on the chart. Here’s an example message received earlier that contains GPS information:

—[2009-09-07 08:33:24]—
M {mode}
.VH-JQG {rego}
{NAK} {ack}
3L {unknown message type}
4 {block}
M55A {seq}
JQ0711 {flt}
S 41.272/E146.515 /UTC 0834

PlanePlotter has displayed all positions so far without altitude, course or speed information. I’m not too sure what that is about, I thought especially the altitude would have been critical information. Anyway I’ll get it setup a bit better and keep my eye on it, the PlanePlotter software also supports Mode-S receivers which I haven’t looked into but maybe the standard ACARS transmissions don’t include the additional information.

Response on SC photographic beam break trigger

I received a prompt response from Jim Rowe at Silicon Chip Magazine on the beam break trigger. Interestlingly the pulse was designed to be short to work with the time delay trigger kit but I didn’t have any success using the two together, maybe some of the components supplied in the kit were out of spec:

Greetings Mr Johnson,

You are correct in that the effective trigger pulse width from the June 2009 Beam Break Trigger will be only a little over 110us, as determined by the 10uF capacitor and the total resistance of 11.1k# in the charging circuit.
The pulse was actually made this short to prevent multiple triggering of the Time Delay Trigger published in the February 2009 issue. However if you want to use the Beam Break Trigger to trigger a camera directly, I imagine that the pulse width will be too narrow – as you have pointed out.

Your remedy of increasing the capacitor value from 10nF to 1uF is fine, but if this does not give sufficient lengthening of the trigger pulse for some cameras, the resistor from the gate of Q2 to ground can also be increased in value from its present value of 10k#. It could be increased to 22k#, 47k# or even 100k# if a much longer pulse is needed.

Thank you for your feedback, and I hope you find the projects useful.

Jim Rowe

Silicon Chip photographic beam break trigger

Recently I constructed the beam break trigger and photoflash trigger kits featured in Silicon Chip magazine. Both look good in general however I ran into an issue getting the beam break kit to work with the trigger because of the short duration of the output. I thought I’d share a letter I just wrote to Silicon Chip about the problem in case anyone else runs into the same problem before it’s published in print form:

After constructing the beam break trigger from the June 2009 issue I didn’t have any luck getting it to trigger a camera either directly or via the photoflash trigger kit. Unless I’m missing something doesn’t the 10nF coupling between Q1 and Q2 lead to a time constant of mere microseconds? I didn’t measure the timing before-hand but after doing a rough calculation I placed a 1uF cap over the 10nF which lead to a trigger time of somewhere in the order of 10mS and all was fine with the photoflash trigger kit. I left it at that because it’s my intention to use it with the trigger kit however readers should be aware that some cameras require a longer pulse on their external trigger to fire. For example my Canon EOS 450D seems to require a minimum duration of about 60mS in manual focus mode, presumably if the pulse is shorter than the normal shutter lag time it gets ignored. Other than that they are a pair of excellent projects and looking forward to exploring the possibilities they offer.

Firefox AJAX requests from different domain / site

Recently I’ve been writing a GPS mapping web page based on the OpenLayers JavaScript library that retrieves GPS data from the server via AJAX. For development convenience I was just storing the HTML file on my local drive for development and fetching the XML data from a different SQL/Server 2008 machine. This worked fine from IE but from FireFox the XMLHttpRequest I was getting a status of 0 (zero) returned and no data.

It turns out this is a security measure to avoid cross-site scripting which is quite sensible but not that convenient for testing, and in this application the authentication happens at a different level so cross-site scripting isn’t an issue. I found the solution is to add an HTTP header of Access-Control-Allow-Origin: * to the response. Of course you should consider the implications carefully outside of a test environment. As my code is written as a traditional ASP file this just consisted of adding the following line:

response.AddHeader “Access-Control-Allow-Origin”, “*”

Hammer Linux Board

Recently I’ve been doing some work with a TinCan Tools Hammer embedded Linux board. The hammer board is a neat little piece of hardware in a 40-pin DIP form factor, it looks good from a hardware point of view although unfortunately lacking a bit in the documentation area. I had some recent frustration getting MMC/SD card support to work which turned out to be a problem in the driver, their Linux programmer tracked it down and supplied the new mach-tct_hammer.c file required to fix things from a driver point of view.

Then I had some frustration because using buildroot as per the supplied configuration was giving me different results to the filesystem loaded onto the device at the factory, namely that it ended up being mounted read only. An attempt to remount the root partition read/write complained that /proc/mounts was missing which I was able to solve by adding the following lines to the device_table.txt file:

/proc        d    777    0     0    –    –    –    –    –
/proc/mounts    d    777    0     0    –    –    –    –    –
/proc/devices    d    777    0     0    –    –    –    –    –

Once these lines were included the root filesystem was mounted read/write by default. I then went on to add the following to device_table.txt:

/dev/mmcblk0     b     777     0    0     179     0     –     –     –
/dev/mmcblk0p1     b     777     0    0     179     1     –     –     –
/mnt/mmc    d    777    0    0    –    –    –    –    –

After which I was able to successfully mount a 4GB FAT32 formatted SD card using the command:

mount /dev/mmcblk0p1 /mnt/mmc

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

End Sub