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
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
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
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