A new cover, title, and co-author; and lots of new content

Professional Android 4th Edition is now available, and will start shipping today (September 25th) from Amazon!

You can order Professional Android 4E in paperback from Amazon or The Book Repository, or your local bookseller. Or for your electronic reading pleasure, Kindle US (and UK) or Google Play Books.

This edition was written using Android Studio 3.1 and targeting API Level 27 (with some coverage of API 28 changes). It introduces Kotlin, but the code samples are written using Java syntax (we plan to make them available in Kotlin on Github as well — stay tuned!).

As always, it covers both the fundamentals of Android development, and explores advanced features and best practices for more experienced Android developers. That includes Android Architecture Components (including Job Scheduler, Live Data, and Room), material design principles and practice, Google Play services including maps, location, and awareness, and introduces the Firebase APIs.

All the code snippets and projects from the book are available at Wrox.com and our GitHub repo (please raise an issue for any bugs!), and you can always get in touch with @retomeier and @ianhlake on Twitter.

It’s been 6 years since the last revision, 5 years since I agreed to write a new edition (in time for Android 5), and nearly four years since people started pre-ordering their copies on Amazon.

Chris #io18 on Twitter

Hey @retomeier I saw that your latest Professional Android Dev book will ship Sept 27th, 2018. Should see how old the oldest pre-orders are, and/or sign those copies :D I pre-ordered mine Jan 27, 2015!!

It’s also been 10 years since the first copy of Professional Android Application Development rolled off the presses, and — because numbers are fun — it also mainly* follows the existing pattern of:

Target Android OS Version = 2^(book edition-1)

(*Some stuff from Android 9.0 may have snuck in towards the end there.)

10 years is a long time; Android has come a long way since I guest blogged about building my first Android app using the 0.8 beta SDK on Google Blogoscoped and Chris Webb from @Wrox emailed me saying he,

“Would be interested in speaking with you about your thoughts on the [Android] SDK. We are considering it for a book.”

While I wrote that first book, the first Android handset hadn’t been released, and the Google Play Store (or the Android Market as it was originally named) didn’t have its first app. Today, Android runs on some 2 billion monthly active devices, and in 2017 the Google Play store delivered over 94 billion app downloads.

In between, there’s been 28 API releases, 14 Android dessert flavors, a new IDE, a new 1st-class programming language, and scores of new platform APIs — not to mention the introduction of material design, the Android Support Library, Google Play services, Android Architecture Components, and Jetpack.

As a result, Professional Android has grown too. The 4th edition is more than double the size of the first edition, with 500 additional pages. We’re up to our 3rd cover, and we’ve added a co-author — the amazing Ian Lake (Ed: Hi Ian!)

What’s new?

The Fourth Edition is fully revised and expanded. It includes detailed coverage of the fundamentals needed by novice developers, and explores advanced features and best practices essential to experienced Android developers creating compelling user experiences, it:

• Covers the latest Android APIs, including the Job Scheduler, Android Architecture Components (including Live Data and Room), and Data Binding
• Offers a detailed exploration of the Android Studio IDE and an introduction to Kotlin
• Introduces material design principles and offers a detailed look at design guidelines, navigation patterns, and UI best practices
• Demonstrates techniques to create compelling UIs for all Android form factors
• Explores Firebase and Google Play services API libraries, including maps, location-based services, and the Awareness API

Support

You can download all the code snippets and sample projects used in the book from the Wrox site or from our GitHub repo.

If you’ve got any questions, you can get in touch with me over on Twitter (@retomeier). If you find a bug in the code, please raise an issue and we’ll get it fixed. For more help with Android development, I’d recommend using Stack Overflow; Ian Lake and I will keep an eye out and help where we can.

We’re also known to frequent some of the more popular Android Slack channels.

Where to buy

Professional Android 4E is currently available for purchase at these fine retailers:

• Amazon.com
• Barnes & Noble
• The Book Depository
• Your local bookseller

If you prefer to travel light, there’s an electronic version to suit your tastes:

• Kindle US
• Kindle UK
• Google Play Books
• DRM-free PDF, Mobi, and ePub coming soon from Wrox
• Safari Books coming soon

Designing a global metaverse

In the second episode of Build Out, Colt McAnlis and Reto Meier were given the challenge of designing a global metaverse.

Take a look at the video to see what they came up with, then continue reading to see how you can learn from their explorations to build your own solution!

TL;DW: What they designed

Both solutions describe a design to generate a 3D environment that users experience using a virtual reality headset, using various levels of cloud compute and storage to provide virtual Earth data to the client, and calculate changes to the world environment as users interact with it.

Reto’s solution is focussed on creating a virtual clone of the real world using millions of drones to obtain real-time sensor readings. His virtual space is intrinsically linked to the real-world, including everything from geometry to prevailing weather conditions.

Colt’s solution leverages his video game development experience with a system design that completely disconnects the virtual world from the physical world. His architecture details the required framework for building the back-end services for an MMO (or other large scale cooperative space.)

Building your own global metaverse

The biggest difference in these designs is the source of the virtual environments’ climate and geometry. Reto’s design relies on analyzing the results of sensors in the real-world, where Colt’s system uses artists to contribute artificial landscapes and buildings.

If you want a system that incorporate real-world geometry and textures, you can use Google Maps as inspiration.

Their system uses a combination of imagery and sensor data to generate 3D models along with texture information for those models. This allows them to generate very realistic 3D representations of urban environments without needing to employ an army of artists to recreate the same content.

Mirroring this process let’s us generate a very similar representation. We can use satellite data, LIDAR input, and drone photography from various angles and sources, and push that into a GCS bucket.

Along with that, we generate work information and push work tokens off to pub/sub. We have a fleet of pre-emptive VMs working to gather those pub/sub requests, and start doing 3D meshing and texture atlas generation. The final results are pushed into a GCS bucket as well.

Why pre-emptive VMs? PVMs allow themselves to be terminated by the Compute Engine manager. As such they offer a significantly discounted price than standard VMs of the same configuration. Since their lifespans are volatile, they are perfect for batch work jobs where work may get interrupted and not completed.

Pub/sub works hand-in-hand with PVMs in this regard. Once a PVM received a termination signal, it can stop work, and push the workload back to pubsub for another PVM to pick up and work on later.

Alternatively, or for areas where this algorithm fails, you could allow users to submit custom models and textures for iconic landmarks, which we can then be inserted into the generated 3D environment.

Storing and distributing metaverse binary data

Once all of our mesh and texture data has been processed, the result will be literal terabytes of virtual environment environmental data. Obviously, we can’t stream all that to each client at once, so instead, we bundle up model data based upon geographic boundaries.

These “regional blobs” are indexed, contain metadata, and can be stored in multi-layered compressed archives so that they can be streamed to the client.

To compute this, use the same offline build-process as for 3D mesh generation; specifically you can generate a bunch of tasks for pub/sub, and use an army of preemptive vms to compute and combine the proper regional blob archives.

Distribution of the regional archives to the client depends on the user’s “physical” location in the virtual universe — as well as what direction they’re facing.

In order to optimize load times for the client, it’ll make sense to add a local cache for areas they visit frequently, to keep them from having to download gigs of data every time they enter a new area.

For the sake of diagram clarity, we can wrap this entire process up as an offline system, and call it “Automated Content Generation” (ACG).

Over time, the local cache will become invalid, or updates will be need to be pushed out to the user. For this, we put together an update & staging process, where a client can receive the updated environment data as they log-in, or as they re-enter a zone they have recently visited.

Why GCF? There’s many ways to allow a client to check for updates. For example, we could create a load balancer which auto-scales a group of GCE instances. Or we could have made a Kubernetes pod which can scale for requests as well.

Or we could have used app engine flex, which would allow us to provide our own images, but scale just the same. Or we could have used app engine standard, which has its own deployment and scaling.

The reason we chose Cloud Functions here: First, GCF has enhanced support for Firebase push notifications. If something’s occuring, and we need to notify the client of an emergency patch, we can push that data to the client directly.

Secondly, GCF needs the least amount of work to get a function deployed. We don’t have to spend extra cycles configuring images, balancing, or deployment specifics; we simply just write our code, and push it out to be ready to be used.

Simulation data for your metaverse

As your users move and interact with the virtual environment, any changes they make will need to be synced with the rest of the universe data and shared with other users.

You’ll need some composite components to make sure that user actions aren’t violating any physical rules, and then a system for storing or broadcasting this information to the other players.

For this, you can leverage a set of App Engine Flex groups, called “World Shards” which allow geographically similar clients to connect and exchange data on position and movement information. So as a user enters a game zone, we’ll figure out their closest region, and connect them directly to the appropriate World Shard.

Why App Engine Flex? For the World Shards, we could have easily used an instanced group of GCE VMs, which share an image, however app engine flex gives us that same functionality w/o needing the extra maintenance overhead. Likewise, a GKE Kubernetes cluster would have done the trick, but for our scenario, we didn’t need some of the advanced features that GKE provides.

We’ll also need a separate set of compute instances to help us manage all the secondary world-interaction items. Things like purchasing goods, inter-player communication, and so on. For this you can spin up a second group of App Engine Flex instances.

All persistent data that needs to be distributed to multiple other clients will be stored in cloud Spanner, which will allow regionally similarly clients to share information as soon as it happens.

Why Spanner? We chose spanner here due to it’s managed service, global capacity, and ability to scale to handle very high transactional workloads. You could have also done this with a SQL system, but at that point, you’re doing a lot of heavy lifting in order to get the same effect.

Since our code will change often, we need to augment our updating and staging servers to also distribute code to our world-shards. Do this, we allow compute-level staging to occur in our staging code, and push images to Google Container Registry to be propped out to various world shards and game servers as needed.

Drawing your metaverse

A metaverse isn’t a metaverse unless you have a strapped on headset. For this, you can leverage Google VR and the Android Daydream platform to render our massive metaverse within a fully immersive VR experience. However Daydream by itself is not a proper rendering engine, so you’ll need to leverage something like UNITY as a tool to help draw all our models and interact with the Daydream system on our behalf.

Describing how to properly render millions of polygons per frame in VR mode is a big challenge, but one that’s outside the scope of this article ;)

Account & Identity services

We’re going to add an app engine front-end instance that leverages Cloud IAM to authenticate and identify the user, and communicate with the account management database, which may include sensitive information like billing and contact data.

Why App Engine Standard? We chose app engine standard as the front-end service for our IAM system for a number of reasons.

Firstly is that it’s managed, so we don’t have to deal with provisioning and deployment details like with containers/ GKE/ App Engine Flex.

Secondly, it has built in IAM rules and configurations, so we can write less code and get the right security and login systems we need.

Thirdly, it has direct built in support for datastore, which we use to store all of our IAM data.

To hear a more detailed account of some of our choices, check out our companion podcast, Build Out Rewound, our on Google Play Music, iTunes, or your favorite podcast app / site.

Add a comment here, or on our YouTube video, with any questions you have about our system design or technology choices.

Building a Virtual World Worthy of Sci-Fi was originally published in Google Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Reto Meier and Colt McAnlis present Build Out (Episode 1)

A Garden that Cares for Itself

Designing an autonomous, learning smart garden

In the first episode of Build Out, Colt and Reto — tasked with designing the architecture for a “Smart Garden” — supplied two very different concepts, that nevertheless featured many overlapping elements. Take a look at the video to see what they came up with, then continue reading to see how you can learn from their explorations to build your very own Smart Garden.

TL;DW: What they built

Both solutions aim to optimize plant care using sensors, weather forecasts, and machine learning. Watering and fertilizing routines for the plants are updated regularly to guarantee the best growth, health, and fruit yield possible.

Colt’s solution is optimized for small-scale home farming, using a modified CNC machine to care for a fruit or vegetable patch. The drill bit is replaced with a liquid spout, UV light, and camera, while the cutting area is replaced with a plant bed that includes sensors to track moisture, nutrient levels, and weight.

Reto’s solution extends a typical garden sprinkler / reticulation system into a distributed autonomous garden care system. A liquid / fertilizer spout and soil sensors are incorporated into each stand-alone device, each of which is installed alongside a plant in your garden — allowing each plant to receive a custom care regime.

How to build your own Smart Garden

The basic system architecture is based around the following common components:

• One or more devices powered by a microprocessor / micro-controller.
• An Android Things “hub” that connects the garden care device(s) to the cloud.
• A cloud server component built around an App Engine Flexible environment that processes camera and sensor results, and determines the appropriate changes to the plant care instructions.
• A machine learning implementation built around TensorFlow models to analyze camera images and optimize plant-care instructions.
• User client(s) to monitor (and control) the garden behavior and status.

Garden-side client architecture

The garden care devices — both the CNC rig and individual plant-care sensor/sprinklers — are optimized for their specific role monitoring sensors and controlling the actuators used to move the arm, trigger the water and fertilizer delivery mechanisms, and collect camera footage.

The specific hardware and software platforms you select for this component will be determined by your specific needs. Colt’s “home-made” CNC machine is driven by a Raspberry Pi, while Reto’s per-plant devices are built on micro-controller like the ESP32.

In any case, you don’t want to connect the plant-adjacent hardware directly to the cloud. Reto’s solution includes multiple devices connected together in a Bluetooth mesh, while even Colt’s CNC system may include multiple CNC devices in each location — so we connect each device wirelessly to a “hub” built using Android Things.

Why Android Things? Android Things is designed to build connected devices using the powerful development tools and APIs used to build apps for Android within Android Studio. Reto and Colt are both experienced Android developers, so this is a big shortcut for them, but you can use any language / SDK supported by the underlying hardware platform.

The Garden Hub serves as the single point-of-contact between garden device(s) and the cloud-based service, and as such serves two purposes. It:

• collects and aggregates the readings from all available sensors and cameras, and uploads them to the cloud for analysis.
• controls the behavior and operation of each garden device (usually based on the results of the cloud analysis.)

To control the operation of each garden device, each hub begins with a set of default garden care instructions, which are updated as needed. The default and current settings are all stored locally, ensuring full offline support so that losing connectivity won’t prevent the garden being watered.

One solution for local offline support is using the Firebase Real-time Database, which is well supported by Android Things and, as well as offline support, can provide auto-synchronization with our server and client implementation.

Why Firebase Real-time Database? The data being stored within each hub is simple, non-relational, and relatively small. Firebase is a NoSQL database that can handle the volume of data we’re storing, takes care of data synchronization for us, and remains available if we go offline. This is handy as it ensures we don’t need to create multiple database storage and synchronization implementations across multiple systems.

The need to have a local database depends on the complexity of your planned garden setup. In the case of a single CNC machine, a database may be overkill — while having a device per plant necessitates a fairly robust database mechanism.

Choosing to include support for web- or app-based modification / control of your garden makes the use of an auto-synced database even more compelling.

Data collection & storage

All your sensor data, including camera imagery, should first be collected at the Garden Hub before being transferred to our cloud for analysis. The frequency at which you collect and upload this data will vary based on the size of your garden, and the resolution of your analysis.

The camera imagery can all be uploaded easily to Google Cloud Storage (Object/File Serving & Storage), Google’s unified object storage offering.

There are multiple options for uploading and storing the sensor data. One particularly flexible approach is to use Cloud Pub/Sub (Distributed Real-time Messaging) to aggregate and publish the sensor data from each garden into a single durable stream, to which multiple cloud components can subscribe.

Why Cloud Pub/Sub? Pub/Sub provides low-latency, durable message delivery based on a many-to-many, asynchronous stream that decouples senders and receivers. That means we’re guaranteed that each message will be received, potentially by multiple subscribers within our server implementation (such as App Engine, BigQuery, and Dataflow.)

Using Pub/Sub allows us to decouple of the uploading and processing steps. This is particularly powerful if multiple components in our cloud architecture need to consume the same data. It also allows us to use services such as Cloud Dataflow (Managed Data Processing) to modify the incoming stream, or perform real-time analytics, prior to processing by our analysis engine.

More recently, Google released Cloud IoT Core in Beta. This fully managed service utilizes Pub/Sub, and is designed specifically to securely connect, manage, and ingest data from globally dispersed devices. IoT Core also enables you to re-configure those devices on-the-fly.

Server-side data analysis

Once the data is uploaded to your cloud, it’s ready for analysis to determine what changes — if any — need to be made to the existing garden-care routine. There are a number of options for building your analysis engine, here we’ll use AppEngine Flex (Managed App Platform).

Why AppEngine? AppEngine is a platform-as-a-service, so we can focus on the code rather than infrastructure, and let Google handle scaling and reliability. We’re using Flex instead of Standard to support our use of the Google Cloud Tasks API. As a new project, there’s no legacy or hybrid infrastructure requirements that might suggest using containers.

Also we both work at Google, so we know someone who can hook us up with a bunch of credits.

To initiate the analysis process, you can either ping an AppEngine front end from the hub once the sensor / image uploads are complete, or subscribe directly to the pub/sub streams for incoming sensor data or modifications to a GCS bucket.

To perform the analysis the Google Cloud Tasks API is used to initiate an AppEngine Flex instances to perform the analysis.

Why not use a Google Cloud Function? A possible alternative to the Cloud Tasks API is triggering a cloud-function based on either the GCS bucket change or Pub/Sub stream. However, the max duration for a Cloud Function is 540 seconds, which may be insufficient for performing image analysis; the Cloud Tasks API supports tasks of up to 24 hours.

Image analysis is used as a signal for determining plant health, so we need to train TensorFlow models to recognize everything from plant decay, to bugs, fruit, and leaf size.

To simplify our use of TensorFlow, we can use the Google Cloud Machine Learning engine, which can host TensorFlow models for training, prediction — providing you with a managed, serverless solution for training TensorFlow models and using them for prediction.

To devise a care routine for each plant, we start with a Datastore (Distributed Hierarchical Key-value Storage) database that contains the baseline care instructions for a variety of plant species.

Why Datastore? There’s a large variety of database options we could use here, but the nature of the plant-care database — largely static, potentially very large, and without any relational constraints — is ideal for a NoSQL database, particularly one that is well supported by AppEngine.

The baseline instructions are modified based on a combination of the image analysis results, plant sensor readings (soil moisture / pH, etc.), and environmental signals including local weather forecasts, seasons, and day / night cycles.

Updating the garden care routine

Having completed your analysis and determined any required updates to your gardening routine, you need to update your Garden Hubs.

If you’re using the Firebase Real-time Database, you can record those changes server-side and let Firebase synchronize those changes on the hub. If not, you’ll need to implement your own synch mechanism to transfer and apply the updates.

All changes made to the plant care routines, and the health-history of each plant is all sent to BigQuery (Managed Data Warehouse / Analytics), that acts as a data pool of training data for TensorFlow that’s used to learn how to better care for each plant based on prior results.

Your BigQuery dataset is also connected to Data Studio (Visualize and Explore Data), which can be used to create real-time visualizations indicating overall garden health status and trends.

User website and app

At this stage your garden is completely autonomous; the client / garden owner has neither visibility into the system settings, nor the ability to manually modify its behavior.

There are a number of options for a client-side implementation, depending on the level of information and control you wish to make available, and the platforms you wish to support.

It’s likely you’ll want at least a web client, and native Android apps that allow users to observe the current plant care settings for their garden, and manually alter the schedules or trigger things like watering or fertilizing on demand.

Why Android? You’re going to build an iOS mobile app as well as Android, and the Firebase Real-time Database and Cloud Messaging both feature an iOS SDK — so when we say “Android” we really mean “Android and iOS”. [Ed: Don’t tell anyone we said this.]

If you used the Firebase Real-time Database to store the plant care settings, the web, Android, and iOS clients can automatically get updated when those settings change — and similarly, providing functionality to modify and save to Firebase within those clients will see the settings updated on the server and Garden Hub.

If you’re building native mobile apps, you can utilize Firebase Cloud Messaging to send realtime notifications for time-sensitive gardening alerts (such as detecting a repeated failure in watering, or mechanical issue with the CNC arms.)

You may also choose to add peer-to-peer control to your mobile client, providing a way for users to modify the settings on the hub even if you have no Internet connectivity. Using Bluetooth you can connect to the local hub and modify the care instructions, turn on specific sprinklers, or otherwise control the garden devices.

Further features depend on your needs. You can use more TensorFlow models to recognize plants by taking a photograph, add support for manual tasks such as pruning or harvesting, or add voice control through Google Assistant Actions.

The fundamentals of both systems are very similar; the most meaningful difference in the two solutions is flexibility versus cost. Colt’s CNC design is restricted to a fixed unit into which your plants are planted, while Reto’s IoT Devices involve a separate device for each plant — improving flexibility at the expense of… expense.

To hear a more detailed account of some of our choices, check out our companion podcast, Build Out Rewound, our on Google Play Music, iTunes, or your favorite podcast app / site.

Add a comment here, or on our YouTube video, with any questions you have about our system design or technology choices. In the meantime, check out some of these awesome real-world Smart Garden implementations:

• Farmbot
• Rachio Smart Sprinkler
• Cucumber Farming with Deep Learning and TensorFlow
• Edyn

Building a Garden that Cares for Itself was originally published in Google Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Crime Hotspots and How to Find Them with BigQuery

Crime Distribution in San Francisco

Map all the SFPD incidents since 2010 and the Tenderloin stands out as a place you probably want to be careful if you’re visiting The City.

I wondered how different types of crime are distributed around the City by the Bay — so I fired up BigQuery and used the SFPD Incidents public dataset to investigate.

By calculating the combined standard deviation of the latitude and longitude of each type of crime, we can find which crimes are the most heavily concentrated in a particular area — highlighting hotspots for specific crimes across the city.

SELECT
  descript,
  STDDEV(latitude) as sdLat,
  STDDEV(longitude) as sdLong,
  count(*) as count
FROM 
  `bigquery-public-data.san_francisco.sfpd_incidents` 
WHERE 
  (Latitude IS NOT NULL) AND (Longitude IS NOT NULL) 
  AND (Latitude > 30) AND (LATITUDE < 40) 
  AND (Longitude > -130) AND (Longitude < -120)
GROUP BY
  descript
HAVING 
  sdlat is not null and sdlong is not null 
  AND count > 500
ORDER BY 
  sdlat+sdLong ASC

The results with the lowest standard deviation represent crimes with the highest location-concentration; the top 10 are shown in the table below. Note that drugs and prostitution make up half the top 10.

In total, 75% of the drug-related crimes in the categories from the top 10 table above occurred in this part of San Francisco shown in the map below:

Prostitution is also tightly concentrated, but in this case most police incidents occur in two separate, but distinct, areas:

Crimes with the highest location standard deviation — or greatest spread — are shown in the table below, with burglary, theft, and found property constituting 7 of the top 10.

These crimes tend to occur all over the city; as you can see in the map below that shows locations with at least one burglary or attempted break-in:

Looking at the most common burglary-related incident type, residential break-ins, 74% of incidents occurred in a location with only one break-in. The map below shows only locations with 5 or more burglary break-ins:

BigQuery includes many more public datasets for San Francisco, as well as other cities including New York and Chicago. What can you discover about the cities we live in?

If you’re new to BigQuery follow these getting started instructions, and remember that everyone gets 1TB and 10 GB of storage at no charge every month to run queries.

Share your investigations with us at reddit.com/r/bigquery and subscribe to Today I Learned with BigQuery for more BigQuery public dataset investigations.

Crime Hotspots and How to Find Them was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Using the NOAA GHCN and GSOD datasets

The analysis in today’s Today I Learned with BigQuery was performed by @savio_lawrence using BigQuery, Tableau, and the NOAA GHCN and GSOD datasets to see what observations we can make about changes in the average temperatures recorded at weather observation stations around the world.

Before we dive-in, note that while interesting, this analysis by itself is insufficient to draw conclusions for something as complex as climate change or global warming.

Scientific organizations and researchers work hard to account for the challenges we’ll highlight throughout the analysis, as well as combining datasets like these with remote sensing observations to obtain global and consistent coverage of the Earth.

One of the beauties of having access to such vast datasets is opportunity to explore some of the data that underpins scientific research to find areas for potential future exploration and analysis.

Using BigQuery and Tableau to Analyze Climate Data

Using BigQuery we processed and summarized 196 tables (74GB+ with over 2.5 billion records) from the GHCN and GSOD public datasets.

NOAA’s Global Historical Climatology Network (GHCN) is an integrated database of climate observations subjected to a common suite of quality assurance reviews.

The NOAA Global Surface Summary of the Day (GSOD) dataset includes weather observations from a much larger set of stations (over 9,000), but these haven’t undergone any review. As a result they tend to be less reliable than the GHCN observations.

We used the SQL statement below to perform the following functions, and applied it to both datasets.

• Find the yearly average temperature per station using the aggregate function.
• Group stations into latitude bands.
• Group years into decades using conditional statements and the like operator.
• Finding the number of readings per year for each station, using the count function.

SELECT
  ID AS STN,
  PERIOD_TS,
  NUMOFDAYS,
  AVERAGE_TMP_C,
  LATITUDE,
  LONGITUDE,
  NAME,
  STATE,
  CASE
    WHEN LATITUDE >= 66  AND LATITUDE <=90  THEN 
      "ARCTIC CIRCLE"
    WHEN LATITUDE >= 23  AND LATITUDE < 66  THEN 
      "NORTHERN TEMPERATE ZONE - BETWEEN TROPIC OF CANCER & ARCTIC CIRCLE"
    WHEN LATITUDE >= 0   AND LATITUDE < 23  THEN 
      "NORTHERN TROPICS - BETWEEN TROPIC OF CANCER & EQUATOR"
    WHEN LATITUDE >= -23 AND LATITUDE < 0   THEN 
      "SOUTHERN TROPICS - BETWEEN TROPIC OF CAPRICORN & EQUATOR"
    WHEN LATITUDE >= -66 AND LATITUDE < -23 THEN 
      "SOUTHERN TEMPERATE ZONE - BETWEEN TROPIC OF CAPRICORN & ANTARCTIC CIRCLE"
    WHEN LATITUDE >= -90 AND LATITUDE < -66 THEN 
        "ANTARCTIC CIRCLE"
  END AS STN_ZN,
  IF (NUMOFDAYS >= 300, "GREATER THAN 300", "LESS THAN 300") AS NUMOFDAYS_IND,
  CASE 
    WHEN YEAR LIKE "192%" THEN "20S"
    WHEN YEAR LIKE "193%" THEN "30S"
    WHEN YEAR LIKE "194%" THEN "40S"
    WHEN YEAR LIKE "195%" THEN "50S"
    WHEN YEAR LIKE "196%" THEN "60S"
    WHEN YEAR LIKE "197%" THEN "70S"
    WHEN YEAR LIKE "198%" THEN "80S"
    WHEN YEAR LIKE "199%" THEN "90S"
    WHEN YEAR LIKE "200%" THEN "2000S"
    WHEN YEAR LIKE "201%" THEN "2010S"
  END AS DECADES
FROM (
  SELECT
    A.ID,
    A.PERIOD_TS,
    COUNT(*) AS NUMOFDAYS,
    ROUND(AVG(A.VALUE/10),1) AS AVERAGE_TMP_C,
    B.LATITUDE,
    B.LONGITUDE,
    B.NAME,
    B.STATE,
    A.YEAR
  FROM (
    SELECT 
      *,
      TIMESTAMP_TRUNC(TIMESTAMP(date), YEAR) AS PERIOD_TS,
      CAST((EXTRACT(YEAR from DATE)) as String) AS YEAR
    FROM
      `bigquery-public-data.ghcn_d.ghcnd_*` ) A
  INNER JOIN
   `bigquery-public-data.ghcn_d.ghcnd_stations` B
  ON
    A.ID = B.ID
  WHERE
    A.ELEMENT = 'TAVG'
  GROUP BY
    B.LATITUDE, B.LONGITUDE, 
    B.NAME, B.STATE, A.ID, A.PERIOD_TS, A.YEAR)

Once we summarized the data — along with the derived attributes including latitude zone, decade, and station observation count — we used Tableau to visualize the results.

Tableau allows us to provide a SQL statement to use as an input into a dashboard, and provides multiple visualization techniques including:

• Map view with colored labeling to help visualize temperature trends by station location.
• A bar chart with reference lines to plot average temperatures by decades.
• Line charts to plot temperature averages by year.

Using this approach, we created simple dashboards that made it easy to understand our results, and perform more detailed analysis.

GHCN Tableau Dashboard | GSOD Tableau Dashboard

Both sets of data indicate a trend of global temperature increase since the 1970s

The graphs below show a gradual increase in net average global temperature each decade using both the GSOD and GHCN station data.

To generate these graphs we used stations with at least 300 measurements in a given year, starting in 1973 (as explained below.)

Of course, this doesn’t tell the whole story — global temperature isn’t consistent, so some places may have gotten warmer and others cooler. Later in this article we’ll look at trends at different latitudes to better understand the data.

We want to consider the full data, so we’ll perform as little filtering as possible

Significant changes in the number and location of weather stations is likely to have a significant impact on the average temperature, as will big shifts in population, and relative density of reporting locations.

For our analysis to be meaningful, we want to start in a year after which there aren’t big jumps in the number of reporting stations, and there’s a reasonable global coverage of stations.

Our data suggests that 1973 was the first year in which the GSOD and GHCN databases both matched these criteria, as shown in these graphs comparing 1972 and 1973.

Notice, for example, that in 1972, there are few stations in South America; in 1973, there are a lot more. It’s also important to note that the GSOD database includes many stations that record only a small number of observations per year — for example, some station observations are only in the summer.

To avoid potential seasonal bias in our data we’ll filter out any station with fewer than 300 observations in a given year.

Comparing the GSOD to GHCN trends since 1973 for stations with 300+ observations

The graphs below break down the decade-averages into a year by year plot for each data set.

This data looks like it’s gotten a lot warmer, but let’s not rush to any conclusions. Averages can be influenced in many ways, and our data might be biased. Notice that over the same time period, there’s a significant increase in the number of reporting stations.

If those new stations are disproportionately located at warmer regions, that could bias the average towards warming.

Looking at the overall distribution of stations for both datasets, the map shows that there are more stations in the most heavily populated latitudes (that have more temperate temperatures), and that there are fewer stations in more remote areas (that are frequently much colder).

As a result the regions with most stations will have disproportionate influence on the overall climate trends.

To begin to account for these potential issues, we can use a common data science technique of separating out our analysis into smaller slices of the data — in this case regions based on latitude bands.

The Arctic: The North Pole (90°N) to the Arctic Circle (66°N)

Both datasets indicate a significant gradual increase in the average temperature reported over the arctic circle each decade.

GSOD shows a 1.08°C increase from the 70’s to the current decade, and GHCN a 0.67525°C increase over the same period .

Observing both results suggests that the Arctic circle has been warming.

If we take a look at the number of stations used each year, it suggests there’s no correlation between the reporting station count and the temperature recorded.

Northern Temperate Zone: The Arctic Circle (66°N) to The Tropic of Cancer (22.5°N)

The Northern Temperate Zone includes the largest number of stations, and represents the most highly populated region.

In the graph below, notice the gradual increase in average temperature as we move from the Arctic Circle to the tropics as indicated by the color of each dot representing the average temperature at each measurement station.

As with the Arctic Circle, both the GSOD and GHCN datasets show a gradual increase in the average temperature reported across the Northern Temperate Zone each decade, though at a slower rate resulting in 0.455°C and 0.65275°C increases in the GSOD and GHCN sets respectively.

Northern Tropics: The Tropic of Cancer (22.5°N) to The Equator (0°)

When we reach the Northern Tropics, the graphs below show that while the number of stations providing readings has increased significantly since the 1970’s, a gradual temperature increase over time is seen in both datasets — we observe an average temperature increase of 0.1312°C and 0.0279°C per decade in GSOD and GHCN sets respectively.

This further reinforces the observation that the larger number of stations isn’t a significant factor in increased global average temperature increases, and also demonstrates that the overall warming trend isn’t consistently observed in all locations.

Southern Tropics: The Equator (0°) to The Tropic of Capricorn (23°S)

The same is true for the Southern Tropics, with only a slight increase in average temperature and a significant increase in reporting stations.

The average increase in temperature per decade for GSOD and GHCN has been 0.186°C and 0.283°C respectively.

Southern Temperate Zone: The Tropic of Cancer (22.5°S) to The Antarctic Circle (66°S)

In the Southern Temperate Zone, both datasets saw a small, but gradual decrease from the 1970’s to 2000’s of around 0.5°C.

Interestingly, the 2010’s have so far almost entirely countered that drop, recording the highest average since the 1970’s thanks principally to a very hot 2015 and 2016.

Are these two years outliers or the beginnings of a new warming trend for the Southern Temperate Zone? It’s too early to say based on only two data-points, but it’s an observation worth tracking.

The Antarctic: The Antarctic Circle (66°S) to The South Pole (90°S)

The number of stations in the Antarctic is very low compared to the other latitude bands— the GHCN records don’t include any stations with over 300 readings until 1980 within the Antarctic Circle.

The measurements we do have, indicate a general trend of decreasing average temperature each decade, of 6°C and 10°C for the GSOD and GHCN data respectively.

Note that most of the weather stations are on the edge of the continent, with very few stations in deeper Antarctica.

There is a pattern that Northern Antarctic stations have higher temperature than those located deeper within Antarctica.

The newer reporting stations are skewed towards those deeper, lower temperature, locations that may be contributing to the observed temperature decrease.

These challenges make the Antarctic data especially difficult to analyze, and require additional steps to gain a better understanding of changing temperatures there.

You’ll find that investigation in a future article.

The raw GSOD data generally agrees with the quality controlled GHCN results

Both datasets, when observed globally, indicate an increase in observed temperatures of 0.7125°C (GSOD) and 0.532°C (GHCN) each decade since the 1970s.

However, when we dig into the details on a regional basis the picture is more complex and a lot less open to simple conclusions.

The Arctic circle appears to be warming at the highest rate of approximately 1.084°C (GSOD) and 0.675°C (GHCN) each decade; and average temperatures in the Northern Temperate zone are also increasing at 0.455°C(GSOD) and 0.653°C (GHCN) / decade.

Meanwhile the tropics have maintained a fairly constant to marginal temperature rise per decade — Northern Tropics 0.13125°C (GSOD) and 0.0279°C (GHCN) while Southern Tropics have a rate of 0.186°C (GSOD) and 0.283°C (GHCN).

And the Southern Temperate zone has been slowly cooling at around 0.01905°C (GSOD) and 0.13°C (GHCN) each decade.

Climate science is… complicated

When measuring something as complex as climate change, it’s important to understand the many variables that can influence our results. For example, even in our relatively simple datasets new stations can be added, measurement stations can move, the surrounding landscape can change, and instruments can become more precise / accurate.

Analyzing this data can provide useful starting points to form hypotheses and begin further investigations into the broader trends — and causes — that they may represent.

If you’re new to BigQuery follow these getting started instructions, and remember that everyone gets 1TB at no charge every month to run queries.

Share your investigations with us at reddit.com/r/bigquery and subscribe to Today I Learned with BigQuery for more BigQuery public dataset investigations.

Investigating Global Temperature Trends with BigQuery and Tableau was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

San Francisco. Fog City. The City by the Bay. No matter what you call it, those 47mi² are home to over 800,000 people about whom we can draw outrageous conclusions using the new San Francisco public dataset in BigQuery.

Thanks to the City and County of San Francisco’s SF OpenData project and Bay Area Bike Share, Google BigQuery’s Public Datasets now includes San Francisco public data, including:

• 2+ million 311 service requests since 2008 (updated daily)
• 2+ million SF Police Department incidents since 2003 (updated daily)
• 4+ million SF Fire Department service calls since 2000 (updated daily)
• The location and species for more than 100,000 sidewalk trees (updated quarterly)
• Bay Area Bike Share stations and almost 1 million Bay Area Bike Share Trips since 2013 (updated daily)

SF Protip #1: Don’t leave valuables in your car when parked on Bryant. Especially on Friday. Definitely not before lunch.

In 2016, stolen cars and theft from locked cars accounts for over 21% of all SFPD crime incidents.

SELECT
  ROUND(100*countif(strpos(descript, "STOLEN AUTOMOBILE") > 0)
    / count(*)) as stolen_pct,
  ROUND(100*countif(strpos(descript, "THEFT FROM LOCKED AUTO") > 0)
    / count(*)) as theft_pct
FROM
  `bigquery-public-data-staging.san_francisco.sfpd_incidents`
WHERE
  category != "NON-CRIMINAL" AND category != "SECONDARY CODES"
  AND category != "WARRANTS" AND EXTRACT(YEAR from timestamp) = 2016

Reports of thefts from locked cars peak on Friday at lunch-time and after-work, presumably as folks are arriving at their cars to find them ransacked. Parking on Bryant is easily the best way to maximize your chances of having your stuff stolen.

If you’re looking to minimize your chances of being a victim of grand theft from a locked auto, this heatmap shows where most thefts from cars have been reported — avoid parking there.

SF Protip #2: If you lose something on Bryant, try looking for it on Valencia and Eddy.

Nearly 20% of all lost property was reported on Bryant Street. The best odds for finding something are over on Valencia and Eddy, where more than twice as many items are reported found than lost.

You’re most likely to come back to your car and find it missing entirely if you parked on Mission Street. Especially on Friday.

SF Protip #3: Sunset Boulevard is the leafiest street in San Francisco.

You can get a taste of Monterey by visiting Sunset Boulevard — the leafiest street in San Francisco, with more trees than any other street, including nearly 1,400 Monterey Pines and Monterey Cypress. If you’re looking for a shady spot, the heatmap below shows the density of tree distribution throughout the City.

SF has a greater density of trees than NYC, featuring 2,637 trees per square mile compared to NYC’s 2,242.

Like New York, the most common tree in San Francisco is the London Plane Tree, which represents 7% of all SF trees, compared to 13% of the trees in New York. San Francisco trees are much more diverse, you’ll find 492 different species, compared to New York’s 183.

San Francisco can boast at least 1 example of each of New York’s top 7 tree species, where New York has none of San Francisco’s, apart from the London Plane.

SF Protip #4: San Francisco residents complain more than New Yorkers.

Per capita, San Francisco uses its 311 municipal complaint line more often than NYC. The most common complaints in SF are requests for sidewalk cleanups, rubbish pickups, and graffiti removals.

SF Protip #5: There’s more graffiti, but at least it’s getting more polite.

The biggest growth in complaints is in the categories that already represent the largest volume, with bulky item pickup requests and illegal homeless encampments rising most quickly.

Note that non-offensive graffiti is on the rise, with offensive graffiti is the fastest dropping complaint category. So either San Franciscans are writing less offensive graffiti, or the residents have grown immune to the current attempts to offend.

SF Protip #6: Steep hills make for slower bike rides.

Riders of the Bay Area Bike Share network average 14 minutes per ride — nearly the same as the 15 min average for New York Citi Bike riders. San Francisco is a smaller town though, and riders average only 1.4 km per trip, compared to New Yorkers, who go 1.8 km.

Or put another way, San Francisco cyclists are 0.3 m/s slower than their New York counterparts. I blame the steep hills.

SF Protip #7: Marijuana legalization will reduce arrests in Haight-Ashbury by 7%.

Marijuana represents 26% of all drug related arrests in San Francisco. In 2016, it was overtaken by meth as the most likely drug involved in an arrest.

Overall, drug crime is down 62% since 2003, with fewer arrests for all drugs — except methamphetamine, which is up 14%.

The map below shows all areas where there have been at least 5 drug-related arrests since 2003. Red pins are for marijuana, blue for crack cocaine, pink for meth amphetamine, green for heroine, and yellow represent cocaine.

The larger pins indicate over 200 drug-related arrests in the same location.

The Tenderloin is the center of San Francisco’s drug arrests, particularly for crack cocaine — the arrests for which are clustered around specific locations; methamphetamine arrests, however, are more likely to follow roads.

Marijuana arrests spike in the Haight-Ashbury neighborhood, where they account for 7% of all arrests.

SF Protip #8: The best place to have your house catch fire is Chinatown. The most likely place is the Tenderloin. If you’re lucky they’ll dispatch Engine 3.

The average response time between calling 911 and firefighters arriving at your burning building is 6 minutes 8 seconds, with units arriving at Chinatown blazes at an impressive city-wide best of 4 minutes and 30 seconds.

The fastest fire truck in the SFFD is Engine 3 based in the 4th battalion with an average response time of just over 3 minutes.

This is a taste of what you can explore and find using this new San Francisco dataset.

Join us here every week, for Today I Learned with BigQuery, as we dig into each of these table in more detail, use the NOAA weather tables to explore the effect of weather on crimes and 311 calls, and compare what we know about San Francisco and other cities, starting with New York.

If you’re new to BigQuery follow these getting started instructions, and remember that everyone gets 1TB at no charge every month to run queries. When you’re done remember to share the results with us using #TILwBQ.

Exploring San Francisco’s Public Data with BigQuery was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

When you’re talking software and you mention “offshore workers”, chances are you’re thinking about dev shops in India.

I grew up in Western Australia, where you can get a job in both major industries: resource mining or extraction.

Sure, I chose oil & gas — but I joined a dynamic startup as the ‘lynch pin of their new technical department’.

Sick of paying licenses to other companies for their software, I was hired to write some for them.

Day 1

New desk, and new copies of Delphi 3…

…and “Learn Delphi in 24 hours”

…23 hour later

I’m living the dream as a software engineering Tech Lead, writing software for coordinating and logging offshore oil & gas installation inspections.

Lesson 1

The sole developer of untested, mission-critical software, should expect to be going wherever that software goes.The

The day after I cobbled together a working demo, the software was committed to our next job. As was I.

That job is 150km (95mi) offshore, and lasts for 3–6 weeks.

The facilities onboard the support vessel we would be calling home, could be considered…

Things have changed now, but in the 90’s that meant no broadcast TV, no radio, no phones, and absolutely no Internet.

That means no Stack Overflow.

There was a satellite phone, and for only $1 / second I could reach out and touch somebody.

When the boat’s orientation was just right (and the stars were aligned) there might be a nightly email uplink to send / receive email on a public, unsecured PC running Outlook Express.

Nothing larger than 50kb and absolutely no attachments.

What to Bring?

3 pair coveralls (pre-washed and worn to avoid the n00b look), hard hat, steel tipped safety boots, sandals, underwear, socks, t-shirts, and razor and toiletries (to prepare for the flight home).

No alcohol, drugs, explosives; and the total allowance is 10Kg (22lbs).

If it’s a decent sized job they’ll do your laundry nightly, so a few clothes go a long way; just remember they wash them at around 150C, so no delicates.

Or nylon.

Or dark colors.

Mobilization is from Karratha

The flight leaves at 6am on Monday morning.

Pre-boarding, most fellow oil & gas workers are smoking — and some look like they’ve been preparing for 6 alcohol free weeks by drinking solidly for the past 6 weeks.

Plane → Chopper → Boat

With any luck without putting any HUET training to the test

It’s 40C and the humidity must be 150%.

It’s into full length coveralls, boots, and a hardhat ready for the vessel safety induction.

Years of StarTrek means understanding where to go when asked to ‘report to the bridge’.

You’re shown your muster station and which alarm means to head there — and which one means you’re better off going straight over the side.

No — that’s not a fire exit.

Questions?

No, there is no fire exit.

The communal death trap room adjoins the engine room and there’s only one entrance / exit — up the stairs.

If the stairs are burning you have no chance to survive make your time.

The bridge crew talks about the scourge of piracy, causing me to cast a sidelong glance at my DVD case, but I relax when I realize they’re talking about actual pirates…

There’s the mess, the galley, the smoking room, and the rec room.

Usually they’re the same room.

A 24 hour living nightmare

Mobilization. Building Windows boxes, installing software, and fixing bugs.

Stringing CATV and 440V mains leads around the back deck.

Telemetry from the ROV has to be fed to survey on the bridge, then back down to you in the inspection shack. The RS232 breakout box is in full effect.

Access to everything is awkward, and there’re always three people trying to occupy the same piece of space, and work with the same piece of hardware.

All this is happening on the back deck of a support vessel as it ploughs out to the field at full steam in 2m seas and 24knot winds; green water coming over the back deck.

Lesson 2

Mission critical means if the software goes down, the job stops.

Hourly cost of support vessel and crew: $20,000

Hourly cost of ROV crew & vehicle hire: $10,000

Look on the party chief’s face when you tell him the software’s crashed and will take 30mins to bring everything back up so everyone can get back to work: Priceless

They paid for a qualified inspection engineer. They got me instead.

I’m a programmer. I know this and so does my employer. But the client paid for an 3.4u Inspection Engineer and expects to get what he paid for.

This would be less of a problem if my employer didn’t insist on my doing both jobs, or if inspection shifts were less the 12 hours long, or didn’t require my full attention.

Now, rather than being the cutting-edge developer on the front lines I’m a barely adequate inspection engineer who seems more interested in the software I’m using than the job at hand.

The job runs 24/7, no weekends, no downtime.

Simulate the daily offshore experience at home!

• Put an uncomfortable plastic chair in your bedroom closet with 6 LCDs.
• Point 3 cameras at the bottom of an aquarium, each at a slightly different angle.
• Park a big-rig next to the adjoining wall (and leave it idling).
• Turn on a humidifier at least 110% relative humidity. Every 30mins alternate between a heater at 45C with an air-con at 15C.
• Invite five complete strangers into your closet.
• Have someone lock the door and don’t let anyone out for at least 12 hours.

• You need to give a running voice-over, and log the action in software.
• This is important. Have your friends rock the wardrobe by about 30 degrees side-to-side continuously. Randomly have them drop it.
• Every 2 to 4 hours have something on the camera change slightly for no longer than 3 seconds.

If you miss the change, you lose.

At 7am the sun’s up and I’ve done the handover to my replacement.

After downing a cholesterol loaded deep-fried heart attack, it’s time to head for bed.

Instead, I’m Tier 1, 2, and 3 support for the day-shift, fixing bugs, and implementing features.

Around midday it’s time time for one last 3 course meal before wedging myself in to bed to avoid plummeting from the top bunk when the boat rolls 30 degrees.

Up at 5pm, and headed to the mess for breakfast; a quick look at the menu and it’s the low GI staying power of pork spare ribs and mashed potatoes with gravy.

Breakfast of champions.

The ROV crew is on midday to midnight shifts, so there’s another full dinner put on at 11:30am and another at 12:30pm. Play your cards right and you can get 5hrs sleep a night and still squeeze in 4 full 3-course dinner meals.

Every.

Single.

Day.

Three weeks in, it’s clear this three week job is not likely to finish in the next 24 hours.

Time to craft an email to the loved ones explaining that rather than being home by the weekend, It’ll be another month.

Time passes…

At 5 weeks the homesickness has replaced seasickness.

I’m swearing like a sailor and no one has shaved since day three. It’s been a month since I’ve seen my girlfriend (or any other three dimensional woman).

I’ve fixed everyone’s computer at least once, have challenged and defeated the entire crew in a series of all-in Quake3 Arena death matches.

It’s time to go home.

On the last night, everyone works 24 hours to get the demobilization finished, those on night shift (like me) are heading for 36 hours “awake.”

Everyone is barely recognizable having washed, shaved, and put on their ‘good’ clothes for the first time in over a month.

The flight back to Perth is a blur of G&Ts.

For the first few days home I’m walking with a swagger, and when attending to nature I still lean forward and support myself against the wall to counter the non-existent swell.

The bank account certainly enjoyed the trip, and I will never in my life know any code as well as I did that software.

Was it worth it?

No.

Will I be going again..?

I was back offshore within a month and did almost a dozen stints over 4 years.

I’ve worked with people who went on one job, got choppered off after 3 weeks and quit the second they hit dry land.

Your mileage may vary.

The New York City public dataset contains all the 311 complaints made since 2010. Let’s see how the city has improved — and gotten worse — over the past 6 years.

The graph below highlights the 311 complaints, with significant call volume, with the biggest increase / decrease between 2010 and 2016.

Complaints have been outpacing population growth

Since 2010 the number of complaints have grown by 18%, while the population expanded by an estimated 6%.

There’s a lot of cars in New York, and people don’t seem to know where to put them

Illegal parking, broken parking meters, blocked driveways, and derelict vehicles represent many of the highest-percentage increases. Illegal parking is the biggest offender with more than a four-fold increase.

Noise represented 18% of all 311 complaints in 2016, up from 10% in 2010. That’s an increase of over 200,000 calls — fully 2/3rds of the total increase.

We’ve previously investigated the increase in rat sightings; the increase in complaints regarding missed trash collections and dirty sidewalks might be another clue that helps explain that jump.

Graffiti and illegal postering are way down

They show a 38% and 78% reduction respectively, in line with New York City’s ongoing efforts to eradicate both.

Street repairs are failing 76% less often, and there are far fewer complaints for broken streetlights and traffic signals — down 50% on average (likely related to the city replacing halogen bulbs with LED lights.)

The graph below is a zoomed-in snapshot for every borough

We’re graphing the percentage difference of a particular boroughs complaint growth rate on the y-axis, relative to the rest of the borough’s growth rate on the x-axis. The size of each point represents the total number of calls received in 2016.

The highlighted point representing derelict vehicles in Manhattan, shows that in the other boroughs, this complaint type increased by 105% (it doubled from 17,759 to 36,416), while the Manhattan growth rate was 62% faster (a 170% increase, from 7,569 to 20,450).

It’s a fun graph to play with (you should make a copy of the sheet and investigate it yourself), but it’s pretty hard to interpret, so let’s pull out some notable observations for each borough.

The following chart breaks the complaints into four quadrants

Those on the right represent complaints that have increased, and on the left where they’ve decreased.

The upper half represents complaint-types that are worse in one borough than the others, and the lower half, shows where they’re better.

Values less than -100% in the upper left (colored red) represent complaint types that have increased in one borough while decreasing in the other boroughs, while values under -100% (green) in the lower right indicate complaint types where calls have decreased in one borough, despite increasing elsewhere.

So here for Brooklyn, complaints about illegal building conversions, street cave-ins, and graffiti have increased, while dropping in the other boroughs. Rough, pitted, and cracked roads complaints in Brooklyn have dropped at more than twice the rate at which they increased in the other boroughs.

Street-light complaints are improving twice as quickly in Brooklyn than elsewhere, but derelict vehicles complaints are growing 62% faster than the other boroughs.

You can see the exceptional complaint types for the other boroughs in the charts below.

I’ve cherry picked the ones I thought most notable or interesting

There are thousands of individual complaint types and descriptors, so the graphs and lists I’ve used in this blog post aren’t comprehensive

The complaint types and descriptors have also changed over the years, so a comparison required some reconciliation to account for renamed, deprecated, and newly introduced complaint types.

Note also that we’re measuring the change in the number of complaints. More complaints are correlated with more problems, but it’s possible that people are just complaining more, or that awareness of the 311 service has been increasing.

Run some queries to investigate the changes in more detail

There’s a lot more New York City open data to be explored for trends and changes. Everything from Citibike and taxi rides, to motor vehicle accidents. Check it out yourself, and share your findings.

If you’re new to BigQuery remember that everyone gets 1TB at no charge every month to run queries. If you’ve never tried BigQuery before, follow these getting started instructions.

The Rise and Fall of New York City (311 complaints) — TIL with BigQuery was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Importing data for analysis with Google BigQuery

Originally published on the Google Cloud Big Data and Machine Learning blog

The NYC 311 dataset in BigQuery indicates a dramatic increase in complaints about rats, starting in 2013.

To dig deeper, I uploaded the Rodent Inspection table from the NYC Pest Control Database to BigQuery; watch the video to see how I did this, and learn how to compare the NYC 311 data with your own dataset.

Manhattanites are the most likely to call in a false rodent alarm

The NYC Department of Health sends inspectors to investigate all rodent complaints — 75% of the time, they don’t find anything.

The heatmap on the left highlights locations where false alarms — inspections where no active rat signs were found — significantly outnumber those that find active rat signs. The blooms in Downtown Manhattan and Harlem show the largest number of false alarms.

The graph on the right highlights areas where inspectors found signs of active rats more often than not.

Here Williamsburg and Bushwick are the areas with the highest concentration of inspections that found rat signs, compared to false alarms.

Does the increase in 311 rat complaints mean more rats, or more complaints?

In 2016 it’s both. But mainly it’s more rats. The graph below shows that since 2014 there’s been an increase in clean inspections (initial inspections that pass), but a larger increase in those finding active rat signs — with 2016 in particular seeing a significant bump.

In contrast, notice the spike in complaints in 2012 actually corresponded with fewer inspections that detected active rat signs that year.

The Bronx dethroned Manhattan as rat-central in 2011, but that lead is under threat

The graphs below show the change in inspections finding active rat signs per borough. On the left, we show year-on-year percentage change, and on the right the overall number.

Note that up to and including 2015, the change between any year was typically less than +/- 30%, but 2016 saw a big increase everywhere but the Bronx.

If the current rate of increase continues across New York, Brooklyn will be crowned the Rat Capital by 2019, with The Bronx falling to 4th place.

Looking at the heatmaps below from 2010 through 2016, areas of darker red indicate a concentration of inspections that found active rat signs.

You can see a gradual migration of the areas of greatest rat sign concentration out of Manhattan (West) towards The Bronx (North East), and more recently, a hotspot forming in Brooklyn around Williamsburg (South East).

If the current trend continues, I’d expect the heatmaps for 2017 onwards to have significant hotspots in Downtown Manhattan and Brooklyn, with The Bronx becoming less prominent.

To learn what’s causing the increase in rats, we need more data

For example, when we explored the effect of weather on NYC 311 complaints, we found a weak correlation between temperature and rat complaints; and 2015 and 2016 were significantly warmer than 2013 and 2014 — so perhaps that’s a factor.

What other factors might be influencing the increase in rat population? Check out the New York City Open Data collection, and import your own data into BigQuery to see if you can figure it out.

If you’re new to BigQuery follow these getting started instructions, and remember that everyone gets 1TB at no charge every month to run queries. When you’re done remember to share the results with us using #TILwBQ.

Crowning the Rat Capital of New York using BigQuery was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Using Google BigQuery to explore how weather affects NYC

Originally published on the Google Cloud Big Data and Machine Learning blog

With over 150GB of New York City public data, parsing it all for patterns and insights is a challenge. One solution? Combine it with another 30GB of weather data, and use the CORR function to find correlations for you.

Correlation doesn’t imply causation, but it can help you identify patterns worth exploring. By finding the highest correlations between weather variables and the NYC datasets, I’m going to try and answer a number of weather-related questions about the city:

• Does the temperature affect Citi Bike and taxi rides?
• Does weather affect the causes of motor vehicle accidents?
• Do wind gusts cause runaway cars?
• Which 311 complaint is most closely related to low temperatures?
• What’s the effect of snow on manhole covers and illegal parking?
• Are rats and dead trees related to warmer temperatures?
• Do noise complaints drop when wind speeds rise?
• What happens to dogs in New York in the summer?

Does the temperature affect the number of Citi Bike and taxi rides?

Inspired by Sara Robinson’s exploration of Citi Bike data, I thought I’d look at the relationship between Citi Bike rides and temperature. We can see that there’s an apparent relationship between temperature, and the rides taken on the Citi Bike network.

Here I’m comparing the number of riders each day, normalized against the average number of rides for that day-of-the-week. As the temperature rises, so does the number of rides being taken. The heatmap shows us the concentration of starting stations — as the temperature increases, more rides begin in the outer boroughs.

Let’s apply the same experiment to the NYC Yellow Cab ride data. I’m going to simplify the graph by bucketing temperatures into degree increments, and finding the average number of trips taken within each bucket.

There’s some noise at the upper and lower end, where we have fewer samples, but nothing that indicates a real relationship. But hot, cold or temperate — New Yorkers take a lot of cab rides.

Inside the NOAA “global summary of day” (GSOD) weather dataset

Before answering the rest of our questions, let’s take a quick detour to simplify things.

The GSOD dataset from NOAA, one of several public datasets available in BigQuery, includes temperature, precipitation, snow and wind from over 9,000 stations dating back to 1929. To reduce time and cost, let’s extract only the data we need.

The following query selects only the weather variables I need from the observations recorded between the 2009 to 2016 span of our NYC data, from the two NYC airports — JFK and La Guardia, calculating the average results between them for each day.

SELECT 
  -- Create a timestamp from the date components.   
  timestamp(concat(year,"-",mo,"-",da)) as timestamp, 
  -- Replace numerical null values with 0s 
  AVG(IF (temp=9999.9, 0, temp)) AS temperature, 
  AVG(IF (visib=999.9, 0, visib)) AS visibility, 
  AVG(IF (wdsp="999.9", 0, CAST(wdsp AS Float64))) AS wind_speed,
  AVG(IF (gust=999.9, 0, gust)) AS wind_gust, 
  AVG(IF (prcp=99.99, 0, prcp)) AS precipitation, 
  AVG(IF (sndp=999.9, 0, sndp)) AS snow_depth 
FROM 
  `bigquery-public-data.noaa_gsod.gsod20*` 
WHERE 
  CAST(YEAR AS INT64) > 2008 
  AND 
  (stn="725030" OR -- La Guardia 
   stn="744860")   -- JFK 
GROUP BY 
  timestamp

The total size of the new table is under 200k, so from here on, our weather joins cost basically nothing.

Does weather affect the likely causes of motor vehicle accidents?

This query uses the CORR function to calculate the Pearson Coefficient — the linear dependence (correlation) — between each weather variable (temperature, precipitation, snow, wind and visibility) and the number of motor vehicle accidents for each primary cause.

The results suggest slippery pavements cause more accidents when there’s more snow on the ground, and that there are more driverless / runaway when it rains and there are gusty winds.

By graphing snow depth and slips, we can see a possible pattern. The greatest impact appears to be on the heaviest day of snow.

My gut tells me gusty winds could cause more cars to slip away from their drivers and escape down slippery, rain-covered streets — but with only four months of data, graphing the results doesn’t really support it.

Calculating correlations for weather variables and 311 complaints

311 is a service offered by New York City (and many other municipalities) for non-emergency inquiries and non-urgent community concerns.

Let’s start by running queries to find which 311 complaint categories have the highest correlations with temperature, snow, wind speed and rain, and take a look at the top results to answer our questions.

Which 311 complaint is most closely related to low temperatures?

The strongest correlation is the least surprising: The call volume for complaints regarding heating are significantly higher when the weather is colder. Looking at the data, you can see the trend line is a good fit for an exponential relationship.

What was more surprising was seeing a similar correlation strength for missing manhole covers related to the depth of snow.

What’s the relationship between snow, missing manhole covers and illegal parking?

Looking at the scatter plot of call volume against snow depth and temperature, we can see that there does appear to be a relationship between snow depth and missing manhole covers.

If we plot our data against time, we get a clear picture that snowfall is likely a significant contributor.

There’s a pattern of spikes in both snow and call volume throughout the past 7 years. It’s unlikely to be a causal relationship (unless people are using the cover of a snowfall to steal manhole covers. Or — if the causation is in the other direction — manhole covers are the only thing between New York and a winter apocalypse.)

Graphing illegal parking mainly shows a trend of New Yorkers doing so more often. But blocked hydrants seem unrelated to snowfall, with a trend towards more annual reports and higher snowfall likely causing the false correlation.

Double parking features two spikes consistent with heavy snow in 2010 and 2016, but little correlation with snowfall in between those years.

Are rats and dead trees related to high temperatures?

The primary correlation for dead or dying trees appears to be seasonal — with call volumes growing in spring and peaking at the start of summer.

I showed this to Lak Lakshmanan, an expert on BigQuery and weather data, and he suggested that we seasonally adjust these figures against the monthly averages.

The following graph measures the difference from the monthly average temperature, against the proportion of monthly call volume. Without the seasonal effects, the relationship looks very weak.

If we seasonally adjusted rat sightings, there might be a trend of high temperatures with increased rodent sightings. The scatter point is noisy, but if we bucket our results by finding the average per degree in temperature, we get a much clearer indication of a relationship.

The bucketed graph offers a clearer signal, but by hiding the variance it makes the relationship appear stronger than it really is.

Which is why I was thrilled when Lak introduced me to the hexbin graph!

Hexbin graphs group together all the points that land within a given hex, and assigns that hex a color according to the number of points within it.

It results in a clearer pattern than the scatter plot, but retains the variation. The distinctive diagonal band from lower left to upper right, shown in both the overall shape and in the area of greatest concentration, suggests a weak correlation between heat and rat sightings.

Do noise complaints drop when there’s higher wind speeds?

Barking dogs, loud talking, car music and loud parties have a correlation coefficient of at least -0.75 with wind speed. But, we also know New York City has windier weather in the winter, so let’s seasonally adjust, and jump straight to our hexbin graph to see if there’s more to it.

There’s a triangular pattern in the lower left corner of each graph that implies a weak, negative correlation — ​ a greater likelihood that there’ll be more noise complaints when the wind is low, and significantly fewer when the wind is 5 knots above the monthly average.

The strongest pattern is for parties, which demonstrates the same trend in the overall distribution, as well as a similar pattern within the region of most concentrated results.

Except for barking dogs. What do you think is going on here?

Dig in and find other nuggets hidden within the data

I’ve shared a larger subset of possible weather/complaint correlations in this Google Sheets spreadsheet. It’s filtered to show only complaints with a Pearson Coefficient stronger than +/-0.7 for temperature, snow depth or wind speed — and +/- 0.5 for precipitation.

Once you dig in, you’ll find multiple confounding factors, including differences between boroughs, seasonality, annual growth, the relationships between variables and many others. When you’re done, make sure you share the results using #TILwBQ.

If you’re new to BigQuery follow these instructions to get started, and remember that everyone gets 1TB at no charge every month to run queries.

Four Seasons (and 5 Boroughs) in One Post with BigQuery was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.