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.

Cloud Firestore is a NoSQL document database that simplifies storing, syncing, and querying data for your mobile and web apps at global scale.

Cloud Firestore is a fast, fully managed, serverless, cloud-native NoSQL document database that simplifies storing, syncing, and querying data for your mobile, web, and IoT apps at global scale.

Its client libraries provide live synchronization and offline support, while its security features and integrations with Firebase and Google Cloud Platform (GCP) accelerate building truly serverless apps.

Here’s other stuff it’s good at:

Sync data across devices, on or offline

With Cloud Firestore, your applications can be updated in near real time when data on the back end changes. This is not only great for building collaborative multi-user mobile applications, but also means you can keep your data in sync with individual users who might want to use your app from multiple devices.

With Firebase Realtime Database, we felt we had built the best force-plate testing software on the market. Thanks to Cloud Firestore, in only two weeks, we built a system that’s significantly better and includes features we never thought possible to ship on Day 1.

Chris Wales, CTO, Hawkin Dynamics

Cloud Firestore has full offline support, so you can access and make changes to your data, and those changes will be synced to the cloud when the client comes back online. Built-in offline support leverages local cache to serve and store data, so your app remains responsive regardless of network latency or internet connectivity.

Simple and effortless

Cloud Firestore’s robust client libraries make it easy for you to update and receive new data while worrying less about establishing network connections or unforeseen race conditions. It can scale effortlessly as your app grows. Cloud Firestore allows you to run sophisticated queries against your data. This gives you more flexibility in the way you structure your data and can often mean that you have to do less filtering on the client, which keeps your network calls and data usage more efficient.

Enterprise-grade, scalable NoSQL

Cloud Firestore is a fast and fully managed NoSQL cloud database. It is built to scale and takes advantage of GCP’s powerful infrastructure, with automatic horizontal scaling in and out, in response to your application’s load. Security access controls for data are built in and enable you to handle data validation via a configuration language.

The last few years have underscored the importance of speed in bringing new drugs and medical devices to market, while ensuring safety and efficacy. Over this time, healthcare and life sciences organizations have transformed the way they research, develop, and deliver patient care by embracing agility and innovation.

Now, the industry is set to reap the benefits of cloud technology and overcome the existing barriers to innovation.

What’s holding back innovation?

Costly clinical trials: The process of trialing and developing new drugs and devices is still long and costly, with more than 1 in 5 clinical trials failing due to a lack of funding.1 The high failure rate comes as no surprise when you consider the average clinical trial costs $19 million and takes 10-15 years (through all 3 phases) to be approved.2

Stringent security requirements: Pre-clinical R&D and clinical trials use large volumes of highly sensitive patient data – making the life sciences industry one of the top sectors targeted by hackers.3 On top of this, the FDA and other regulatory bodies have strict requirements for medical device cybersecurity.

Unpredictable supply chains: Global supply chains are becoming increasingly complex and unpredictable. This can be brought on by anything from supply shortages, to geo-political events, and even bad weather. Making things worse is the lack of visibility into medical shipment disruptions – so when disaster strikes you’re often caught off guard.

Google Cloud for life sciences

At Alphabet, we’ve made significant investments in healthcare and life sciences, helping to tackle the world’s biggest healthcare problems, from chronic disease management, to precision medicine, to protein folding.

Together with Google, you can transform your life sciences organization and deliver secure, data-driven innovation across the value chain.

• Accelerate clinical trials to deliver life-saving treatments faster and at less cost. Clinical trials require relevant and equitable patient cohorts that can produce clinically valid data. Solutions like DocAI can enable optimal patient matching for clinical trials, helping organizations optimize clinical trial selection and increase time to value. How that patient data is collected is also important. Collection in a physician’s office captures a snapshot of the participant’s data at one point in time and doesn’t necessarily account for daily lifestyle variables. Fitbit, used in more than 1,500 published studies–more than any other wearable device–can enrich clinical trial endpoints with new insights from longitudinal lifestyle data, which can help improve patient retention and compliance with study protocols. We have introduced Device Connect for Fitbit, which empowers healthcare and life sciences enterprises with accelerated analytics and insights to help people live healthier lives. We are able to empower organizations to improve clinical trials in key ways:

1. Enable clinical trial managers to quickly create and launch mobile and web RWE collection mechanism for patient reported outcomes
2. Enable privacy controls with Cloud Healthcare Consent API and, as needed, remove PHI using Cloud Healthcare De-identification API
3. Ingest RWE and data into BigQuery for analysis
4. Leverage Looker to enable quick visualization and powerful analysis of a study’s progress and results

• Ensure security and privacy for a safe, coordinated, and compliant approach to digital transformation. Google Cloud offers customers a comprehensive set of services including pioneering capabilities such as BeyondCorp Enterprise for Zero Trust and VirusTotal for malicious content and software vulnerabilities; Chronicle’s security analytics and automation coupled with services such as Security Command Center to help organizations detect and protect themselves from cyber threats; as well as expertise from Google Cloud’s Cybersecurity Action Team. Google Cloud also recently acquired Mandiant, a leader in dynamic cyber defense, threat intelligence and incident response services.

• Optimize supply chains and enhance your data to prepare for the unpredictable. With a digital supply chain platform, we can empower supply chain professionals to solve problems in real time including visibility and advanced analytics, alert-based event management, collaboration between teams and partners, and AI-driven optimization and simulation.

Ready to learn more? We’ll be taking a deep dive into each of the challenges outlined above in our life sciences video series. Stay tuned.

1. National Library of Medicine
2. How much does a clinical trial cost?
3. Life Sciences Industry Becomes Latest Arena in Hackers’ Digital Warfare