<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Theo Bodmer on Medium]]></title>
        <description><![CDATA[Stories by Theo Bodmer on Medium]]></description>
        <link>https://medium.com/@theo.bodmer?source=rss-f552663be151------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*N3FHt3rjGWqSHedR</url>
            <title>Stories by Theo Bodmer on Medium</title>
            <link>https://medium.com/@theo.bodmer?source=rss-f552663be151------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 22:53:45 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@theo.bodmer/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Hierarchical data in Firestore]]></title>
            <link>https://medium.com/google-cloud/hierarchical-data-in-firestore-b91581e44856?source=rss-f552663be151------2</link>
            <guid isPermaLink="false">https://medium.com/p/b91581e44856</guid>
            <category><![CDATA[firestore]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[database-design]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[firebase]]></category>
            <dc:creator><![CDATA[Theo Bodmer]]></dc:creator>
            <pubDate>Mon, 09 Nov 2026 06:42:17 GMT</pubDate>
            <atom:updated>2026-11-09T17:49:16.852Z</atom:updated>
            <content:encoded><![CDATA[<h4>Let every document carry its path</h4><p>In my <a href="https://medium.com/google-cloud/running-a-gps-tracking-platform-on-firebase-and-bigquery-6926cb295b75">last article</a> I described how <a href="https://track.trackportal.com/marketing/platform-offerings.html">trackportal.com</a>, our GPS fleet tracking platform, runs on Firebase and BigQuery.</p><p>This one is about <strong>WTracking</strong>, an add-on module to the GPS tracking. With WTracking a team records its work: <strong>who</strong> did <strong>which activity</strong>, <strong>where</strong>, and <strong>for how long</strong>. Some of Switzerland’s finest wines are now made with its help.</p><p>Both “which activity” and “where” are <strong>trees</strong>. This article covers only that: how we store hierarchical data in Firestore, and how we query it.</p><h3>The problem</h3><p>Activities can be nested to any depth, and so can geo regions. A region tree is a logical structure, not a map: a winery’s office can sit under the winery next to its vineyards.</p><p>The questions span levels: all work in a region <strong>including everything below it</strong>, or all time on an activity <strong>including its sub-activities</strong>.</p><p>Firestore is hierarchical by nature, so my first idea was to nest sub-regions as sub-collections. That proved <strong>impractical</strong>. A bit of research showed the better way: each document <strong>carries its place in the tree with it</strong>, plus a reference to its parent. With that, querying is straightforward, and everything the user interface needs is always at hand.</p><h3>The pattern: parentId plus the path, written once</h3><p>Every node in a tree gets two fields:</p><ul><li>parentId: the direct parent, or null at the top.</li><li>ancestorIds: all ancestors, <strong>root first</strong>, without the node itself.</li></ul><p>This is the well-known <strong>array of ancestors</strong> pattern (also called a <em>materialized path</em>), applied to Firestore, where array-contains makes it a natural fit.</p><p>Both are written <strong>once, when the node is created</strong>. The trick that keeps this simple is a rule in the app: <strong>a node never moves</strong>. You can rename it, redraw it or switch it off, but it keeps its parent forever. So the stored path can never go stale, and we never have to rewrite a subtree.</p><p>Two more rules keep the tree whole:</p><ul><li>A node that has children cannot be deleted, so nothing is ever orphaned.</li><li>A node that should no longer be used is switched off, not deleted, so old work entries still point to it.</li></ul><h3>The geo-region hierarchy</h3><p>A geo region is a polygon on the map. For WTracking customers, regions can have sub-regions to any depth. The examples below use a winery: an estate, its vineyards, and the parcels inside them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0SHPQ8sfdxPHgtRNGmJ9wQ.png" /><figcaption>Geo regions · the region tree next to the map, sub-regions indented under their parent</figcaption></figure><p>customers/{customerId}/geoRegions/wg_sonnhalde</p><pre>{<br>  &quot;name&quot;: &quot;Weingut Sonnhalde&quot;,<br>  &quot;points&quot;: [ &quot;…&quot; ],<br>  &quot;parentId&quot;: null,<br>  &quot;ancestorIds&quot;: []<br>}</pre><p>customers/{customerId}/geoRegions/pz_b12</p><pre>{<br>  &quot;name&quot;: &quot;Parzelle B12 Riesling&quot;,<br>  &quot;points&quot;: [ &quot;…&quot; ],<br>  &quot;parentId&quot;: &quot;rb_sued&quot;,<br>  &quot;ancestorIds&quot;: [&quot;wg_sonnhalde&quot;, &quot;rb_sued&quot;]<br>}</pre><p>The parcel lies in the vineyard <em>Rebberg Süd</em> (rb_sued), which lies in the estate. A child&#39;s ancestorIds is simply its parent&#39;s ancestorIds plus the parent&#39;s ID, so creating a node needs nothing but the parent document, which the app already has on screen.</p><h3>The activity tree</h3><p>Activities use exactly the same two fields: <em>Leaf thinning</em> sits under <em>Canopy work</em>, which sits under <em>Vineyard work</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/667/1*VMRSosaazoDEzYGGnusPnw.png" /><figcaption>Activities · the activity tree, sub-activities indented under their parent</figcaption></figure><h3>Pointing into the trees</h3><p>A work entry recorded on the phone points at one node of each tree. It doesn’t only store the node: it stores the <strong>whole path, root first, including the node itself</strong>. Work orders carry the same two paths.</p><p>customers/{customerId}/work/w_7Hq2</p><pre>{<br>  &quot;uid&quot;: &quot;uid_marco&quot;,<br>  &quot;start&quot;: &quot;2026-07-15T07:02:00Z&quot;,<br>  &quot;end&quot;: &quot;2026-07-15T10:41:00Z&quot;,<br>  &quot;geoRegionPath&quot;: [&quot;wg_sonnhalde&quot;, &quot;rb_sued&quot;, &quot;pz_b12&quot;],<br>  &quot;geoRegionName&quot;: &quot;Parzelle B12 Riesling&quot;,<br>  &quot;activityPath&quot;: [&quot;act_vineyard&quot;, &quot;act_canopy&quot;, &quot;act_leaf_thinning&quot;],<br>  &quot;activityName&quot;: &quot;Leaf thinning&quot;<br>}</pre><p>With the node in its own path, <em>“in this region or below it”</em> becomes a single membership test, whatever level the region is on. The names are copied too, so a report printed next year still reads correctly, even if the parcel has been renamed or deleted since.</p><p>On the phone, the worker picks the region and the activity from the same trees:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/586/1*frEHrB2Bq4XhGkxm0FArcA.png" /><figcaption>Region picker · the path down to a parcel</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/549/1*MhbUnS41n-RL5BoE2Pzk1A.png" /><figcaption>Activity picker · the same tree, expanded down to the activity</figcaption></figure><h3>Querying the trees</h3><p>The examples use the Firebase JavaScript SDK (v9, modular); regions, workOrders and work are the collections under the customer&#39;s document.</p><h4>Load a whole tree once</h4><p>A customer has tens or hundreds of regions, not millions, so the app reads the whole collection once and builds the tree in memory. That covers the region list, the pickers and every breadcrumb.</p><pre>const snap = await getDocs(regions);<br>const byId = new Map(snap.docs.map((d) =&gt; [d.id, { id: d.id, ...d.data() }]));<br> <br>function pathOf(id) {   // root first: estate, vineyard, parcel<br>  const path = [];<br>  for (let n = byId.get(id); n; n = byId.get(n.parentId)) path.unshift(n);<br>  return path;<br>}<br> <br>const label = pathOf(&quot;pz_b12&quot;).map((n) =&gt; n.name).join(&quot; › &quot;);<br>// &quot;Weingut Sonnhalde › Rebberg Süd › Parzelle B12 Riesling&quot;</pre><h4>A subtree in one query</h4><p>Every node below the estate, at any depth, has the estate’s ID in its ancestorIds. And every order on the vineyard or on any parcel in it has the vineyard&#39;s ID in its path:</p><pre>const belowEstate = await getDocs(<br>  query(regions, where(&quot;ancestorIds&quot;, &quot;array-contains&quot;, &quot;wg_sonnhalde&quot;)));<br> <br>const openOrders = await getDocs(query(workOrders,<br>  where(&quot;geoRegionPath&quot;, &quot;array-contains&quot;, &quot;rb_sued&quot;),<br>  where(&quot;status&quot;, &quot;==&quot;, &quot;open&quot;)));</pre><h4>The work report: a region and a period</h4><p>The monthly work report asks for one region and everything below it, in a period:</p><pre>const snap = await getDocs(query(work,<br>  where(&quot;geoRegionPath&quot;, &quot;array-contains&quot;, &quot;rb_sued&quot;),<br>  where(&quot;start&quot;, &quot;&gt;=&quot;, Timestamp.fromDate(new Date(2026, 6, 1))),<br>  where(&quot;start&quot;, &quot;&lt;&quot;, Timestamp.fromDate(new Date(2026, 7, 1))),<br>  orderBy(&quot;start&quot;)));<br> <br>const entries = snap.docs.map((d) =&gt; d.data()).filter((w) =&gt; w.end);</pre><p>Combining array-contains with a range on start needs a <strong>composite index</strong> on geoRegionPath (array-contains) and start (ascending). Without it Firestore rejects the query, and its error message contains a link that creates the index in the console.</p><p>Firestore allows only <strong>one </strong><strong>array-contains per query</strong>, so an activity filter (<em>Canopy work</em> and everything below it) runs on the client: entries.filter((w) =&gt; w.activityPath.includes(&quot;act_canopy&quot;)).</p><h4>Subtotals for every level in one pass</h4><p>Each entry adds its time to <strong>every node on its path</strong>, so the estate, the vineyard and the parcel all get their totals in a single loop, without walking the tree:</p><pre>const total = new Map();   // region id → minutes, subtree included<br>for (const w of entries) {<br>  const minutes = (w.end.toMillis() - w.start.toMillis()) / 60000;<br>  for (const id of w.geoRegionPath) {<br>    total.set(id, (total.get(id) ?? 0) + minutes);<br>  }<br>}</pre><h3>The trade-offs</h3><ul><li><strong>Nodes can’t move.</strong> That is the price of a path that is written once. Moving a parcel to another vineyard would mean rewriting its subtree and every work entry that points into it. Our customers draw a new region instead; the old one keeps its history.</li><li><strong>Depth is no problem.</strong> A path is one ID per level, and real trees are three to five levels deep, far from any Firestore limit on array or document size.</li><li><strong>Reads stay cheap.</strong> A tree is one collection read, a subtree is one query, and a report is one indexed query.</li></ul><p>If you’re building something similar, I’m happy to compare notes: <a href="/service/mailto:theo.bodmer@insafety.ch">theo.bodmer@insafety.ch</a></p><p><em>Theo Bodmer is CTO of Insafety GmbH in Zürich, Switzerland, where he built </em><a href="https://track.trackportal.com/marketing/platform-offerings.html"><em>trackportal.com</em></a><em> on Firebase and BigQuery.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b91581e44856" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/hierarchical-data-in-firestore-b91581e44856">Hierarchical data in Firestore</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Listen to €20 GPS trackers in Go]]></title>
            <link>https://medium.com/@theo.bodmer/talking-to-20-gps-trackers-in-go-1ed6b7289f76?source=rss-f552663be151------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ed6b7289f76</guid>
            <category><![CDATA[golang]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[fleet-management]]></category>
            <category><![CDATA[gps-tracking-system]]></category>
            <category><![CDATA[iot]]></category>
            <dc:creator><![CDATA[Theo Bodmer]]></dc:creator>
            <pubDate>Sat, 17 Oct 2026 12:50:50 GMT</pubDate>
            <atom:updated>2026-11-09T17:51:23.542Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SrArhljsZmfSJCLutqxGgw.gif" /></figure><p>Many GPS trackers between €15 and €40 speak one of two protocols: <strong>GT06</strong> (binary, from Concox) or <strong>H02</strong> (plain text, *HQ,...#). The protocol documents are scattered PDFs, often for a slightly different firmware. This post is what we learned running both in production for <a href="https://track.trackportal.com/marketing/platform-offerings.html?utm_source=devto">trackportal.com</a>. The full listener is standard-library Go, with a simulator that replays real tracker sessions:</p><p>👉 <a href="https://github.com/Insafety/gt06-h02-go"><strong>github.com/Insafety/gt06-h02-go</strong></a></p><p>Want to try your own trackers without writing any code? <a href="/service/mailto:welcome@insafety.ch?subject=trackportal%20demo%20account%20request">Get in touch</a> and we’ll open a trackportal test account for you.</p><ul><li><strong>Encoding:</strong> GT06 is binary (78 78 … 0d 0a), H02 is plain text (*HQ,…#).</li><li><strong>Device ID:</strong> GT06 sends it only in the login packet, H02 in every message.</li><li><strong>Server reply:</strong> GT06 needs an ack with serial and CRC, H02 needs none.</li><li><strong>Speed:</strong> GT06 in km/h, H02 in knots.</li></ul><h3>The listener</h3><p>Both run over plain TCP, one port each (5023 and 5024). One goroutine per connection, and <strong>one decoder per connection</strong>, which matters for GT06:</p><pre>func listen(addr string, newDecoder func(net.Conn) func([]byte), split bufio.SplitFunc) {<br>    l, err := net.Listen(&quot;tcp&quot;, addr)<br>    if err != nil {<br>        log.Fatal(err)<br>    }<br>    for {<br>        c, err := l.Accept()<br>        if err != nil {<br>            continue<br>        }<br>        go func() {<br>            defer c.Close()<br>            decode := newDecoder(c) // fresh session state per connection<br>            s := bufio.NewScanner(c)<br>            s.Split(split) // whole frames only<br>            for s.Scan() {<br>                decode(s.Bytes())<br>            }<br>        }()<br>    }<br>}</pre><p>TCP is a stream, not messages: one read can hold two frames, or half of one. The split function cuts whole frames out first, on # for H02 and on the length byte for GT06.</p><h3>GT06: binary, and the connection is the identity</h3><p>A GT06 frame is 78 78, a length byte, the protocol number, the content, a 2-byte serial, a CRC and 0d 0a. The device connects and sends a 0x01 login carrying its IMEI, then <strong>waits for your ack</strong>. Every packet after that <strong>arrives without the IMEI</strong>: the TCP connection is the only identity. So the decoder is a closure that holds the session:</p><pre>func gt06(c net.Conn) func([]byte) {<br>    imei := &quot;&quot; // set by the login, used by every packet after it<br>    return func(m []byte) {<br>        switch m[3] {<br>        case 0x01: // login: IMEI as 8 BCD bytes, first nibble is padding<br>            imei = hex.EncodeToString(m[4:12])[1:]<br>        case 0x12: // position<br>            lat := float64(binary.BigEndian.Uint32(m[11:15])) / 1800000 // minutes × 30000<br>            lon := float64(binary.BigEndian.Uint32(m[15:19])) / 1800000<br>            // Course/status word: bit 10 north, bit 11 west, bit 12 GPS fixed.<br>            cs := binary.BigEndian.Uint16(m[20:22])<br>            if cs&amp;0x0400 == 0 {<br>                lat = -lat<br>            }<br>            if cs&amp;0x0800 != 0 {<br>                lon = -lon<br>            }<br>            fix := cs&amp;0x1000 != 0<br>            log.Printf(&quot;%s %.6f,%.6f %d km/h fix=%t&quot;, imei, lat, lon, m[19], fix)<br>        }<br>        // Ack every packet: its protocol number, its own serial, a new CRC.<br>        s := int(m[2]) - 1 // the serial sits right before the CRC<br>        r := []byte{0x78, 0x78, 0x05, m[3], m[s], m[s+1], 0, 0, 0x0d, 0x0a}<br>        binary.BigEndian.PutUint16(r[6:8], crc16X25(r[2:6]))<br>        c.Write(r)<br>    }<br>}</pre><h3>H02: text, stateless</h3><p>A message looks like *HQ,1234567890,V1,083000,A,4722.6140,N,00832.5020,E,21.60,135,141026,fffbffff#: device ID, type, time, fix flag, latitude, longitude, speed, course, date and a status word. Every message carries the device ID, so there is no session and nothing to ack:</p><pre>func h02(net.Conn) func([]byte) {<br>    return func(m []byte) { // no state: every message names its device<br>        f := strings.Split(strings.TrimRight(string(m), &quot;#\r\n&quot;), &quot;,&quot;)<br>        if len(f) &lt; 13 || f[0] != &quot;*HQ&quot; {<br>            return<br>        }<br>        t, _ := time.Parse(&quot;020106150405&quot;, f[11]+f[3]) // ddmmyy + hhmmss, UTC<br>        lat, lon := dm(f[5], 2), dm(f[7], 3)           // NMEA ddmm.mmmm, dddmm.mmmm<br>        if f[6] == &quot;S&quot; {<br>            lat = -lat<br>        }<br>        if f[8] == &quot;W&quot; {<br>            lon = -lon<br>        }<br>        knots, _ := strconv.ParseFloat(f[9], 64)<br>        st, _ := strconv.ParseUint(f[12], 16, 32) // active-low: 0 = true<br>        sos, ign := st&amp;(1&lt;&lt;1) == 0, st&amp;(1&lt;&lt;18) == 0<br>        log.Printf(&quot;%s %s %.6f,%.6f %.0f km/h fix=%t ign=%t sos=%t&quot;,<br>            f[1], t, lat, lon, knots*1.852, f[4] == &quot;A&quot;, ign, sos)<br>    }<br>}</pre><p>Both helpers, crc16X25 and dm, are in the repo&#39;s <a href="https://github.com/Insafety/gt06-h02-go/blob/main/parser/main.go">parser/main.go</a>.</p><h3>Testing without a car</h3><p>The repo’s simulator replays 15 recorded sessions from five real trackers against your listener:</p><pre>go run ./parser                                  # terminal 1<br>go run ./tracker_simulator                       # terminal 2<br>go run ./tracker_simulator -delay 200ms -split   # every frame in two halves</pre><p>-split and -merge reproduce what mobile networks do to TCP, so you can check your framing holds up.</p><p>Found a device that behaves differently, or a trap we missed? <a href="https://github.com/Insafety/gt06-h02-go/issues">Open an issue</a>, we’d like to hear about it.</p><p><em>Theo Bodmer is CTO of Insafety GmbH in Zürich, which builds </em><a href="https://track.trackportal.com/marketing/platform-offerings.html?utm_source=devto"><em>trackportal</em></a><em>: fleet tracking for GT06, H02 and other trackers, with the option to run the backend in your own Google Cloud project.</em></p><p><em>Originally published at </em><a href="https://dev.to/theo_bodmer/talking-to-eu20-gps-trackers-in-go-416p"><em>https://dev.to</em></a><em> on October 17, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ed6b7289f76" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Running a GPS tracking platform on Firebase and BigQuery]]></title>
            <link>https://medium.com/google-cloud/running-a-gps-tracking-platform-on-firebase-and-bigquery-6926cb295b75?source=rss-f552663be151------2</link>
            <guid isPermaLink="false">https://medium.com/p/6926cb295b75</guid>
            <category><![CDATA[firestore]]></category>
            <category><![CDATA[gps-tracking-system]]></category>
            <category><![CDATA[firebase]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud]]></category>
            <dc:creator><![CDATA[Theo Bodmer]]></dc:creator>
            <pubDate>Thu, 15 Oct 2026 10:51:51 GMT</pubDate>
            <atom:updated>2026-10-15T10:51:51.816Z</atom:updated>
            <content:encoded><![CDATA[<p>This is the story of trackportal.com, our GPS fleet tracking platform.</p><p>The new system costs <strong>substantially less</strong> to run and is <strong>a lot more resilient</strong>. We can now offer our customers <strong>total data sovereignty</strong> by using their own Google accounts.</p><h3>Our story</h3><p><strong>2011:</strong> We built a vehicle GPS tracking system using a central relational database and server-side-generated web pages.</p><p><em>It was great at the time.</em></p><p><strong>2015:</strong> We moved to a more mobile-friendly client-side rendered UI.</p><p><em>It was great at the time.</em></p><p><strong>Eventually,</strong> maintaining the old system no longer felt ideal. Everything still worked fine, but:</p><ul><li>We had to rebuild the production server about once a year.</li><li>Every month our frameworks fell a little further behind.</li></ul><p>Meanwhile, on other projects, we had good experiences building mobile apps using Google Firebase and its Firestore document database. Its fabulous client SDKs make data synchronization a breeze.</p><p>Why not try it for our GPS fleet tracking?</p><ul><li><strong>Will it scale?</strong></li><li><strong>Will it be economical?</strong></li></ul><h3>The data structure</h3><p>After some trial and error we settled on these design decisions.</p><p>We had to bite the bullet and write two documents per GPS position, and Firestore charges for every write.</p><ul><li>The first updates the device with the latest position.</li><li>The second goes into the <strong>position bucket</strong>, a subcollection of the device document. There we have one document per device per hour, with the positions stored in an array.</li></ul><p>This addresses several problems. Reading one device’s positions for an entire day results in a maximum of 24 document reads. Retrieving positions for days and hours is efficient, fast, and economical.</p><p>It also works well with Firestore’s maximum document size of one megabyte.</p><h3>The device document</h3><p>customers/{customerId}/devices/{deviceId}</p><pre>{<br>  &quot;imei&quot;: &quot;860000000000001&quot;,<br>  &quot;name&quot;: &quot;Delivery van 3&quot;,<br>  &quot;active&quot;: true,<br>  &quot;lastSeen&quot;: &quot;2026-07-27T17:00:25Z&quot;,<br>  &quot;lastPosition&quot;: {<br>    &quot;lat&quot;: 47.37840,<br>    &quot;long&quot;: 8.54010,<br>    &quot;kmh&quot;: 49,<br>    &quot;dir&quot;: 12,<br>    &quot;at&quot;: &quot;2026-07-27T17:00:24Z&quot;,<br>    &quot;ts&quot;: &quot;2026-07-27T17:00:25Z&quot;,<br>    &quot;dist&quot;: 136<br>  },<br>  &quot;lastAlerts&quot;: {<br>    &quot;speedLimit80&quot;: &quot;2026-07-27T16:12:40Z&quot;,<br>    &quot;leftZurichCity&quot;: &quot;2026-07-27T08:05:13Z&quot;,<br>    &quot;idleOver60min&quot;: &quot;2026-07-26T12:47:02Z&quot;<br>  }<br>}</pre><p>The lastAlerts map records when each alert was last sent; with this information, we can prevent alert storms by restricting how often they can be generated.</p><h3>A position bucket document</h3><p>customers/{customerId}/devices/{deviceId}/positions/2026-07-27T17</p><pre>{<br>  &quot;imei&quot;: &quot;860000000000001&quot;,<br>  &quot;hourStart&quot;: &quot;2026-07-27T17:00:00Z&quot;,<br>  &quot;distM&quot;: 415,<br>  &quot;pos&quot;: [<br>    {<br>      &quot;lat&quot;: 47.37591, &quot;long&quot;: 8.53950, &quot;kmh&quot;: 51, &quot;dir&quot;: 8,<br>      &quot;at&quot;: &quot;2026-07-27T17:00:04Z&quot;, &quot;ts&quot;: &quot;2026-07-27T17:00:05Z&quot;, &quot;dist&quot;: 138<br>    },<br>    {<br>      &quot;lat&quot;: 47.37718, &quot;long&quot;: 8.53975, &quot;kmh&quot;: 50, &quot;dir&quot;: 10,<br>      &quot;at&quot;: &quot;2026-07-27T17:00:14Z&quot;, &quot;ts&quot;: &quot;2026-07-27T17:00:15Z&quot;, &quot;dist&quot;: 141<br>    },<br>    {<br>      &quot;lat&quot;: 47.37840, &quot;long&quot;: 8.54010, &quot;kmh&quot;: 49, &quot;dir&quot;: 12,<br>      &quot;at&quot;: &quot;2026-07-27T17:00:24Z&quot;, &quot;ts&quot;: &quot;2026-07-27T17:00:25Z&quot;, &quot;dist&quot;: 136<br>    }<br>  ]<br>}</pre><p>Firestore indexes fields by default; since we do not run any queries against the position array, we do not need these fields indexed, and thereby reduce the space needed of our position buckets by approximately two-thirds!</p><pre>gcloud firestore indexes fields update pos \<br>  --collection-group=positions --disable-indexes --project=YOUR_PROJECT_ID</pre><p>These simple data structures have addressed our real-time tracking. The front end simply listens to the device documents, so the map updates live; it is snappy and convenient.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ui9Pn5woSHqOuBGunj8Yxw.png" /><figcaption>Front end · the live map listens to the device documents</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/976/1*3Bnsx0BXK2MAYPoKWPDo8Q.png" /><figcaption>Back end · the hour buckets in the Firestore console</figcaption></figure><h3>Analytics and Reporting</h3><p>What has not been addressed so far is reporting and <a href="https://docs.cloud.google.com/bigquery/docs/geospatial-intro"><strong>analytics</strong></a>, but we found a great solution for this as well: Google <a href="https://cloud.google.com/bigquery"><strong>BigQuery</strong></a> and Google <a href="https://cloud.google.com/data-studio"><strong>Data Studio</strong></a>.</p><p>Linking BigQuery with Firebase gives us a spatial-data capable data warehouse.</p><h3>The BigQuery denormalized positions table</h3><pre>+-------------------------+----------------------+-------------------+----------------+----------+---------+-----+-----+--------+---------------+<br>| position_time           | customer_name        | device_group_name | device_name    | lat      | long    | kmh | dir | dist_m | tz            |<br>+-------------------------+----------------------+-------------------+----------------+----------+---------+-----+-----+--------+---------------+<br>| 2026-07-27 17:00:04 UTC | Example Logistics AG | Zürich vans       | Delivery van 3 | 47.37591 | 8.53950 | 51  | 8   | 138    | Europe/Zurich |<br>| 2026-07-27 17:00:14 UTC | Example Logistics AG | Zürich vans       | Delivery van 3 | 47.37718 | 8.53975 | 50  | 10  | 141    | Europe/Zurich |<br>| 2026-07-27 17:00:24 UTC | Example Logistics AG | Zürich vans       | Delivery van 3 | 47.37840 | 8.54010 | 49  | 12  | 136    | Europe/Zurich |<br>+-------------------------+----------------------+-------------------+----------------+----------+---------+-----+-----+--------+---------------+</pre><p>Each row carries the customer, group and device names and the time zone, so reports need no joins.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/853/1*MQDJoMm8DJj-h0h7mvgImA.png" /><figcaption>Fleet report · distance, moving, idle and utilization per device, straight from BigQuery</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/873/1*O59KiMo-h5uYT-3cbihyjw.png" /><figcaption>Heat maps · where vehicles spend their time and where alerts fire</figcaption></figure><p>These Data Studio reports are embedded in our solution, but our customers can be set up to make their own using the Data Studio web-based designer.</p><h3>So, was it economical?</h3><p>At current Google Cloud list prices, the whole stack — live tracking in Firestore and analytics in BigQuery — works out to roughly <strong>40 cents per vehicle per month</strong>, for a vehicle reporting every 30 seconds for ten hours a day.</p><h3>Estimated monthly cost by fleet size</h3><pre>+---------+-----------------+------------------+-------------------+----------+-----------+------------+<br>| Devices | Firestore reads | Firestore writes | Firestore storage | BigQuery | Total     | Per device |<br>+---------+-----------------+------------------+-------------------+----------+-----------+------------+<br>|      10 |           $1.26 |            $0.35 |             $0.23 |    $0.04 |     $1.87 |      $0.19 |<br>|      50 |           $9.90 |            $6.05 |             $1.85 |    $0.19 |    $17.98 |      $0.36 |<br>|     100 |          $20.70 |           $13.18 |             $3.88 |    $0.36 |    $38.11 |      $0.38 |<br>|     400 |          $85.50 |           $55.94 |            $16.04 |    $1.50 |   $158.99 |      $0.40 |<br>|   1,000 |         $215.10 |          $141.48 |            $40.38 |    $4.10 |   $401.06 |      $0.40 |<br>|   5,000 |       $1,079.10 |          $711.72 |           $202.60 |   $30.43 | $2,023.85 |      $0.40 |<br>|  10,000 |       $2,159.10 |        $1,424.52 |           $405.37 |   $67.34 | $4,056.33 |      $0.41 |<br>+---------+-----------------+------------------+-------------------+----------+-----------+------------+</pre><p>If you’re building something similar, I’m happy to compare notes: <a href="/service/mailto:theo.bodmer@insafety.ch">theo.bodmer@insafety.ch</a></p><p><em>Theo Bodmer is CTO of Insafety GmbH in Zürich, Switzerland, where he built </em><a href="https://track.trackportal.com/marketing/platform-offerings.html?utm_source=medium"><em>trackportal.com</em></a><em> on Firebase and BigQuery.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6926cb295b75" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/running-a-gps-tracking-platform-on-firebase-and-bigquery-6926cb295b75">Running a GPS tracking platform on Firebase and BigQuery</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>