iPhone consolidated.db location tracking notes

I’m trying to understand the consolidated.db iPhone location database, which means understanding how iOS stores and managing data. Here’s some notes.


  1. @aallan’s original announcement
  2. iPhone Tracker (Objective C code)
  3. Technical instructions from howto.wired
  4. nphonetrack (.NET code)
  5. iPhoneLs.py
  6. iphone-tracker.py
  7. Quick start for sqlite3
  • When you make a backup of an iPhone, it creates a big directory full of files with random 160 bit filenames. These are named files in the iPhone that iphonels.py can display. My 3 biggest files are named
  • consolidated.db is the file with the location data in it. It’s a sqlite3 database.
  • The juicy tables are (apparently) CellLocation and WifiLocation. The *Counts tables seem to contain just a row count. Presumably the Boxes tables are some sort of spatial index. I can find no record of how often I was at a specific location other than what’s implicit in the timestamps and density of cell/wifi locations.
  • I have 32,013 rows in CellLocation, 171,040 rows in WifiLocation
  • Timestamps are like 309803342: I believe this is NSDate seconds since 1 Jan 2001. Add 978307200 to get it in seconds since Unix 1970 epoch.
  • HorizontalAccuracy varies from 50-300 for WifiLocation, and up to 80,000 for CellLocation.
  • Confidence is 0, 50, 60, 65, 68, or 70
  • Speed and Course are always -1 (no surprise).
Some quicky sqlite3 shell code
.mode csv
.output wifilocation.csv
select Timestamp, Latitude, Longitude from WifiLocation order by Timestamp;
.output CellLocation.csv
select Timestamp, Latitude, Longitude from CellLocation order by Timestamp;
Some pasted info from sqlite3

sqlite> .tables
CdmaCellLocation                   CellLocationCounts
CdmaCellLocationBoxes              CellLocationHarvest
CdmaCellLocationBoxes_node         CellLocationHarvestCounts
CdmaCellLocationBoxes_parent       CellLocationLocal
CdmaCellLocationBoxes_rowid        CellLocationLocalBoxes
CdmaCellLocationCounts             CellLocationLocalBoxes_node
CdmaCellLocationHarvest            CellLocationLocalBoxes_parent
CdmaCellLocationHarvestCounts      CellLocationLocalBoxes_rowid
CdmaCellLocationLocal              CellLocationLocalCounts
CdmaCellLocationLocalBoxes         CompassCalibration
CdmaCellLocationLocalBoxes_node    Fences
CdmaCellLocationLocalBoxes_parent  Location
CdmaCellLocationLocalBoxes_rowid   LocationHarvest
CdmaCellLocationLocalCounts        LocationHarvestCounts
Cell                               TableInfo
CellLocation                       Wifi
CellLocationBoxes                  WifiLocation
CellLocationBoxes_node             WifiLocationCounts
CellLocationBoxes_parent           WifiLocationHarvest
CellLocationBoxes_rowid            WifiLocationHarvestCounts

CREATE TABLE CellLocation (
  Timestamp FLOAT,
  Latitude FLOAT,
  Longitude FLOAT,
  HorizontalAccuracy FLOAT,
  Altitude FLOAT,
  VerticalAccuracy FLOAT,
  Speed FLOAT,
  Course FLOAT,
  Confidence INTEGER,

CREATE TABLE WifiLocation (
  Timestamp FLOAT,
  Latitude FLOAT,
  Longitude FLOAT,
  HorizontalAccuracy FLOAT,
  Altitude FLOAT,
  VerticalAccuracy FLOAT,
  Speed FLOAT,
  Course FLOAT,
  Confidence INTEGER,

subverting iframe same-origin policy

I want to embed a part of a web page from a third party on to my web page. Easy, right, that’s what iframes are for! Only I don’t want to embed the whole foreign page, just a 200×200 pixel chunk in the middle of the page. You can crop 200×200 with the width and height parameters, but there’s no clean way to scroll to the middle of the embedded page. The simple Javascript scrollTo fails because the third party page in the iFrame does not have the same origin as the main page.

There’s a work around: doubling up on iframes. Your main page embeds an iframe to a shim page on your own server. The shim page in turn just embeds an iframe of the foreign page. The master page can scroll the shim page since they’re in the same origin. Ugly, but it works.

aprs.fi as a source of GPS tracks

I have some friends who track their positions via ham radio on APRS. Cool stuff. The site http//aprs.fi/ has a really nice map viewer of the data. I’m looking at extracting that data for my flight tracker.

The easy way is to have the user use the data export tool to download a file. It’s a bit awkward and manual but easy to understand. There are multiple format options, CSV seems sufficient. Fields are:

  • time: full text UTC, “when the target first reported this (current) position”
  • lasttime: identical to time in my data. “the time when the target last reported this (current) position”
  • lat: decimal degrees
  • lng: decimal degrees
  • speed: kilometers / hour
  • course: degrees, probably true?
  • altitude: meters
  • comment: empty in my examples. “APRS comment”

aprs.fi also has a simple REST API. Unfortunately it only seems to report the last fix, not a history of the track. A fetch of the last location works like http://api.aprs.fi/api/get?name=CALLSIGN&what=loc&format-JSON&apikey=KEY.

The granularity of track data is up to the APRS sender. I think one fix every 5 seconds is considered fast. My friend’s tracker is smart enough to send more rapid updates when turning, etc. 66 points for a 90 minute flight.

There are other sources of this data. aprs.fi notes you can also get the data directly from APRS-IS via a library like Ham-APRS-FAP. There’s also aprsworld.com; Adam Fast has some nice API code that relies on his having database access to aprsworld.

Ubuntu 11.04 notes

Time for my yearly experiment running Linux as a Desktop OS. I installed Ubuntu 11.04 beta1 (“Natty Narwhal”). And immediately hit graphics corruption and system crashes. To be fair I have a weird graphics card that Windows hates too, a Radeon HD 2400 PRO AGP.

So first order of business was switching from the ATI binary driver to the open source VESA driver. There are no docs on this I could find anywhere; there’s no longer an xorg.conf to edit. I finally just used apt-get to uninstall xserver-xorg-video-ati; happily, Ubuntu is smart enough to drop back to the VESA driver if the ATI driver is not around.

The second problem is Unity, the fancy new GUI, doesn’t run on the VESA driver. Too much GL or something. The old UI is there but if you want Unity the solution for that is to install unity-2d, a simpler UI.

With that all done, things sorta work. As with every time I try to use Linux as a desktop, it was a disaster. I can’t really blame Ubuntu for not handling my weird graphics card well, but I can blame it for there being no clear path to diagnose and fix the mistake. Also I’d say unity-2d ought to be a default option, not some weird thing to install.


D3 scales and interpolation

D3 has a notion of “scales”, transformations of data from a domain to a range. Say your data is percentages (0% to 100%) and you want to draw them as bars of length 10-20. You can easily construct a linear scale to map your domain [0,100] to a range [10,20]:

var s = d3.scale.linear.domain([0,100]).range([10,20])
    .attr("height", s)

If that use of s seems magic, equivalents would be

    .attr("height", function(n) { return s(n); })

    .attr("height", function(n) { return 10 + 10 * (n/100) });

D3 provides various useful scales. Numeric scales like linear, log, and pow, also discrete scales like quantize and ordinal.

One thing you can set on a scale is the interpolate function. It’s invoked when mapping the domain to the range and lets you make the range be something other than the usual numeric range.

s = d3.scale.linear()
  .range(["#ff0000", "#0000ff"])
s(0) == "rgb(255,0,0)"
s(100) == "rgb(0,0,255)"
s(50) == "rgb(128,0,128)"

There are pre-defined interpolation functions for numbers, colours, strings, objects, etc. I believe they’re also used by D3’s transition animations, specifically to automatically warp complex things like SVG paths from one shape to another.

The application of interpolation in d3.scale.linear.interpolate is pretty simple to follow:

  interpolate = d3.interpolate,
  i = interpolate(y0, y1);

  function scale(x) {
    return i((x - x0) * kx);

In the linear.js code [x0,x1] is the domain, kx is 1/(x1-x0). [y0,y1] is the range. So the above code takes the input x, maps it to the interval [0,1], then applies cached interpolation function i(). By default that function is a linear interpolation in [y0,y1], but the developer can override it.

Building node without SSL

I’m trying to build node.js from source on Debian/wheezy. There’s an RPM but it’s two major versions behind. I got an error about SSL methods not being declared when compiling. No idea why Debian’s dev environment makes node’s configure think it can build SSL when it can’t (I have libssl-dev installed), but a quick workaround is buidling Node without crypto, via ./configure --without-ssl.

[74/75] cxx: src/node_crypto.cc -> build/default/src/node_crypto_4.o
/usr/bin/g++ -pthread -m32 -g -O3 -DHAVE_OPENSSL=1 -DHAVE_MONOTONIC_CLOCK=1 -DEV_FORK_ENABLE=0 -DEV_EMBED_ENABLE=0 -DEV_MULTIPLICITY=0 -DX_STACKSIZE=65536 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -DEV_MULTIPLICITY=0 -DHAVE_FDATASYNC=1 -DPLATFORM="linux" -D__POSIX__=1 -Wno-unused-parameter -D_FORTIFY_SOURCE=2 -DNDEBUG -Idefault/src -I../src -Idefault/deps/libeio -I../deps/libeio -Idefault/deps/http_parser -I../deps/http_parser -Idefault/deps/v8/include -I../deps/v8/include -Idefault/deps/libev -I../deps/libev -Idefault/deps/c-ares -I../deps/c-ares -Idefault/deps/c-ares/linux-ia32 -I../deps/c-ares/linux-ia32 -Ideps/v8/include ../src/node_crypto.cc -c -o default/src/node_crypto_4.o
../src/node_crypto.cc: In static member function ‘static v8::Handle<v8::Value> node::crypto::SecureContext::Init(const v8::Arguments&)’:
../src/node_crypto.cc:101:29: error: ‘SSLv2_method’ was not declared in this scope
../src/node_crypto.cc:103:36: error: ‘SSLv2_server_method’ was not declared in this scope
../src/node_crypto.cc:105:36: error: ‘SSLv2_client_method’ was not declared in this scope
Waf: Leaving directory `/usr/local/src/node/build'
Build failed:  -> task failed (err #1):
        {task: cxx node_crypto.cc -> node_crypto_4.o}
make: *** [program] Error 1

mobile safari and viewport

Apple’s mobile browser is great, but the viewport meta tag is kind of a hash. There seems to be two conflicting ways to hint to the iPhone or iPad how big to draw things.

  1. width and height. Tells the browser this content is going to render best at a certain width, so render it to that size. width=device-width or width=320 is one way to tell the iPhone not to scale at all. width=600 or the like often solves the problem of stuff looking too small on the iPhone, while still letting it scale. If you set a small width the iPad will scale content up, making it bigger.
  2. initial-scale, minimum-scale, maximum-scale. Gives an explicit scaling factor. The user can override this (unless you disable it).

What’s confusing is if you set both a width and scaling behaviour: which takes precedence?

Mike suggested what seems to work well for maps is setting initial-scale to 1.0 and maximum-scale to 1.0. This keeps Safari from doing its own scaling on the page at all. Even better, when you rotate the device it doesn’t try to scale the width to match the old view; instead it just loads some stuff that was previously off-screen. The only drawback is this effectively sets the width to 320px on an iPhone, you no longer get any in-browser scaling.

What I really want is no scaling at all on the iPad, and 1.5 scaling on the iPhone. I can’t figure out a way to do that without changing the tags depending on what device loaded the page.