Geolocation of mobile phones in the GSM network

As user of mobile phones we are used to have an almost 100% coverage for phone calls (not for data though) and the user-experience is absolutely seamless in most urban and sub-urban areas in Europe and most other countries. Moving around in trains and cars we cant sense the hand-over in the background of the GSM (2G, GPRS, EDGE), UMTS (3G), LTE (4G) network passing on our connection between the BTS ( base transceiver station). As regular user we dont have an idea about the number and location of cell towers around us, some towers or antennas are mounted on very obvious structures (antennas on towers), some are almost hidden. Though the GSM antennas can have a range of up to 35km (flat plane vs less than 5km in hilly areas), we have much higher density of cells in the urban area with antennas almost every few 100 metres or less. There are a lot of parameters influencing the infrastructure and its layout at a certain place, I wont dive into the details of if, you can get some info on the reference sites listed at the end of the article.

Rather approaching this topic hands-on, I was curious about the information that I can retrieve with Android about the active cells, its location and ultimately about the information the network operator (or other interested parties) collects about one. We might disable the GPS function of a phone to stop apps to collect our whereabouts, giving apps access to the phone state still gives a coarse location profile.

Usually phones dont reveal any network information other than the network operator but with the help of some regular Android methods of the TelephonyManager and GsmCellLocation class we get the crucial information.

The key info we are looking for is

  • MCC – Mobile Country Code
  • MNC – Mobile Network Code
  • LAC – Location Area Code
  • CELLID – The ID of the cell

Only the combination of the above 4 values is unique and can identify the location. You can look at a directory of MCC codes at Wikipedia, but there is no list of of LAC and CELLID codes published by the provider. But in the era of the “crowd” there is a collaborative community collecting the measurements of cellphones and putting them into a DB (CC-BY-SA 3.0). At  opencellid.org you can both retrieve information about cell towers as well download the complete DB.

OpenCellID.org - cell info

OpenCellID.org – cell info

I was curious how often we change the cell and what geo information I could retrieve from this, respectively build a geo profile of myself moving around. I build an app the listens to the currently active cell, lists it up on the screen and logs it into a csv files, and optionally gives an acoustic notification (beep) every time cell has changed.

MyCellDroid Beta App

MyCellDroid Beta App

Quite surprising, during one of the first tests, driving a 100km distance along the highway, both rural and suburban area, I passed through more than 80 cells !

Relevant Android methods

TelephonyManager telephonyManager = (TelephonyManager) this.getSystemService(Context.TELEPHONY_SERVICE);
mcc = telephonyManager.getNetworkOperator().substring(0, 3);
mnc = telephonyManager.getNetworkOperator().substring(3);
operator = telephonyManager.getNetworkOperatorName();

GsmCellLocation location = (GsmCellLocation) telephonyManager.getCellLocation();
lac = location.getLac();
cellid = location.getCid();

Be aware of the permissions required

 <uses-permission android:name="android.permission.READ_PHONE_STATE" />
 <uses-permission android:name="android.permission.ACCESS_COARSE_LOCATION" />

A extra note about Android 6.x+ development, Google changed massively the permission concept and apps require an additional confirmation of the required permissions (for so-called dangerous permissions) during runtime, this has to be implemented specifically, otherwise your application which runs on earlier Android versions will crash. I will share the relevant implementation in an upcoming entry.

Note for Samsung Phones: The function to get information about the nearby cells is not supported by Samsung phones

List<NeighboringCellInfo> neighborCells = telephonyManager.getNeighboringCellInfo();

This list will all be null on Samsung phones. You only can retrieve information about the currently connected cell.

I will try to make some more sense out of the collected data and see how fine-grain the collected data reveals my location.

Btw, there are dozens of similar apps in the Playstore and some even report back collected data to improve and build up the opencellid project database.

References:

  1. https://en.wikipedia.org/wiki/Cell_site
  2. https://en.wikipedia.org/wiki/Base_transceiver_station
  3. https://en.wikipedia.org/wiki/Sector_antenna
  4. https://en.wikipedia.org/wiki/Cellular_network
  5. https://en.wikipedia.org/wiki/Mobile_country_code
  6. https://developer.android.com/reference/android/telephony/NeighboringCellInfo.html
  7. https://developer.android.com/reference/android/telephony/TelephonyManager.html
  8. https://developer.android.com/reference/android/telephony/gsm/GsmCellLocation.html
  9. https://developer.android.com/guide/topics/security/permissions.html
Advertisements

Exploring a SQLite Database on Android

or “How to read SQLite DB from a desktop”

SQLite is the relational, embedded, ACID compliant database that comes with Android. Due to this fact it is certainly the most deployed DB engine on this planet. In case your application need to have CRUD features for local persisted data and the complexity level is beyond a simple text file, you have to consider it.

A challenge is to look into the (raw) DB from your desktop (if you dont want to build and integrate a DB viewer into your app). As Android apps store databases into their respective /data subfolder and if you don’t have a rooted phone, you cant look inside this folder.

I am not aware of any tool that can open a connection to the DB remotely, so the best way is to copy the DB file into the accessible SD card (or whatever the phone and its manufacturer considers as SD card, even the internal memory mounted as SD card), download it to your desktop and open it with a tool like the SQLite DB Browser.

Let’s put some sourcecode here as reference

Create a simple demo DB

No bells and whistles, no helper classes, etc. just the most simple way to create DB and a table.

 
    private void createDB() {
        SQLiteDatabase sampleDB = this.openOrCreateDatabase("MYDEMODB", MODE_PRIVATE, null);
        sampleDB.execSQL("CREATE TABLE IF NOT EXISTS MYTABLE (Last VARCHAR, First VARCHAR, Role VARCHAR);");
        sampleDB.execSQL("INSERT INTO MYTABLE Values ('Smith','John','CEO');");
        sampleDB.execSQL("INSERT INTO MYTABLE Values ('Thomson','Allan','CTO');");
        sampleDB.close();
    }

Copy the DB to your SD card

 
    private void exportDB() {
        File mySd = Environment.getExternalStorageDirectory();
        File myData = Environment.getDataDirectory();

        FileChannel src = null;
        FileChannel des = null;
        String currentDBPath = "/data/" + getApplicationContext().getPackageName() + "/databases/MYDEMODB";
        String exportDBPath = "MYDEMODB";

        File currentDB = new File(myData, currentDBPath);
        File backupDB = new File(mySd, exportDBPath);
        try {
            src = new FileInputStream(currentDB).getChannel();
            des = new FileOutputStream(backupDB).getChannel();
            des.transferFrom(src, 0, src.size());
            src.close();
            des.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

After download to your local drive you can use the SQLite Browser to open the file. Very useful data debugging or for apps that collect data and you can’t implement an upload of the data to a server via the internet connection.

SQLite DB Browser

SQLite DB Browser (Structure View)

 

SQLite DB Browser

SQLite DB Browser (Data View)

Hands-On Amazon Echo Dot and Alexa

Amazon Echo, the voice-controlled and hands-free device/speaker was already launched in the US in November 2014, now 2 years later the Echo, and the Echo Dot second generation, is available in Europe. In Germany it was soft-launched in late October on an invitation base at Euro 59.99, the bigger Echo at Euro 179.-.

Amazon Echo Dot

Amazon Echo Dot

Curious enough about having a glimpse into our household and workplace (?) future I requested one and got it delivered last week Friday. At the size of a hockey puck, the device contains 7 microphones, a simple loudspeaker, WLAN and Bluetooth connectivity. No battery, so the Echo must be connected to a USB power adapter at all times. I must admit, the idea of having a “spy” device with microphones permanently listening into my room brings up some privacy concerns, though Amazon claims only the keyword (Alexa, Echo or Amazon) is activating the device, it’s LED ring starts to turn blue, and the spoken commands get transferred to the Amazon cloud, using the Alexa Voice Recognition Service, on which Amazon supposedly spend a 100 million dollars.

Amazon Echo Dot

Amazon Echo Dot

Here a first hands-on experience resume:

Being an Amazon user with a Prime account and already a Kindle and a Fire HD tablet at home, the setup takes less than 5 minutes, inclusive of setting up a WLAN connection from the Alexa App to the device, preparing the WLAN access from the device to your AP and connecting it via Bluetooth to the home theater system. The device is woken up with the keyword or by pressing one of the four buttons on top of it, followed by your question or command.

It does not run a conversational model in the basic use cases, though the skill sets support sessions ! You raise a question or trigger a command, that’s it. It wont ask back (yet). It will respond with the right answer or execute what you have asked for, or respond it if it does not understand you, sometimes it wont do anything at all after activation other than showing the blue ring (maybe due to noisy environment). The basic services available are rather simple or move around the Amazon product landscape, most prominently playing music on demand from the Amazon Prime Music offerings, ordering products from Amazon or responding with the weather info or respond to simple Wikipedia style questions. The power of the device is unfolding with the skill-sets that allow third parties to offer services based on the Alexa services. This can be house-automation, ordering pizzas and other consumer services. Being a regional feature there are about 3,000 skills available in the US but only about 2 dozens in Germany at the time of writing.

My kids had a Sunday afternoon fun time to play with it and trying to fool with it, though at this stage it wears out pretty fast after hearing “I don’t understand your request” and similar responses if you leave its pre-programmed comfort zone (it is interesting to observe how kids approach such a device). Be aware of the Eliza Effect using a device with a synthetic voice and human-like response.

What makes it particularly interesting to me is the evaluation of a completely voice based service and the platforms extensibility through the Alex Skill Set and the API’s that Amazon provides. You find lots of information at the Amazon developer portal and you can even join the Mashup Contest.

In short, right now it is still a toy but with lots of opportunities to come up in the near future. I will look at the potential use cases in a aviation environment, both operational and as passenger and keep you posted.

Amazon Echo Dot

Amazon Echo Dot

While using the Echo I feel a bit like talking to Hal 9000 in the 1968 film “2001: A Space Odyssey” directed by Stanley Kubrick. Echo does not yet have an attitude.

Visualization Use Case Part 2: Airline Arrival Delays in the US (Tableau)

After reviewing the flaws of the previous visualization of the DOT Airline performance data in part 1, I created an improved version with the same recordsets. It is a separate viz because the first version have some mistakes due to the number conversion during the csv import. I cleaned up, checked the data and used calculated fields to derive the sum of delays.

Airline Performance in the US 2015

Airline Performance in the US 2015

The basic concept is still the same, the matrix on the top left controls the dashboard, initially you see all data for 2015 combined, clicking into cells drills down.
I changed the barchart to stacked bars comparing total to delayed flight in one bar for each month.

Bar Chart

Bar Chart

I moved the split delay reasons into a separate bar chart and added a pie chart which reveals the main reason for delays (surprisingly weather and security have the smalles share!) The 2 lists are a Top 10 style lists highlighting the airports and airlines with the most delays.

Performance

Airport Performance

 

Performance

Airline Performance

 

How does the visualization transport information ? Let’s look at the strong and weak points of the second iteration.

+ The key information presentation is improved. We can see the viz is about delays.

 The dashboard starts to look a bit disorganized and the viewer eyes are moving around without a centre of attention.

+ The barchart now makes sense, you can compare total flights and delays.

– The detail delay reason over time does not create too much value as the distribution of reason is quite similar.

Conclusion: Spending more time on both data and visualizations improved the overall impact, though a bit cluttered.

Lets try to apply to some more tweaking..

Visualization Use Case Part 1: Airline Arrival Delays in the US (Tableau)

Going beyond sample datasets and basic visualizations I was looking for open data in my professional domain, the aviation and airport industry. Potential candidates for visualizations are connections, routes, flight plans, airport and airline performance. Performance is usually the comparison of scheduled operations vs. actual milestones. The delay of arriving or departure flights is not only affecting passengers and many parties inside and outside the airport community, but it is driving sentiments, perception and reputation and eventually costs money. This kind of data is not something operators like to release but thanks to the Freedom of Information Act (FOIA), a US Federal law, public gets access to all kind of statistics. From the US DOT (Department of Transportation) you can access and download a variety of datasets, one of them is the On-Time Arrival Performance of US airlines in the US and their delay causes since the year 2003 (link). You can filter by airline, airport and timeframe, review the summary on the DOT website or download the set as CSV for your own analysis. I downloaded the complete dataset for 2015, a 2,25 MB file with roughly 13.500 records.

Arrival Delays in Tableau

Arrival Delays in Tableau

 

Airline Delays in the US in 2015 by DOT

Airline Delays in the US in 2015 by DOT

 

It provides total arriving flights, cancelled and diverted flights, the delay count and total time by reason (weather, carrier, NAS, security, late aircraft) for each month-airport-airline combination for 14 carriers at 322 airports.

Airline Delays in the US in 2015 by DOT

Continue reading

Big Data – How to create it or get access to it?

Big Data – Nothing new anymore. Occasionally still used as buzzword, but for many company it became a productive tool to analyze huge volumes of data. I noticed the term Big Data disappeared from the 2015 and 2016 Gartner report “Hype Cycle for Emerging Technologies Identifies the Computing Innovations That Organizations Should Monitor” (compare the 2013 and 2014 report), I guess it jumped straight into the plateau of productivity.

We know the definition (volume, velocity, variety, plus variability and complexity), read books about, join conferences and meetups but for a developer (outside a corporate environment with access to some kind of big data) the question remains “How do I get Big Data”. While we have access to the various tools and platforms there is no stream we can easily tap in. Of course you could create random data in the millions but this would not create content that you could analyze. There are efforts to publish data accesible to the public (open data), but it is hardly a large volume and it is usually not streaming.

In the attempt to get at least a small portion of Big Data, I only found Twitter to play with. As a message based social networking service it certainly falls into the Big Data space with more than 310 million active users and 6.000 tweets a second (from 5.000 tweets a day in 2007). The 3 V’s are ticked and fortunately Twitter gives us API access to the data for developers. You can search the tweets, retrieve information about user accounts and listen to the status stream (though you can tap into the public stream only which is supposedly 1% of all, the gardenhose and firehose is off limits, you only get expensive access to it through some data resellers). Still we can poke our nose into Big Data ‘lite’.

I did some experiments with the Twitter search and streaming API, also in the context of aviation and airport. I started to persist trends, search results and filtered live stream into MongoDB. I will share some of my findings soon.

Twitter Stream

Twitter Stream

 

D3.js Getting started (again..)

The same questions as some years back: How to get started with it ?

The learning curve for beginners is certainly a bit steep if you dont have a basic background in at least HTML, CSS and JavaScript, in this case you rather go for a visualization tool like Tableau if you look for fast results.
The good news: There is plenty of material out there about D3, books, websites, tutorials. I purchased a couple of books from Packt Books and O’Reilly as PDF version, I think they have the most books, though most of them based on earlier D3 versions.

Packt Books

Packt Books

O'Reilly Books

O’Reilly Books

I wont recommend a specific one, most follow the same principles, a short intro to the DOM, explain the D3 basics followed by more or less complex samples. As with any other technology or programming language you learn most hands-on, there so many examples and good tutorials available. I never attempted to understand everything, understand the concepts, dissect existing visualizations and get creative !

Continue reading