As we become increasingly reliant on technology to make decisions, geospatial data is becoming more critical than ever. It is a powerful resource that can be used to solve a variety of problems, from tracking the movement of goods, identifying interest areas and potential areas of disaster.

Geospatial data incorporates data with a geographic component, such as latitude and longitude coordinates, addresses, postal codes, or place names, and can be obtained from a variety of sources, including satellites, sensors, and surveys, making it an incredibly powerful tool with a broad range of applications.

One of the key features of BigQuery, Google Cloud’s serverless enterprise data warehouse, is its ability to analyze geospatial data. However, oftentimes geospatial data sits in a variety of public clouds, not just Google Cloud. To access it effectively, you need a multi-cloud analytics solution that lets you capitalize on the distinct capabilities of each cloud platform, while extracting insights and value from data sitting across multiple cloud platforms.

BigQuery Omni is a multi-cloud analytics solution that enables the analysis of data stored across public cloud environments, including Google Cloud, Amazon Web Services (AWS) and Microsoft Azure, without the need to transfer the data. With BigQuery Omni, users can employ the same SQL queries and tools used to analyze data in Google Cloud to analyze data in other clouds, making it easier to gain insights from all data, regardless of the storage location. For businesses using multiple clouds, BigQuery Omni is an excellent tool to unify analytics and optimize the value of data.

With BigQuery Omni, organizations can analyze location-based information or geographic components, such as latitude and longitude coordinates, addresses, postal codes, or place names without even copying their data to Google Cloud. For example, you could use BigQuery Omni to analyze data from a fleet of delivery vehicles to track their location and identify potential problems.

BigQuery Omni and geospatial data analysis

If you are working with geospatial data, BigQuery Omni is a powerful tool that can help you to get insights from your data. It is scalable, reliable, and secure, making it a great option for unifying your analytics and getting the most out of your data. 

Here are a few examples of ways organizations might use BigQuery Omni for geospatial data:

• A transportation company could use BigQuery Omni to analyze data from GPS sensors in its vehicles to track the movement of its fleet and identify potential problems.
• A retail company could use BigQuery Omni to analyze data from its point-of-sale systems to track customer behavior and identify trends.
• A government agency could use BigQuery Omni to analyze data from its weather sensors to track the movement of storms and identify areas at risk of flooding.

BigQuery Omni and geospatial data can be used together to gain insights into a variety of business problems. Specifically, some of the advantages of using BigQuery Omni and geospatial data include:

• Access to quality geospatial data: BigQuery supports loading of newline-delimited GeoJSON files and provides built-in support for loading and querying geospatial data. Data from public data sources like BigQuery public datasets, the Earth Engine catalog, and the United States Geological Survey (USGS) can be easily integrated into your BigQuery environment. Earth Engine has an integrated data catalog with a comprehensive collection of analysis-ready datasets, including satellite imagery and climate data. This data can be combined with proprietary data sources such as SAP, Oracle, Esri ArcGIS Server, Carto, and QGIS.
• Loading and preprocessing of geospatial data: BigQuery has built-in support for loading and querying geospatial data types, and you can use partner solutions such as FME Spatial ETL to load data.
• Working with different geospatial data types and formats: BigQuery supports a variety of file types and formats including WKT, WKB, CSV and GeoJSON.
• Coordinate reference systems: BigQuery’s geography data type is globally consistent. That means that your data is registered to the WGS84 reference system and your analyses can span a city block or multiple continents.

Overall, geospatial analytics with BigQuery Omni provides a wide range of technical capabilities for processing and analyzing geospatial data, making it a powerful tool for businesses that need to work with location-based data.

Analyzing geospatial data with BigQuery Omni

Imagine a retailer who has a large chain of department stores with locations all over the country. They are looking to expand their business and want to identify areas with high sales potential. They want a way to get a better understanding of their sales volume within specific geographic boundaries. To achieve this goal, the retailer turns to the GIS (Geographic Information System) functions built into BigQuery. Here are the steps what the retailer takes to analyze this dataset:

Step 1 : An initial orders dataset on AWS S3 contains 5.54 million rows, and with separate locations (300 rows) and zipcode (33144 rows) metadata files on AWS S3.

Orders Parquet files:

Location and Zipcode files:

Step  2 : The retailer uses BigQuery Omni to establish a connection between the data stored in AWS and BigQuery, enabling them to access the S3 datasets externally.

External Connection for AWS

External Table for orders

External Table for locations

External Table for zipcode

Step 3 : They combine the orders and locations datasets using BigQuery Omni, and remotely aggregate the data on AWS. Joining this dataset with geospatial datasets helps them derive geospatial coordinates.

The final aggregated dataset is reduced to 23 rows. Subsequently, they bring back the result dataset, which contains just 23 rows. This helps them reduce the extraction of millions of rows to just 23 rows for their geospatial analytics.

Select sales.store_city store_city,
sales.number_of_sales_last_10_mins number_of_sales_last_10_mins,
sales.store_zip store_zip,
 ST_GeogPoint(zip_lat_lng.longitude ,
   zip_lat_lng.latitude  ) geo
FROM (
 SELECT
   FORMAT_DATETIME("%X",
     CURRENT_DATETIME("America/Los_Angeles")) current_time,
   MAX(DATETIME(time_of_sale,
       "America/Los_Angeles")) time_of_last_sale,
   COUNT(1) number_of_sales_last_10_mins,
   locations.city store_city,
   locations.zip store_zip
 FROM
   `bqomni-blog.aws_locations.orders_small`  sales
 JOIN
   `bqomni-blog.aws_locations.locations` locations
 ON
   sales.store_id = locations.id
 GROUP BY
   locations.city,
   locations.zip ) sales
JOIN
   `bqomni-blog.aws_locations.zipcode` zip_lat_lng
ON
 cast(sales.store_zip as INT) = cast(zip_lat_lng.zipcode as INT)
 WHERE ST_WITHIN( ST_GeogPoint(zip_lat_lng.longitude , zip_lat_lng.latitude  ) ,ST_GeogFromText(zip_lat_lng.zipcode_geom ) )
 AND zip_lat_lng.state_name  = "New York"
 ORDER BY number_of_sales_last_10_mins

Aggregated Sales data by region

To build richer views of their sales volume data, the retailer uses BigQuery GeoViz integration, a powerful tool that allows for visualizing geographic data on maps.BigQuery Geo Viz is a web tool for visualization of geospatial data in BigQuery using Google Maps APIs. You can run a SQL query and display the results on an interactive map

BigQuery Geo view for sales data analysis

With the geo-tagged data in place, the retailer can now see regional sales volume, sales density, distribution by department by time, and distribution within department, all powered through BigQuery.

Satellite view

Benefits of using BigQuery Omni 

Beyond geospatial analysis, BigQuery Omni offers a number of benefits, including:

Reduced costs: BigQuery Omni’s ability to eliminate data transfers between clouds can help organizations reduce costs and simplify data management, making it a valuable tool for multi-cloud analytics. Also, the ability to access and analyze data across multiple clouds can reduce the need for data replication and synchronization, which can further simplify the ETL process and improve data consistency.

Unified governance: BigQuery Omni uses the same security controls as BigQuery, which include features such as encryption, access controls, and audit logs, to help protect data from unauthorized access.

Single pane for analytics: BigQuery Omni provides a single interface for querying data across all three clouds, which can simplify the process of analyzing data and reduce the need for organizations to use multiple analytics tools.

Flexibility: Analyze data stored in any of the supported cloud storage services, giving organizations the flexibility to work with the data they have regardless of where it’s located.

BigQuery Omni is a valuable tool for geospatial analysis because it allows you to analyze data from multiple sources without having to move the data. This can save you time and money, and it can also help you to get more accurate insights from your data.If you are looking for a way to improve the accuracy, efficiency, and decision-making of your business, using BigQuery Omni to analyze geospatial data can be a powerful tool.

References

• Omni-introduction 
• Omni-benefits 
• Omni-aws-create-connection
• Omni-azure-create-connection 
• BigQuery Omni Retail Demo on Looker
• Omni-pricing 
• Working with Geospatial Data
• Geospatial Analytics – Intro

Learn more about how BigQuery Omni can help your organization.

With a data strategy and data warehouse in place, EdTechs are building a data culture that helps everyone – from educators and administrators, to employees in marketing and accounting – make more informed decisions with their data.

So how do you build a data culture in your organization? It starts by asking these questions:

• Are users able to find answers to their data questions from available data?
• Can they act based on available metrics?
• Can they tell a compelling story with the data at hand?

How well is your company using analytics? The matrix  from Looker’s Analytical Maturity eBook, shows ranges from “vanity” (nice to see) metrics to “optimization.” 

Adopting and rolling out your organization’s new data platform

As outlined in the previous blog in this series, From data chaos to data-driven: How dedicated data teams can help EdTechs influence the future of education, a comprehensive data team is essential for building a data warehouse. That data team should include a core education intelligence team. This team can align with analytically savvy members of each department they support. It’s critical that the Education Intelligence team understand each department’s reporting needs and can make adjustments based on user feedback.

Include these key components to ensure smooth adoption for internal teams:

• Share a roadmap: An effective rollout team always knows what’s next on their roadmap and communicates that to the entire organization. Launching analytics across an entire company at once usually leads to slow movement, miscommunication, and lack of adoption. A roadmap will ease this transition.
• Train everyone: This approach makes sure everyone is consistent during the data rollout. Show users how to make the most of the platform your data team has created, and be sure they know where they can go for help.
• Monitor and optimize: Monitor your organization’s analytics usage. Understand which individuals are using your education intelligence system to drive their day-to-day operations. These individuals can provide further insight into what is working. Identifying those who may need more guidance and support ensures they don’t miss out on benefits.

Guild Education: Creating a data culture

Guild Education started working with Google Cloud to build a data culture and implemented Looker as their data platform. 

The company transformed their student success program—in which coaches work directly with connected employees throughout their educational journey—with Looker. “As enrollments increased, even spreadsheets were not able to accurately keep track of caseloads,” Sean McKeever, Senior Business Intelligence Analyst, recalls. “We needed a singular source of truth that could be updated in almost real-time.” In response, they created “Student Rosters” to manage a coach’s student outreach.

Users were thrilled with the new tools, and when the coaching team grew from 20 to 90, Sean created ambassador groups consisting of data specialists to support each department. This sparked Guild Education’s “data-driven evolution.”

“Unexpectedly, the Student Success task force also became an engine for new BI work,” Sean says. “They blew my highest expectations out of the water and started owning virtually the entire process: building requirements, prototyping, testing, and deploying new dashboards and panels—with hardly any help from the BI team.”

These successes have set Guild Education on its way to becoming a fully data-driven company. Unexpectedly, the Student Success task force also became an engine for new BI work…they blew my highest expectations out of the water and started owning virtually the entire process…with hardly any help from the BI team.Sean McKeever
Senior Business Intelligence Analyst, Guild Education

Start your journey from data chaos to data-driven

We hope this three-part series has shown how data analytics has an important role to play in transforming the future of education. EdTech companies are unlocking the power of big data to serve their customers. “The education landscape is changing rapidly, and EdTech has a major role to play as institutions adapt to the massive shift in learners’ preferences and expectations,” says Jesus Trujillo Gomez, Strategic Business Executive, Education & Research, Google Cloud. 

Feeling inspired? Let’s meet your EdTech challenges together and transform your data culture. Visit Google Cloud for education technology.

Editor’s note: In Today’s guest post we hear from beauty leader L’Oréal about their approach to building a modern data platform on fully managed services: managing the ingest of diverse datasets into BigQuery with Cloud Run, and orchestrating transformations into relevant business domain representations for stakeholders across the organization. Learn more about how businesses have benefited from Cloud Run in Forrester’s report on Total Economic Impact.

L’Oréal was born out of science. For over 100 years, we have always shaped the future of beauty, and taken its eternal quest to new horizons. This has earned us our current position as the world’s uncontested beauty leader (~€ 32 B annual sales in 2021), present in 150 countries with over 85,000 employees.

Today, with the power of our game-changing science, multiplied by cutting-edge technologies, we continue our lifelong journey of shaping the future of beauty.

As a Beauty Tech company, we leverage our decades-long heritage of rich data assets to empower our decision-making with instant, sophisticated analysis.

Because we oversee global brands, which must adapt to local requirements, we need to maintain a deep understanding of what a brands’ data represents, while managing disparate legal and regulatory requirements for different countries. Our end goal is to run a safe, compliant and sustainable data warehouse as efficiently and effectively as possible.

We sync and aggregate internal and external data from a wide variety of sources across organizations and retail stores. This made the management of our data warehouse infrastructure used to be very complex and hard to manage before Google Cloud. L’Oréal’s footprint was so large that we once found it impossible to have a standardized method to handle data. Every process was vendor-specific, and the infrastructure was brittle. We went looking for a solution to our complex data infrastructure needs, and defined the following non-negotiable principles:

• No Ops: The job of a developer at L’Oréal is not to manage servers. We need an elastic infrastructure that scales on demand, so that our developers can focus on delivering customized and inclusive beauty experiences to all consumers, rather than focusing on managing servers.
• Secure: We have strict security and compliance requirements which vary by country, and we employ a zero-trust security strategy. We must keep both our own internal data and customer data safe and encrypted.
• Sustainable : Our data lives in multiple environments, including on-prem data centers and public cloud services. We must be able to securely access and analyze this data while minimizing the complexity and environmental impact of moving and duplicating data.
• End-to-end supervision: Because developers shouldn’t be managing servers, we need a “single pane of glass” dashboard to monitor and triage the system if something goes wrong.
• Easy-to-deploy: Deploying code safely should not compromise velocity. We are constantly developing innovations that push the boundaries of science and reinvent beauty rituals. We need integrated tools to make our code deployment process seamless and safe.
• Event-driven architecture: Our data is used globally by research, product, business and engineering teams with high expectations on data quality and timeliness. Many of our internal processes and analysis are based on near real-time data.
• Data products delivered “as a service”: We want to empower our employees to drive business value at record speed. To that end, we need solutions that enable us to remove the developers from the critical path of solution delivery as much as possible.
• Extract-load-transform (ELT): Our goal is to implement the pattern to load data as soon as possible into the data warehouse to take advantage of SQL transformations.

After considering multiple vendors on the market, with these principles in mind, we landed on end-to-end Google Cloud serverless and data tooling. We were already using Google Cloud for a few processes, including BigQuery, and loved the experience.

We’ve now expanded our use of Google Cloud to fully support the L’Oréal Beauty Tech Data Platform.

L’Oréal’s Beauty Tech Data Platform incorporates data from two types of sources: directly via API, which is data that adapts easily to our schema and is inserted directly into BigQuery, and bulk data from integrations, which require event-driven transformations using Eventarc mechanisms. These transformations are performed in Cloud Run and Cloud Functions (2nd gen), or directly in SQL. With Google Cloud, we can adapt very quickly.

Today, we currently have 8500 flows for ~5000 users using the native zero-trust capabilities offered by Google Cloud. Indeed, the flows come from Google Cloud and other third-party services.

BigQuery enabled us to adopt standard SQL as our universal language in our data warehouse and meet all expectations for queries and reporting. We were also able to load original data using features like federated queries, and efficiently transitioned from ETL to ELT data ingestion by handling semi-structured data with SQL. This approach of loading original data from sources into BigQuery with non-destructive transformations allows us to reprocess data for new use-cases easily, directly within BigQuery.

Our applications are hosted on multiple environments – on-premises, in Google Cloud, and in other public clouds. This made it difficult for our data engineers and analysts to natively analyze data across clouds until we started using BigQuery Omni. This capability of BigQuery allowed us to globally access and analyze data across clouds through a single pane of glass using the native BigQuery user interface itself. Without BigQuery Omni, it would’ve been impossible for our teams to natively do cross-cloud analytics. Moreover, it eliminated the need for us to move sensitive data, which is not only expensive because of local tax and subsea transport, but also incredibly risky – sometimes even forbidden – because of local regulations.

Today Google Cloud powers our Beauty Tech Data Platform, which stores 100TB of production data in BigQuery and processes 20TB of data each month. We have more than 8000 governed datasets, and 2 millions of BigQuery tables coming from multiple data sources such as Salesforce, SAP, Microsoft, and Google Ads.

For more complex transformations where custom and specific libraries are required, Cloud Workflows help us to manage the complexity very efficiently by orchestrating steps in containers through Cloud Run, Cloud Functions and even BigQuery jobs — the most used way to transform and add value to the L’Oréal data.

Additionally, by using BigQuery and Google Cloud’s serverless compute for API ingestion, bulk data loading, and post-loading transformations, we can keep the entire system in a single boundary of trust at a fraction of the cost. With ingest, queries, and transformations all being fully elastic and on-demand, we no longer have to perform capacity planning for either the compute or analytics components of the system. And of course these services’ pay-as-you-go model perfectly aligns with L’Oréal’s strategy of only paying for something when you use it.

Google Cloud fulfilled the requirements of our Beauty Tech Data Platform. And as if offering us a no-ops, secure, easy-to-deploy, custom-development free, event-based platform with end-to-end supervision wasn’t enough, Google Cloud also helped us with our sustainability efforts.

Being able to measure and understand the environmental footprint of our public cloud usage is also a key part of our sustainable tech roadmap. With Google Cloud Carbon Footprint, we can easily see the impact of our sustainable infrastructure approach and architecture principles. Our Beauty Tech platform is a strategic ambition for L’Oréal: inventing the beauty products of the future while becoming the company of the future.

Sustainable tech is an imperative and a very important step towards this ambition of creating responsible beauty for our consumers, and sustainable-by-design tech services for our employees. We all have a role to play, and by joining forces, we can have a positive impact.

Google Cloud’s data ecosystem and serverless tools are highly complementary, and made it possible to build a next-generation data analytics platform that met all our needs.

Get started using serverless and BigQuery together on Google Cloud today.

As software-defined vehicles continue to advance and the quantity of digital services grows to meet consumer demand, the data required to provide these services continue to grow as well. This makes automotive manufacturers and suppliers look for capabilities to log and analyze data, update applications, and extend commands to in-vehicle software.

The challenges the automotive sector faces can be quantified. A modern vehicle contains upwards of 70 electronic control units (ECUs), most of which are connected to one or more sensors. Not only is it now possible to exactly measure many aspects of vehicle performance, but new options become available. Using LIDAR (light detection and ranging), for example, vehicles are achieving higher levels of autonomy; this leads to a data stream from such demanding applications that may reach 25 GB per hour. For the in-vehicle processing of data, 100 million lines of software code may be present — more than a fighter jet. This in-vehicle code will have to be maintained with updates and new functionalities.

Access to the data will allow manufacturers to gain valuable insights into operational details of their vehicles. The use of this data can help to reduce costs and risks, increase ROI, support ESG initiatives, and provide valuable insights to develop innovative solutions and shorten the time to value for Electric Vehicle innovations.

Sibros’ Deep Connected Platform (DCP) makes it possible for these manufacturers to build and launch new connected vehicle use cases from production to post-sale at scale by connecting and managing all software and data throughout every life cycle stage. A key component of this platform is the Sibros Deep Logger that provides capabilities like the following:

• Full configurability of what to record, when to record it, and how fast to record it.
• High resolution timestamps of all Controller Area Network (CAN) messages.
• Dynamic application of live log configurations to receive new data points without deploying new software.

For example, properly analyzed engine data enables true predictive maintenance for the first time, which creates the option to repair or replace components before failure happens. Another example would be the evaluation of data regarding the use of certain in-car features with the goal to redesign its interior.

Two other components of the DCP are software updates and remote commands to ECUs. The DCP on Google Cloud enables seamless integration with any vehicle architecture and provides OEMs and suppliers with the platform to manage connected vehicle data at rest and in transit using a proven and secure way on a global scale.

OEMs can pull data through APIs provided by Sibros into Google Data Cloud (including BigQuery) to gain access to the rich information data sets provided by the DCP within their environment and blend this data with their first party data sets to provide value insights for their business. Some of the Connected Vehicle insights that DCP information enables are:

• Damage prevention, improved operation, or development of the next generation of engines with insights from complex analyses that could consider parameters like model, engine type, mileage, overall speed, temperature, air pressure, load, services, and more.
• The combination of electric vehicle battery usage data like charging cycles, engine performance, and battery age with contributing factors as the use of the air conditioning to determine if such factors contribute to hazardous battery conditions and for improved battery development.
• Cross-organization collaboration in R&D by the provision of information on all these metrics and more from real-world driving, like engine knock data and even tire pressure.
• Google Cloud’s unified data cloud offering provides a complete platform for building data-driven applications like those from Sibros — from simplified data ingestion, processing, and storage to powerful analytics, AI, ML, and data sharing capabilities — integrated with Google Cloud. With a diverse partner ecosystem and support for multi-cloud, open-source tools and APIs, Google Cloud provides Sibros the portability and the extensibility they need to avoid data lock-in.

“Software has an ever increasing importance in the automotive world, even more so with electric vehicles and new mobility services. Google Cloud is partnering with Sibros to bring their award winning Deep Connected Platform to deliver high frequency, low latency over-the-air software updates, data logging & diagnostics capabilities to our automotive customers, leveraging the security and scale of Google Cloud. This is revolutionizing everything from development cycles to business models and customer relationships.” — Matthias Breunig, Director, Global Automotive Solutions, Google Cloud

Through Built with BigQuery, Google Cloud is helping tech companies like Sibros build innovative applications on Google’s Data Cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs.

“Sibros is looking forward to partnering with Google Cloud, which will enable vehicle manufacturers and suppliers to reach the next level in their use of data. Sibros solutions for Deep Data Logging and Updating on the Google Data Cloud, combined with Google BigQuery, will help them to mitigate risks, reduce costs, add innovative products, and introduce value-added use cases.” — Xiaojian Huang, Chief Digital Officer, Software, Sibros

Sibros and Google Cloud are driving Connected Mobility transformation to help our customers accelerate R&D innovation, power efficient operations, and unlock software-defined vehicle use cases with a full stack connected vehicle platform. Click here to learn more about Sibros on Google Cloud.