GPS / Asset tracking AJAX web page

I’ve just completed an AJAX based GPS tracking page for a client based on the OpenLayers Javascript library using the OpenStreetMap map tile server for data, although it can be easily be adapted to other data sources. The following page offers a login to the demonstration system for evaluation:

(No longer available as it was based on an old version of OpenLayers, but I may still be interested in developing custom solutions using the latest version)

Data is served from an SQL database with a modular set of stored procedures translating data from its raw format allowing integratation with a wide range of data sources and GPS tracking devices. Columns displayed can be customised on a per-user basis and dynamically generated SQL queries allow great flexibility in the data generated. Please contact me if you are interesting in licensing the software for your own site.

SQL / Database

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

GPS / GIS Linux

OSM planet.osm disk requirements

After loading the planet.osm file (planet-090916.osm.bz2) into an Ubuntu 9.04 virtual machine the total disk usage was 133GB after the import. The total dropped to 115GB after doing a VMWare shrink operation however it looks like to account for future near-term expansion of the OSM data I should allow for at least 200GB of storage. I’d originally hoped to store the data on a small but fast SAS 15K RAID array on my server but the current version will only barely fit so I’ll need to consider other alternatives. It looks like costs are dropping fairly fast for some of the larger 15K SAS drives so I might defer the decision for a few months as the system isn’t required for production until the start of the year. Meanwhile I’ll just slip a 1TB SATA drive into the server for testing.

Programming SQL / Database

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

GPS / GIS Linux

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)

Programming SQL / Database

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


Electronics GPS / GIS

SatSleuth Electronic Circuit Diagrams

I maintain a list of electronic circuit diagrams at 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.

Electronics Photography

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

Electronics Photography

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.