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.