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