Categories
LoRaWAN Radio Communications SQL / Database

Importing Helium Network Gateways

With the Telemetry2U LoRaWAN IoT platform now supporting Helium Network integration I wanted to import the complete list of Helium gateways into SQL/Server for further analysis. The network is growing fast with over 650,000 Helium gateways currently registered of with just shy of 500,000 showing as active.

The following C# code was used in LINQPad to perform the import. It takes around thirty minutes and the current size is around 300MB so can be imported into the Express edition of SQL/Server. The code backs off for one minute when a 429 Too Many Requests is received from the Helium API, please don’t abuse the Helium Network by reducing that limit or running this script too often.

async Task Main()
{
	client.DefaultRequestHeaders.Add("User-Agent", "C# App");
	var baseUrl = "https://api.helium.io/v1/hotspots";
	var lastCursor = "";
	bool moreData = true;
	int curRecords = 0;
	while (moreData)
	{
		var url = baseUrl;
		if (lastCursor.Length > 0)
		{
			url += "?cursor=" + lastCursor;
		}
		int retries = 0;
		string json = "";
		while (json.Length == 0)
		{
			try
			{
				json = "";
				HttpResponseMessage response = await client.GetAsync(url);
				response.EnsureSuccessStatusCode();
				json = await response.Content.ReadAsStringAsync();
			}
			catch (HttpRequestException ex)
			{
				Console.WriteLine(ex.Message);
				if (retries > 100)
				{
					throw ex;
				}
				else
				{
					retries++;
					await Task.Delay(TimeSpan.FromMinutes(1));
				}
			}
		}
		Root data = JsonSerializer.Deserialize<Root>(json);
		foreach (var datum in data.Data)
		{
			Hotspots.Add(new Hotspots {
				Address = datum.Address,
				Block = datum.Block,
				BlockAdded = datum.BlockAdded,
				CityId = datum.Geocode.CityId,
				Elevation = datum.Elevation,
				Gain = datum.Gain,
				Height = datum.Status.Height,
				LastChangeBlock = datum.LastChangeBlock,
				LastPocChallenge = datum.LastPocChallenge,
				Lat = datum.Lat,
				Lng = datum.Lng,
				Location = datum.Location,
				LocationHex = datum.LocationHex,
				LongCity = datum.Geocode.LongCity,
				LongCountry = datum.Geocode.LongCountry,
				LongState = datum.Geocode.LongState,
				LongStreet = datum.Geocode.LongStreet,
				Mode = datum.Mode,
				Name = datum.Name,
				Nonce = datum.Nonce,
				Online = datum.Status.Online,
				Owner = datum.Owner,
				Payer = datum.Payer,
				RewardScale = datum.RewardScale,
				ShortCity = datum.Geocode.ShortCity,
				ShortCountry = datum.Geocode.ShortState,
				ShortState = datum.Geocode.ShortState,
				ShortStreet = datum.Geocode.ShortStreet,
				Timestamp = datum.Status.Timestamp,
				TimestampAdded = datum.TimestampAdded
			});
			curRecords++;
		}
		await SaveChangesAsync();
		Console.WriteLine($"Saved {curRecords} records");
		moreData = data?.Cursor?.Length > 0;
		if (moreData)
		{
			lastCursor = data.Cursor;
		}
	}
}

static readonly HttpClient client = new HttpClient();

public record Status(
	[property: JsonPropertyName("timestamp")] DateTime? Timestamp,
	[property: JsonPropertyName("online")] string Online,
	[property: JsonPropertyName("listen_addrs")] IReadOnlyList<string> ListenAddrs,
	[property: JsonPropertyName("height")] int? Height
);

public record Geocode(
	[property: JsonPropertyName("short_street")] string ShortStreet,
	[property: JsonPropertyName("short_state")] string ShortState,
	[property: JsonPropertyName("short_country")] string ShortCountry,
	[property: JsonPropertyName("short_city")] string ShortCity,
	[property: JsonPropertyName("long_street")] string LongStreet,
	[property: JsonPropertyName("long_state")] string LongState,
	[property: JsonPropertyName("long_country")] string LongCountry,
	[property: JsonPropertyName("long_city")] string LongCity,
	[property: JsonPropertyName("city_id")] string CityId
);

public record Datum(
	[property: JsonPropertyName("lng")] double Lng,
	[property: JsonPropertyName("lat")] double Lat,
	[property: JsonPropertyName("timestamp_added")] DateTime TimestampAdded,
	[property: JsonPropertyName("status")] Status Status,
	[property: JsonPropertyName("reward_scale")] double? RewardScale,
	[property: JsonPropertyName("payer")] string Payer,
	[property: JsonPropertyName("owner")] string Owner,
	[property: JsonPropertyName("nonce")] int Nonce,
	[property: JsonPropertyName("name")] string Name,
	[property: JsonPropertyName("mode")] string Mode,
	[property: JsonPropertyName("location_hex")] string LocationHex,
	[property: JsonPropertyName("location")] string Location,
	[property: JsonPropertyName("last_poc_challenge")] int? LastPocChallenge,
	[property: JsonPropertyName("last_change_block")] int LastChangeBlock,
	[property: JsonPropertyName("geocode")] Geocode Geocode,
	[property: JsonPropertyName("gain")] int Gain,
	[property: JsonPropertyName("elevation")] int Elevation,
	[property: JsonPropertyName("block_added")] int BlockAdded,
	[property: JsonPropertyName("block")] int Block,
	[property: JsonPropertyName("address")] string Address
);

public record Root(
	[property: JsonPropertyName("data")] IReadOnlyList<Datum> Data,
	[property: JsonPropertyName("cursor")] string Cursor
);

To run the above code you’ll need to create a database called Helium or similar and use the following SQL DDL to create the table. Then the two can be linked as a database connection within LINQPad.

async Task Main()
{
	client.DefaultRequestHeaders.Add("User-Agent", "C# App");
	var baseUrl = "https://api.helium.io/v1/hotspots";
	var lastCursor = "";
	bool moreData = true;
	int curRecords = 0;
	while (moreData)
	{
		var url = baseUrl;
		if (lastCursor.Length > 0)
		{
			url += "?cursor=" + lastCursor;
		}
		int retries = 0;
		string json = "";
		while (json.Length == 0)
		{
			try
			{
				json = "";
				HttpResponseMessage response = await client.GetAsync(url);
				response.EnsureSuccessStatusCode();
				json = await response.Content.ReadAsStringAsync();
			}
			catch (HttpRequestException ex)
			{
				Console.WriteLine(ex.Message);
				if (retries > 100)
				{
					throw ex;
				}
				else
				{
					retries++;
					await Task.Delay(TimeSpan.FromMinutes(1));
				}
			}
		}
		Root data = JsonSerializer.Deserialize<Root>(json);
		foreach (var datum in data.Data)
		{
			Hotspots.Add(new Hotspots {
				Address = datum.Address,
				Block = datum.Block,
				BlockAdded = datum.BlockAdded,
				CityId = datum.Geocode.CityId,
				Elevation = datum.Elevation,
				Gain = datum.Gain,
				Height = datum.Status.Height,
				LastChangeBlock = datum.LastChangeBlock,
				LastPocChallenge = datum.LastPocChallenge,
				Lat = datum.Lat,
				Lng = datum.Lng,
				Location = datum.Location,
				LocationHex = datum.LocationHex,
				LongCity = datum.Geocode.LongCity,
				LongCountry = datum.Geocode.LongCountry,
				LongState = datum.Geocode.LongState,
				LongStreet = datum.Geocode.LongStreet,
				Mode = datum.Mode,
				Name = datum.Name,
				Nonce = datum.Nonce,
				Online = datum.Status.Online,
				Owner = datum.Owner,
				Payer = datum.Payer,
				RewardScale = datum.RewardScale,
				ShortCity = datum.Geocode.ShortCity,
				ShortCountry = datum.Geocode.ShortState,
				ShortState = datum.Geocode.ShortState,
				ShortStreet = datum.Geocode.ShortStreet,
				Timestamp = datum.Status.Timestamp,
				TimestampAdded = datum.TimestampAdded
			});
			curRecords++;
		}
		await SaveChangesAsync();
		Console.WriteLine($"Saved {curRecords} records");
		moreData = data?.Cursor?.Length > 0;
		if (moreData)
		{
			lastCursor = data.Cursor;
		}
	}
}

static readonly HttpClient client = new HttpClient();

public record Status(
	[property: JsonPropertyName("timestamp")] DateTime? Timestamp,
	[property: JsonPropertyName("online")] string Online,
	[property: JsonPropertyName("listen_addrs")] IReadOnlyList<string> ListenAddrs,
	[property: JsonPropertyName("height")] int? Height
);

public record Geocode(
	[property: JsonPropertyName("short_street")] string ShortStreet,
	[property: JsonPropertyName("short_state")] string ShortState,
	[property: JsonPropertyName("short_country")] string ShortCountry,
	[property: JsonPropertyName("short_city")] string ShortCity,
	[property: JsonPropertyName("long_street")] string LongStreet,
	[property: JsonPropertyName("long_state")] string LongState,
	[property: JsonPropertyName("long_country")] string LongCountry,
	[property: JsonPropertyName("long_city")] string LongCity,
	[property: JsonPropertyName("city_id")] string CityId
);

public record Datum(
	[property: JsonPropertyName("lng")] double Lng,
	[property: JsonPropertyName("lat")] double Lat,
	[property: JsonPropertyName("timestamp_added")] DateTime TimestampAdded,
	[property: JsonPropertyName("status")] Status Status,
	[property: JsonPropertyName("reward_scale")] double? RewardScale,
	[property: JsonPropertyName("payer")] string Payer,
	[property: JsonPropertyName("owner")] string Owner,
	[property: JsonPropertyName("nonce")] int Nonce,
	[property: JsonPropertyName("name")] string Name,
	[property: JsonPropertyName("mode")] string Mode,
	[property: JsonPropertyName("location_hex")] string LocationHex,
	[property: JsonPropertyName("location")] string Location,
	[property: JsonPropertyName("last_poc_challenge")] int? LastPocChallenge,
	[property: JsonPropertyName("last_change_block")] int LastChangeBlock,
	[property: JsonPropertyName("geocode")] Geocode Geocode,
	[property: JsonPropertyName("gain")] int Gain,
	[property: JsonPropertyName("elevation")] int Elevation,
	[property: JsonPropertyName("block_added")] int BlockAdded,
	[property: JsonPropertyName("block")] int Block,
	[property: JsonPropertyName("address")] string Address
);

public record Root(
	[property: JsonPropertyName("data")] IReadOnlyList<Datum> Data,
	[property: JsonPropertyName("cursor")] string Cursor
);
Categories
Radio Communications Tasmania Police Scanner

Tasmanian police scanner part 4

There’s a new location for the Tasmania police scanner that allows secure TLS (HTTPS) connections to support more mobile devices and newer browsers. The new page is available at:

Tasmania Police Scanner

The scanner still receives from the Mount Wellington repeater so the majority of traffic is for Hobart and the remainder of Southern Tasmania. It will be available between approximately 9:00AM – Midnight AEST (Australian Eastern Standard Time) while I have my PC running.

While the existing page at peter-johnson.com.au:8000 will remain available for the moment please update your bookmarks to use the new page.

Now that it’s running on a separate site over time some more information will be added such as Tasmanian police frequencies and information on configuring your own EDACS scanner.

Categories
Hardware

Current draw of Dewalt DCL060 20V Max LED Worklight

I recently purchased a Dewalt DCL060 area worklight and decided to measure the current draw so I could calculate the rough battery lifetime. With a lab PSU set to 18V which is the nominal voltage the current draw was 1.37A. Here’s a table of roughly how long it will last for the common battery capacities that I know of:

Capacity (Ah)   Battery life (hours)
1.3		0.9
2		1.5
3		2.2
4		2.9
5		3.6
6		4.4
9		6.6
Categories
Radio Communications Tasmania Police Scanner

Tasmanian police scanner part 3

The police scanner had some reliability issues running under Windows Server 2012 because the USB drivers for the sound capture device weren’t 100% compatible with Windows Server and after some time would get in a state where audio capture wouldn’t work until the system was rebooted. Quite a few people reported problems connecting to the scanner and that was the reason for it being unreliable. After upgrading to Windows Server 2016 the driver stopped working altogether so I decided to remove the scanner from the server and attach it to my PC.

The good news is because the USB capture device has supported Windows 10 drivers it appears to be working well now and I haven’t seen any reliability issues yet. The bad news is that I don’t leave the PC running overnight so it won’t be available between about midnight and 8:00AM Australian Eastern Time. The scanner is still available at the address below, note that the password field is for some administrative functions that I don’t have enabled so you don’t need a password to listen to the feed:

http://www.peter-johnson.com.au:8000/

Categories
Hardware

Current draw of Dewalt DCL050 20V Max LED Hand Held Area Light

I recently purchased a Dewalt DCL050 light and while reading reviews there seemed to be a lot of questions about current draw / battery life. Dewalt claim a maximum of 22 hours but that’s about all I could find in official documentation so I thought I’d attach it to a lab power supply and measure what it actually draws. I had the PSU set to 18V which is the nominal voltage and results were as follows:

Low setting (250 lumens) = 240 mA
High setting (500 lumens) = 460 mA

With the low setting the instantaneous current draw skips around because of the PWM used to dim the LEDs so the above is a 10 second average. The largest Dewalt battery is 5 Ah and 5 / .24 = 20.8 so the 22 hour maximum sounds plausible given that batteries are normally rated for a 10 hour discharge and often perform a little better at lower discharge rates. Here’s a little table of roughly what it would be for the common battery capacities that I know of:

Capacity (Ah)   Low setting    High setting
1.3                 5 hrs           3 hrs  
  2                 8 hrs           4 hrs
  3                13 hrs           7 hrs
  4                17 hrs           9 hrs
  5                21 hrs          11 hrs
  6                25 hrs          13 hrs
Categories
Radio Communications Tasmania Police Scanner

Tasmanian police scanner part 2

Recently I’ve upgraded the RadioFeed software to V2.2 which seems to resolve some issues a few people (including myself) were having with mobile devices. Also a user Brett mentioned a lack of Glenorchy / Hobart traffic. I checked into it and the channel S23-Hobart had been locked out which I’ve now resolved so it makes for more interesting listending for anyone that hasn’t tried in a while, just go to the following URL:

http://www.peter-johnson.com.au:8000/

Categories
Operating systems

Windows 7 empty recycle bin “hanging” with a lot of files

I was just cleaning up a directory path and had over 1GB and several thousand files in my recycle bin. About 5 minutes after selecting “Empty Recycle Bin” I still hadn’t even got as far as the prompt asking to confirm the delete. After some system monitoring I saw that Microsoft Security Essentials was attempting to scan the files. After temporarily disabling real-time protection the prompt immediately appeared and the bin emptied within 10 seconds or so.

Rather interesting that they scan files that are about to be deleted. The .NET FileSystemWatcher has a deleted method as does the underlying Win32 API so it’s not hard to detect.

Categories
Linux Operating systems

Rasberry Pi initial configuration

Posting my initial Raspberry Pi configuration steps to enable SSH / FTP / VNC and Wifi to a WPA2 access point in case it’s of use to anyone else getting started with the Pi. WPA connection is automatic, I’ve left VNC manually started to save resources when not in use, it can be started via SSH or the console using the following command:

vncserver :1 -geometry 1024x768 -depth 16 -pixelformat rgb565

# loaded debian6-19-04-2012.img to SD card
# expanded main partition using gparted on another machine
sudo /boot/boot_enable_ssh.rc
uname -a
# Linux raspberrypi 3.1.9+ #90 Wed Apr 18 18:23:05 BST 2012 armv6l GNU/Linux
sudo dpkg-reconfigure tzdata
sudo nano /etc/dhcp/dhclient.conf # set host-name
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install ca-certificates git-core proftpd tightvncserver
sudo wget http://goo.gl/1BOfJ -O /usr/bin/rpi-update
sudo chmod +x /usr/bin/rpi-update
sudo rpi-update # ignore error about libvchiq_arm.so
sudo reboot
uname -a
# Linux raspberrypi 3.1.9+ #159 PREEMPT Wed Jul 11 19:54:53 BST 2012 armv6l GNU/Linux
wpa_passphrase <SSID> <Passphrase> # copy hex from psk= and paste into <PSK> below
sudo nano /etc/network/interfaces
# append following to bottom:
auto wlan0
iface wlan0 inet dhcp
wpa-ssid <SSID>
wpa-psk <PSK>
Categories
Radio Communications Tasmania Police Scanner

Tasmanian Police Scanner

I’ve just installed Radio Feed 1.5 on my server, a nice free piece of software for radio scanner streaming. It’s connected to a Uniden UBCD396T set to receive the Tasmanian Government EDACS radio network used by Tasmania Police. It’s set to pickup communications from Mount Wellington so only covers Southern Tasmanian. No guarantees on uptime but feel free to give it a try at:

http://www.peter-johnson.com.au:8000

Categories
Photography

Canon 7D native ISO tests

Recently I picked up a Canon 7D which I’m really enjoying so far. While researching the camera I found various references to both 100 and 160 being the native ISO and as I do quite a bit of still photography from a tripod was keen to determine the optimum ISO setting and also the best ISO multiples to use for available light shooting. Between various forum posts there seemed to be about a 50/50 mix of people suggesting one or the other was the native ISO so I decided to do my own test. I printed an ISO 12233 test chart replica from Cornell University on an A4 piece of paper and set it up inside a lightbox in a room with no direct sunlight. I shot using the 7D with a Canon 70-200mm f/2.8L IS USM II lens at f/2.8 at around 100mm focal length, tripod mounted with a remote cable release, manually focused once at the start of the sequence with the camera set to aperture priority mode. While I’m sure there are plenty of things that could be done better I thought this was typical of the kind of lighting situation where I’d be tempted to knock up the ISO speed. I imported the RAW files in Adobe Lightroom 3 beta 2 and didn’t perform any operations other than changing the colour balance and exposure equally on all shots to lighten them up for printing.

The results? ISO 100 and 160 were very close, but I felt 160 was just a little more defined so I might settle on that for tripod shooting. I don’t have an expert eye but I couldn’t spot any nasty steps in the ISO range that other’s have reported, in my mind it was a pretty even transition with the each ISO speed. ISO 400 remained excellent so I wouldn’t hesitate to go that high for quality shots. What I’d set out to find is if automatic ISO was a useful mode and I feel that it is. From what I gather Canon don’t release specific details on the native ISO of their cameras but the results made me wonder if on a modern DSLR it’s a myth, I’d assume the ISO setting equates to a sampling period for the sensor and can’t think of any reason it couldn’t be infinitely variable over a range. Of course at the lower end of the range there could be an optimum level such as 160 below which the sensor becomes saturated and needs to be attenuated which may explain why ISO 160 appeared marginally better than ISO 100.