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.

Editor’s note: Today’s post documents the solution to a problem of how to scale website infrastructure as it moves from on-prem to the cloud. It is the result of collaboration between technical teams of Les Echos Le Parisien Annonces (a division of Groupe Les Echos, subsidiary company of LVMH) and Google in the spirit of finding the best tool for the job together.

All technology starts aging from the first day you set it up, and over time becomes in need of some renewal. At Les Echos Le Parisien Annonces (a division of Groupe Les Echos, subsidiary company of LVMH), we complement the main site (“publication annonce légale”) with a number of local market sites for different regions within metropolitan France, as well as French readers in territories across the world. Each local market site offers a variety of services and content, which are served by a set of CMS-driven sites.

Historically these sites were served from dedicated on-prem infrastructure. As the number of sites grew, vertical scaling was used to increase the capacity of the machine. Over time, this approach presented challenges. First, each new site involved modifying multiple shared configurations, and modifying several monolithic parts of the architecture. Because all the sites shared parts of the serving stack, an issue that developed for one site could take down all of the sites. The reliability of the serving path was not able to reach the availability we and our customers expected. Les Echos and Google put their heads together to solve this with a new serverless deployment pattern where each new site can be quickly and independently launched.

Each site, running a conventional PHP CMS solution, is now containerized and deployed as its own Cloud Run service with an independent configuration and database. This means each site scales vertically, including down to zero at times of day when that locale is quiet. Because Cloud Run is available across GCP regions, we can deploy sites closest to the market we want to reach. To further enhance our customer experience, each of these sites is run behind Cloud Global Load Balancing with Cloud Armor and Cloud CDN, providing extra security and performance. The initial pilot of 30 sites has performed well, and the remainder of the 150 current sites are now being migrated. Adding new sites has gone from taking hours to minutes. This allows Les Echos to quickly explore new opportunities in markets that previously would have taken much longer to assess and qualify for investing in a new site.

Adopting this serverless approach has been a successful step in the modernization journey to Google Cloud, allowing us to use containers to take existing software and both re-architect and re-platform to support growth while reducing toil.

Editor’s note: Today’s post is written by Hatim M, Chief Commercial Officer at BungkusIT. The on-demand delivery service delivers packages within the hour for one million customers across Malaysia and uses Google Maps Platform to create a seamless end-to-end delivery experience for its customers.

Imagine it’s the end of a long day at work, and you’re heading home for dinner as you look forward to a restful evening. Then just as you arrive at the doorstep, your phone buzzes with a message from a family member, asking if you could grab a carton of milk from the supermarket.

With moments like these in mind, we founded BungkusIT to alleviate the stress of everyday chores and give people a break from having to run seemingly mundane errands that can often be time-consuming. With so much to do and so few hours in a day, we want to make life simpler by providing our customers with an on-demand delivery service.

As a social enterprise, our mission is to create jobs and increase earning potential for BungkusIT roadies (those who run deliveries), while also increasing online visibility for  local small and medium enterprises through an e-commerce platform. As such, we don’t charge merchants a fee for being featured on our app. 

Our roadies help connect users with products and services that they need. Whether it’s picking up the keys you forgot, or buying a birthday cake for a loved one, our on-demand roadies can help accomplish your task quickly and efficiently. In fact, we’ve helped many seniors—a large and often underserved population—to run errands and provide last mile support.

Providing reliable service, 24/7

From the day we launched, our promise has always been to complete every task given to us within one hour. Google Maps Platform empowers us to fulfil this promise. We understand the importance of selecting a reliable mapping service from the get-go. After exploring different map products, we made the decision to go with Google Maps Platform due to its detailed and accurate location mapping system. With the support of our partner Searce, we’ve been able to quickly integrate all the relevant products on our platform and optimize our API calls.

On a day-to-day basis, our roadies rely on the Places API to navigate their journeys. It pinpoints the exact location set by customers so that our roadies can be sure they arrive at the correct destination. With so many small roads and alleys in Malaysia, it can be challenging to find the exact place based on an address. Dropping a pin on a specific location removes any confusion.

Because we price our services based on distance, it’s business critical to determine the exact distance of a location and the most efficient route to reach it. By automating that process with the Distance Matrix API, we can guarantee that customers don’t get overcharged, while making sure that we’re adequately compensated for our services.

Improving the user experience

At the end of the day, we want to make people’s lives easier. That’s why having an app that is intuitive is key. More than that, we want customers to be able to create a request as quickly as possible. By automatically completing the location a customer is typing after they’ve keyed in the first few characters, Place Autocomplete does that job for us. 

As a fairly young company, we’ve got a long way to go. I believe that we’ll continue evolving alongside Google Maps Platform and exploring the new features it has to offer. We’re already talking to global brands to expand our services and I’m confident that with Google Maps Platform as our maps partner, we will go a long way.

For more information on Google Maps Platform, visit our website.

What do CIOs and CTOs deliver for the company? If you said “technology,” that’s just the beginning. According to their research, McKinsey found that 85% of CIOs and CTOs interviewed in the spring of 2019 said they were essential for at least two of the three most common CEO priorities—revenue acceleration, improved agility and time to market, and cost reduction.

IT modernization – including migrating to the cloud – is key to business growth and agility. Yet, according to a recent McKinsey study, 80% of CIOs report that regardless of their level of cloud migration, they still haven’t reached their projected agility and business benefits. Sometimes, this is because of issues like training and skills gaps in the IT workforce. Surprisingly often though, the barrier to reaching the goals is based on trade-offs that CIOs themselves feel they must make to strike a balance between the perfect and the possible.

But what if you could have it all without the trade-offs? As Will Grannis, Managing Director of the CTO Office at Google, and Arul Elumalai, Partner at McKinsey & Company discussed in our recent digital conference, many of the compromises CIOs make can be avoided with new technology, modern architectures and by encouraging a transformation mindset across the business. In interviews, CIOs explained how they’ve leveraged the best of the cloud without compromising on security, agility, and flexibility. Here’s how these leaders avoid three of the top perceived trade-offs—both with technology and by transforming their operating model.

Trade-off #1: Developer agility vs. control and governance

Moving to the cloud offers new opportunities for speed, but 69% of organizations indicate that stringent security guidelines and code review processes can slow developers significantly. One CISO of a multinational company mentioned that cloud development was so fast that they had to institute manual checks on their developers’ code. So much for agility. 

To overcome this trade-off and maintain both speed and security, some respondents found success in DevOps, hiring security-experienced talent and introducing automation for security and quality. Building in security into the CI/CD pipeline and increasing automation don’t just eliminate the tradeoff, they result in higher quality and faster innovation.

At Google Cloud, we’ve also observed that customers with strong DevOps practices have increased speed-to-market and product/service quality. From our own journey, we’ve learned seven critical lessons essential to adopting a DevOps model, ranging from taking up small projects and embracing open source to building an overall DevOps culture.

Trade-off #2: Single-vendor benefits vs. freedom from lock-in

CIOs perceive benefits to using the fewest number of clouds, specifically avoiding introducing multiple systems that require their teams to develop and maintain multiple skillsets. Unfortunately, 83% of the CIOs interviewed said that while they would prefer fewer clouds, the potential financial and technical lock-in drives them to multiple providers. 

Successful CIOs said that they can avoid lock-in pitfalls not just with contractual guardrails and executive and board education, but with evolving hybrid cloud technologies that provide additional choices. Hybrid cloud platforms based on containers can further mitigate the risk of using a single cloud vendor. The key to successful hybrid architectures is the infrastructure abstraction and portability that containers create for them, enabling disparate environments to work together. 

This notion has been at the heart of our strategy at Google Cloud with Anthos, which provides an abstraction layer and an application modernization platform for hybrid and multi-cloud environments. Enterprises can use Anthos to modernize how they develop, secure, and operate hybrid-cloud environments and enable consistency across cloud environments.

Trade-off #3: Best-of-breed tools vs. standardization and familiarity

Optimizing tool chains for different environments can improve productivity, but many CIOs believe that this means reduced functionality and tools. While 77% of CIOs said they had to standardize to the lowest common denominator, some have found a better solution. Rather than giving up the languages, libraries, and frameworks that their teams prefer, effective leaders said that they found success by investing in training programs to upscale talent and adopting new open and vendor-agnostic solutions. Architectures that are based on open-source components have been the keys that helped remove this tradeoff, and eliminate the notion of a lowest common denominator. 

This is why we have built Anthos on open-source components like Kubernetes, Istio and Knative. Anthos gives your business the choice you need. With the ability to create code that works in most environments using the tools, languages, and systems you prefer, you can do more without major changes to how you work.

Regardless of your current cloud adoption level, check out “Unlock business acceleration in a hybrid cloud world” to discover more about McKinsey’s findings, including how CIOs drive agility, methods to make trade-offs unnecessary, and how to prepare your team for the cloud. Then, stay tuned for subsequent posts that  take a closer look at how hybrid solutions and strategies can help CIOs drive a transformation mindset across the business—without compromising on security, agility, and flexibility.

Do you need to develop, run and secure applications across your hybrid and multicloud environments? Look no further than Anthos, our managed application platform that extends Google Cloud services and engineering practices to your environments so you can modernize apps faster and establish operational consistency across them. 

To help you get started, we created the Anthos 101 video learning series. It’s a great starting point for understanding the basics of Anthos—and you can watch the whole series in less than an hour.

Let’s dive in.

1. What is Anthos?

Discover what Anthos is and how it helps enterprises manage their applications. You’ll learn about the different tools Anthos offers—like the ability to create environs and platform administrators—to help you modernize and manage your application infrastructure.

https://youtube.com/watch?v=Qtwt7QcW4J8%3Fenablejsapi%3D1%26

2. How to get started with Anthos on Google Cloud

Ready to get started with Anthos? In this lesson, you’ll create your own Anthos deployment. You’ll learn about the different tools on the Anthos dashboard—like the Service Mesh card and Cluster Status cards—plus how to deploy and alter Google Kubernetes Engine (GKE) clusters and Anthos Service mesh via Google Compute Engine.

https://youtube.com/watch?v=ghFiaz7juoA%3Fenablejsapi%3D1%26

3. How to modernize and run Windows apps in Anthos

Running a Windows application that’s in need of modernization? In this lesson, you’ll discover how you can create and deploy a Windows-based application on Anthos, allowing you to modernize existing workloads and manage your application seamlessly. You’ll even learn to do this without requiring access to source code, re-writing, or re-architecting your existing application.

https://youtube.com/watch?v=w6tzIjZhTIk%3Fenablejsapi%3D1%26

4. How to build modern CI/CD with Anthos

Continuous integration? Continuous delivery? These are two things that developers need to think about with container adoption for hybrid or multicloud environments. Learn how Anthos helps you increase your development velocity without compromising the security of your application.

https://youtube.com/watch?v=ayRz5NmM6pI%3Fenablejsapi%3D1%26

5. How to adopt a multi-cluster strategy for your applications in Anthos

There are a number of use cases that might require a multi-cluster strategy, such as maintaining multiple clusters on the cloud and in your own data center. In this lesson, learn the different tools that Anthos offers—such as GKE, Anthos Config Management, and Anthos Service Mesh—to help deploy and manage multiple clusters.

https://youtube.com/watch?v=ZhF-rTXq-Us%3Fenablejsapi%3D1%26

6. How to improve observability using golden signals in Anthos

Observability is important in application development, but without the right tools monitoring your services can be time consuming. In this episode, learn more how Anthos Service Mesh can help you monitor and manage the four Golden Signals—latency, traffic, errors, and saturation—for your application.

https://youtube.com/watch?v=EDcy3KwV22o%3Fenablejsapi%3D1%26

7. How to modernize legacy Java apps with Anthos

Looking to modernize legacy Java applications? In this lesson, you’ll learn the three categories of Java applications and their unique paths for modernization via Anthos. This can help you reduce your dependency on high-cost proprietary software, decrease operational overhead, and increase software delivery speed.

https://youtube.com/watch?v=hQWcx9iyF7E%3Fenablejsapi%3D1%26

8. How to apply a zero trust model for your deployments using Anthos

It’s time to rethink traditional security models when it comes to network observability and consistency for IAM permissions. In this lesson, learn how you can adopt a zero trust posture with Anthos. This allows you to better secure your network, detect underlying network compromises, and ensure workloads are secure before deployment.

https://youtube.com/watch?v=_qG2vazlozY%3Fenablejsapi%3D1%26

9. How to go beyond business continuity with Anthos

Sometimes a business continuity plan that only covers traditional backup and disaster recovery methods simply isn’t enough. In this lesson, learn how Anthos helps resolve issues like data redundancy, scaling without code changes, implementing measurable SLOs, and much more. You’ll also discover how Anthos can help you manage your application beyond the confines of traditional backup and disaster recovery approaches.

https://youtube.com/watch?v=kUxqdjbgcXs%3Fenablejsapi%3D1%26

10. How to simplify identity with Anthos

Managing identities across hybrid and multicloud environments can be troublesome and hard to keep track of. Luckily, Anthos is capable of simplifying identity management for users and workloads. In this lesson, you’ll learn how Anthos can extend and enable existing capabilities, while allowing you to manage IAM permissions across multiple Anthos and GKE environments.

https://youtube.com/watch?v=6P-4ZEwZqZQ%3Fenablejsapi%3D1%26

11. How to optimize costs with Anthos

Learn how you can optimize costs with Anthos through greater observability, improving existing operations, and many other practices.

https://youtube.com/watch?v=8mGICSTRoYw%3Fenablejsapi%3D1%26

Keep learning

This is just a starting point for learning about Anthos. To deepen your knowledge, check out our free on-demand training: Getting started with Anthos. Or, you can download our Anthos Under the Hood ebook, or get hands-on right now with the Anthos sandbox.