Category: SQL / Database

Importing Helium Network Gateways

Post author By Peter Johnson
Post date March 19, 2022

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

GPS / GIS SQL / Database

Geocoding using OSM data and SQL/Server 2008

Post author By Peter Johnson
Post date January 29, 2010

I’ve just completed importing the OpenStreetMap data into SQL/Server 2008 using the new geography data types. Originally I linked the data to Manifold GIS to verify the import process but have just started on my first real project using the data which is address geocoding for a GPS tracking site located in Australia. The OSM data doesn’t contain enough street number information to be useful for most areas but the aim is to provide nearest street names at no charge, versus some local commercial geocoding services that charge around ten cents per address.

Loading the OSM ‘ways’ as a series of around 250,000 linestrings seems to be giving a performance of around 40 geocoding operations per second. As you’d expect performance of the STDistance method depends heavily on the distance, so in the SQL/Server stored procedure I first start by locating streets within 100 meters, followed by 1Km, 10Km and finally 100Km. The series of queries only attempts to find street names not matched by the previous query, and for Australia these numbers seem to give a match rate of about 90%, 9%, 1% and not much for the final distance that results in good performance.

For optimisation of the spatial index I found that changing the default of medium for each level in the index grid to high gave approximately a 100% increase in performance. It makes sense that the finest level of granularity would work best when looking for the nearest distance to points. The following was what I ended up using for the spatial index definition, changing the cells per object didn’t seem to have a great deal of effect for this dataset / application so I left it at the default value of 16.

CREATE SPATIAL INDEX [spatial_IX] ON [dbo].[Ways] 
(
	[LineString]
) USING  GEOGRAPHY_GRID 
WITH (
    GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
    CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]

SQL / Database

Using MySQL variables to limit a select query

Post author By Peter Johnson
Post date December 18, 2009

I discovered today when writing a stored procedure for a web reporting application that MySQL doesn’t support using variables for the LIMIT clause in a SELECT statement. I didn’t really want to write a dynamic query, one workaround that I found while Googling looked promising:

SET SQL_SELECT_LIMIT = RowLimit (can be a variable)
SELECT ….
SET SQL_SELECT_LIMIT = default

However it is applied at a global level and I needed different limits within a sequence of four queries formed into a union to get the top N results for a series of categories. Because I was giving the user the limited choice of retrieving the top 10, 20, 50 or 100 items I ending up using a CASE statement with each WHEN clause containing a duplicate of the entire query. Certainly not a pretty solution but works and is reasonably maintainable by leaving the LIMIT clause on a seperate line so any changes can be cut & pased down the procedure.

Programming SQL / Database

Using JET OLE DB under ASP and Vista x64

Post author By Peter Johnson
Post date November 1, 2009

I’m currently performing some work on classic ASP pages that during development are retrieving data from an existing Microsoft Access database. After receiving the following error I found that Microsoft haven’t and have no plans to release JET OLE DB drivers for 64-bit platforms. Fair enough it’s hardly the way of the future but I wanted to be able to perform some development on the pages before migrating to SQL/Server.

Provider=Microsoft.Jet.OLEDB.4.0
ADODB.Connection error ‘800a0e7a’
Provider cannot be found. It may not be properly installed.

After some digging around I found the solution was to change the IIS 7 settings to run the application in 32-bit mode. That can be performed under Vista x64 using the following steps:

Click Start > All programs > Administrative tools > Internet Information Server (IIS) Manager

Under the machine name click on Application Pools.

Right-mouse click on DefaultAppPool and select Advanced Settings…

Under the general section set “Enable 32-Bit Applications” to true.

Press OK and IIS should now be running in 32-bit mode, no other actions are necessary.

Programming SQL / Database

Creating compressed ZIP files using VBScript

Post author By Peter Johnson
Post date October 18, 2009

SQL/Server database backups tend to compress fairly well, in my experience typically down to around 25% of the original file size. However I discovered backup data compression is only a feature of SQL/Server 2008 Enterprise Edition and it’s not a feature that inspired me to spend thousands extra on a license. I’ve attached a VBScript procedure that I wrote to automatically compress my SQL/Server backups but of course it can be used for any application where you’d like to compress the entire contents of a directory automatically.

I can’t take credit for the innovative technique of creating a ZIP header in code to make Windows create a compressed ZIP folder. That was a part of a code snippet I found elsewhere quite some time ago. For some reason the MoveHere method of Shell.NameSpace didn’t seem to remove the source file so when move mode is selected I stuck with deleting the source file afterwards. This version displays ZIP compression progress in a dialog box but you could use “ZipFile.CopyHere InFilename, 4” to disable the progress dialog box.

' VBScript to move or copy all files in a folder to a compressed ZIP file

Option Explicit
Const MoveMode = False
Const BackupDir = "E:\DB Backups"
Const TimeoutMins = 10 ' Timeout for individual file compression operation

Sub MoveToZip(InFilename, OutFilename)
	Dim FSO : Set FSO = CreateObject("Scripting.FileSystemObject")
	Dim Timeout : Timeout = 0
	FSO.CreateTextFile(OutFilename, true).WriteLine "PK" & Chr(5) & Chr(6) & String(18, 0)
	Dim Shell : Set Shell = CreateObject("Shell.Application")
	Dim ZipFile: Set ZipFile = Shell.NameSpace(OutFilename)
	ZipFile.CopyHere InFilename
	Do Until ZipFile.items.Count = 1  or Timeout > TimeoutMins * 600
		Wscript.Sleep 100
		Timeout = Timeout + 1
	Loop
	If MoveMode and ZipFile.items.Count = 1 Then FSO.DeleteFile(InFilename)
	Set Shell = Nothing
	Set FSO = Nothing
	Set ZipFile = Nothing
End Sub

Dim FSO : set FSO = CreateObject("Scripting.FileSystemObject")
Dim Folder : Set Folder = FSO.GetFolder(BackupDir)
Dim Files : Set Files = Folder.Files
Dim File
For Each File In Files
	If InStr(UCase(File.Name), ".ZIP") = 0 Then
		MoveToZip BackupDir & "\" & File.Name, BackupDir & "\" & FSO.GetBaseName(File.Name) & ".zip"
	End If
Next

SQL / Database

MySQL: Access denied for user ‘root’@’localhost’ (using password: YES)

Post author By Peter Johnson
Post date October 12, 2009

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

Programming SQL / Database

SQL convert integer to hexadecimal string

Post author By Peter Johnson
Post date October 6, 2009

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.

CREATE FUNCTION ConvertToHex2
(
@pNumber int
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @HexDigits varchar(16)
DECLARE @Digit1 int
DECLARE @Digit2 int
DECLARE @ResultVar varchar(2)

IF @pNumber IS NULL
SET @ResultVar = NULL
ELSE BEGIN
SET @HexDigits = ‘0123456789ABCDEF’
SET @Digit1 = @pNumber / 16 + 1
SET @Digit2 = @pNumber % 16 + 1
SET @ResultVar = SUBSTRING(@HexDigits, @Digit1, 1) + SUBSTRING(@HexDigits, @Digit2, 1)
END
RETURN @ResultVar
END

Programming SQL / Database

SQL/Server function to format integer IP address

Post author By Peter Johnson
Post date October 2, 2009

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 ‘1.2.3.4’. I’m using it as part of a geolocation by IP address project.

CREATE FUNCTION IP2String (@IPAddress bigint)
RETURNS varchar(15)
AS
BEGIN
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

END

Programming SQL / Database

Application.FileSearch in MS Access 2007

Post author By Peter Johnson
Post date April 18, 2009

I recently wrote a small application for someone using Microsoft Access 2003 and found the Application.FileSearch method of enumerating files is no longer available. The older “dir” function is still available so I replaced it with the following code to change the current drive and path and enumerate the files. Note this code will only work for local drive paths, not UNC style network paths.

Private Sub Form_Activate()

Dim i As Integer
Dim SearchPath As String
Dim Filename As String

SearchPath = GetPath() & "Backups"
ChDrive Mid$(SearchPath, 1, 2)
ChDir SearchPath
Filename = Dir("*.csv")
While Filename <> ""
  RestoreList.AddItem (Filename)
  Filename = Dir
Wend

End Sub

Recent Posts

Recent Comments

Archives

Categories