Stick-N-Find iBeacon

Written by Joshua Lyon on .

A summary of my findings with the Stick-N-Find stickers and their usability as an iBeacon / Bluetooth LE proximity device. I ordered a set of Estimote Beacons and Stick-N-Find stickers and have been playing with automation concepts within Android. In particular, I wanted to be able to have my Android device understand the proximity to a location and have a general understanding of indoor positioning.

Note: If you just want to know how to Configure your Stick-N-Find for use in Tasker, jump to Configure as iBeacon

Stick-N-Find Beacon and iPhoneEstimote iBeacon


Install Raspbian on 2GB Card

Written by Joshua Lyon on .

There are several options for running a Raspberry Pi operating system on a 2GB SD card, but in the this blog post I’d like to talk about how to install the ‘official’ distribution, Raspbian, on a smaller SD card. This method requires the minimal Raspbian Unattended Netinstaller which can be found at the following link:

The readme file that is shown on the base GitHub project linked above covers the basics of installing the operating system which I will briefly touch on here and the cover a few additional configuration options I choose to do. Some of the key things I like about the raspbian-ua-netinst project is that it fits on a 512 MB card, it downloads the required components through an internet connection via the Ethernet port, and it can do all the hard work completely unattended.

At a high level, here is what is needed to perform the install. (Reference the github link above for more detailed instructions):

  • Grab a 512 MB or larger SD card
  • Download the 10 MB installer package
  • Format the SD card as FAT32
  • Unpack the installer archive
  • Copy the unpacked files to the SD Card
  • Plug the SD card and a network cable into the Pi
  • Power up the Pi and let it install (this is the unattended part mentioned above)

Remove Directory from Git Repository

Written by Joshua Lyon on .

I recently started working with the integrated Version Control System (VCS) features of PyCharm and accidentally pushed my IDE / PyCharm project settings to my Google Code hosted Git repository. Basically, all of the files in the .idea folder of my project were pushed to the repository. While these files didn’t necessarily contain any proprietary or confidential information, I didn’t want them cluttering up my project repository. And while I could have simply removed the files and performed a commit, these files would have then been in the history of my repository.

I did some quick research and found a post on GitHub explaining how to remove a file along with its history from a GitHub repository. This command basically did what I needed to, but was designed to remove a single file at a time. Being that I wanted to remove a whole directory of files, I modified the command to remove the folder and recursively iterate through the directory removing children:

git filter-branch --force --index-filter \
'git rm -r --cached --ignore-unmatch .idea' \
--prune-empty --tag-name-filter cat -- --all

git push origin master --force

It should be noted that the command will overwrite existing tags and that if you had sensitive information in the files (like passwords), you should consider the information compromised and take appropriate action.


SQL Subquery / Function in Group By

Written by Joshua Lyon on .

I ran into an issue this week where I had written a table function (ITVF) to help convert a datetimeoffset column from UTC to the destination timezone within a query. Generally, we handle all date conversion at the application layer using simple .NET DateTime / TimeZone functions, but in this case we were trying to group by the adjusted date to display a count of events on a particular date. If the GROUP BY was run against the raw data, the resulting set would be grouped by the UTC timezone. As such, we needed to convert the timestamp to the destination timezone before performing the grouping.

When I first wrote the query, I attempted to simply group by the ITVF, but the following error message was thrown:

Msg 144, Level 15, State 1, Line 7
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

My first solution to this involved rewriting the core components of the ITVF statement directly into my query, but this made for an unwieldy and long query. Then last night I had an epiphany – why not just create the converted dataset as a view/subquery. I did a bit of quick research and to simplify my needs with integrating this timezone adjustment into an existing query, I decided on a Common Table Expression (CTE). The CTE allows us to define an expression that can be reused throughout the rest of the query similar to a table or view. The resulting solution is as follows:

DECLARE @timezone varchar(255)
SET @timezone = 'Central Standard Time'; /*.NET Timezone ID*/

    SELECT ID, AttributeID, Value, Status, /* original columns, plus the converted _time */
      (select * from TimezoneOffset.dbo.fn_ConvertToTimezoneTBL(@timezone, Time)) as TimeAdj
    FROM [Alarms].[dbo].[History]

select CONVERT(Date, TimeAdj, 1) as DateAdj, count(Value)
from AH
group by CONVERT(Date, TimeAdj, 1)