Visualising Mobile Network Data with Google Big Query and Google Maps

My talk at AfricaCom about using Google Big Query to mine OpenCellID data across 1.2 billion rows, and using Google Maps to visualise the data.

Transcript

1. Experts
   Developer
   

   View Slide

2. GDE
   Visualising Mobile Network Data
   with Google Big Query and Google Maps
   Dale Humby
   dale@nomanini.com
   Hiren Patel
   hir3npatel@gmail.com
   

   View Slide

3. Experts
   Developer
   developers.google.com/groups
   

   View Slide

4. GDE
   Mobile Networks is Big Data
   

   View Slide

5. GDE
   

   View Slide

6. GDE
   OpenCellID collecting data
   

   View Slide

7. GDE
   OpenCellID to Big Query
   

   View Slide

8. GDE
   

   View Slide

9. SQL
   GDE
   Number of Measurements
   SELECT
   COUNT(*)
   FROM (TABLE_QUERY(africacom, 'table_id CONTAINS "measurements"'))
   

   View Slide

10. SQL
    GDE
    Number of Base Stations
    SELECT
    COUNT(*)
    FROM
    (SELECT
    COUNT(*)
    FROM (TABLE_QUERY(africacom, 'table_id CONTAINS "measurements"'))
    GROUP BY
    mcc, net, area, cell)
    

    View Slide

11. SQL
    GDE
    Location of Subscribers
    SELECT
    ROUND(lat, 2) AS lat,
    ROUND(lon, 2) AS lon,
    COUNT(*) AS count
    FROM (TABLE_QUERY(africacom, 'table_id CONTAINS "measurements"'))
    WHERE
    lat > -34.60 AND lon > 17.80 AND
    lat < -33.00 AND lon < 20.00 AND
    HOUR(measured) = 14
    GROUP BY lat, lon
    ORDER BY count DESC
    LIMIT 8000;
    

    View Slide

12. GDE
    Location of Subscribers
    

    View Slide

13. GDE
    Subscribers on a Base Station
    

    View Slide

14. GDE
    Signal Strength reported by MNO
    Source: http://www.3g.co.za/index.php/information/coverage-maps/tabbed-coverage-map-of-south-african-networks
    

    View Slide

15. GDE
    Signal Strength reported by MNO
    Source: http://www.3g.co.za/index.php/information/coverage-maps/tabbed-coverage-map-of-south-african-networks
    

    View Slide

16. GDE
    Signal Strength on phone
    

    View Slide

17. GDE
    What could a MNO use it for:
    Stream CDR’s to Big Query
    Bulk loaded in Cloud Store
    KPI perf monitoring and business trends
    Engineering, planning and data exploration tool
    

    View Slide

18. GDE
    When to use Big Query
    Adhoc queries on large data
    Data analysis
    Streaming inserts keep data live
    Fast enough for dashboards
    

    View Slide

19. Experts
    Developer
    cloud.google.com
    $500 promo code: gde-in
    

    View Slide

20. GDE
    Thank You
    App:
    Data:
    Code:
    GDG:
    Dale Humby
    dale@nomanini.com
    plus.google.com/+DaleHumby
    Hiren Patel
    hir3npatel@gmail.com
    plus.google.com/+HirenPatel
    dale-humby.appspot.com
    bigquery.cloud.google.com/queries/dale-humby
    github.com/GDGCapeTown/bigquery-demo
    developers.google.com/groups
    

    View Slide