1 of 59

Getting started with Geospatial Analysis

Geo for Good User Summit 2018

Karin Tuxen-Bettman

#GeoForGood18

2 of 59

Goal & Agenda

In this session, you’ll learn about some new tools, some cool functions, and get your hands dirty just enough to be dangerous!

​

  • Quick introduction to geospatial analysis at Google

​

  • BigQuery GIS

​

  • Earth Engine

​

  • A little hands-on

​

​

#GeoForGood18

3 of 59

Disclaimers

​

  • These are new tools, and I just recently starting using them.

​

  • I’m a non-programmer, and this session is aimed at non-programmers.

​

  • My background is in GIS & Remote Sensing, not coding.

​

  • This is the first time I’ve taught this course, so please give feedback! :-)

​

​

#GeoForGood18

4 of 59

Visualization tools vs. Analysis tools

great for visualization

great for analysis

#GeoForGood18

5 of 59

Some geospatial analysis functions

  • Overlay functions (e.g. Intersect)
  • Proximity functions (e.g. Buffer, Distance to features)
  • Geostatistical functions (e.g. Interpolation between features)

#GeoForGood18

6 of 59

What is BigQuery?

​

Convenience of�standard SQL

Fully managed�and serverless

Google Cloud Platform’s enterprise data warehouse for analytics

Encrypted, durable

and highly available

Petabyte-scale storage�and queries

Real-time analytics on �streaming data

#GeoForGood18

7 of 59

What is BigQuery GIS?

​

New geography�data type

New geospatial functions

New mapping visualization

01

02

03

#GeoForGood18

8 of 59

Point

​

​

​

Linestring

​

​

​

Polygon

​

​

​

Multi-polygon

​

​

​

Collections

Data type:

Formats:

WKT

GeoJSON� WKB

​

​

#GeoForGood18

9 of 59

Native SQL support in BigQuery

for the most commonly used �ST_* functions

Function types:

Constructors

Parsers

Formatters

Transformations

Predicates

Accessors

Measures

Aggregates

#GeoForGood18

10 of 59

Constructors

ST_GEOGPOINT(longitude, latitude)�ST_MAKELINE(geography_1, geography_2)�ST_MAKELINE(array_of_geography)�ST_MAKEPOLYGON(geography_expression)�ST_MAKEPOLYGON(geography_expression, array_of_geography)�ST_MAKEPOLYGONORIENTED(array_of_geography)

Build geographies from �coordinates or existing geographies

#GeoForGood18

11 of 59

Parsers & formatters

ST_GEOGFROMGEOJSON(geojson_string)�ST_GEOGFROMTEXT(wkt_string)�ST_GEOGFROMWKB(wkb_bytes)

�ST_ASGEOJSON(geography_expression)�ST_ASTEXT(geography_expression)�ST_ASBINARY(geography_expression)

Create/export geographies �between formats

((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),

((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)),

((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1))

#GeoForGood18

12 of 59

Transformations

ST_INTERSECTION(geography_1, geography_2)�ST_UNION(geography_1, geography_2) �ST_UNION(array_of_geography)�ST_UNION_AGG(geography)�ST_DIFFERENCE(geography_1, geography_2)�ST_CENTROID(geography_expression)�ST_CLOSESTPOINT(geography_1, geography_2[,spheroid=FALSE])�ST_BOUNDARY(geography_expression)�ST_SNAPTOGRID(geography_expression, grid_size)

Create new geographies �with similar properties

#GeoForGood18

13 of 59

Predicates

ST_CONTAINS(geography_1, geography_2) �ST_COVEREDBY(geography_1, geography_2)�ST_COVERS(geography_1, geography_2)�ST_DISJOINT(geography_1, geography_2)�ST_DWITHIN(geography_1, geography_2, distance[, spheroid=FALSE])�ST_EQUALS(geography_1, geography_2)�ST_INTERSECTS(geography_1, geography_2)�ST_INTERSECTSBOX(geography, lng1, lat1, lng2, lat2)�ST_TOUCHES(geography_1, geography_2)�ST_WITHIN(geography_1, geography_2)

Filter geographies �(TRUE/FALSE)

TRUE

​

​

​

1

​

​

​

2

​

​

​

#GeoForGood18

14 of 59

Accessors

ST_ISEMPTY(geography_expression)�ST_ISCOLLECTION(geography_expression)�ST_DIMENSION(geography_expression)�ST_NUMPOINTS(geography_expression)

Access properties �of geographies

18 (vertices)

​

​

​

#GeoForGood18

15 of 59

Measures

ST_DISTANCE(geography_1, geography_2[, spheroid=FALSE])�ST_LENGTH(geography_expression[, spheroid=FALSE])�ST_PERIMETER(geography_expression[, spheroid=FALSE])�ST_AREA(geography_expression[, spheroid=FALSE])�ST_MAXDISTANCE(geography_1, geography_2[, spheroid=FALSE])

​

Compute measurements �of geographies

3967 (meters)

​

​

​

#GeoForGood18

16 of 59

Joins

JOIN on ST_INTERSECTS�JOIN on ST_DWITHIN�JOIN on ST_CONTAINS�JOIN on ST_WITHIN�JOIN on ST_COVERS�JOIN on ST_COVEREDBY�JOIN on ST_EQUALS

​

JOIN on geography �Predicates statements

#GeoForGood18

17 of 59

Regular, Non-GIS, Query First

#standardSQL� SELECT� id,� name,� longitude,

latitude� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`

#GeoForGood18

18 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�

#GeoForGood18

19 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters�

#GeoForGood18

20 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`, params�

#GeoForGood18

21 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`, params� WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), params.center, params.maxdist_km*1000)�)�

#GeoForGood18

22 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`, params� WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), params.center, params.maxdist_km*1000)�)�SELECT * from distance_from_center

#GeoForGood18

23 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`, params� WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), params.center, params.maxdist_km*1000)�)�SELECT * from distance_from_center

#GeoForGood18

24 of 59

173 results

Download as CSV

Save Query

#GeoForGood18

25 of 59

BigQuery Geo Viz

WHAT IT IS

  • Visualize small result sets. The tool can only display up to 2000 results on the map.
  • Supports WKT point/line/polygon input. This tool works with geometry (points, lines, and polygons) in WKT format.
  • Real-time, interactive analysis in your browser. Visualization happens locally — in your browser.

WHAT IT IS NOT

  • Not a full GIS Visualization Tool. We are not trying to create a full featured geo visualization tool. This is a lightweight way to get the results of a query on a map, one query at a time.

#GeoForGood18

26 of 59

Click Run. 173 results.

#GeoForGood18

27 of 59

Select the

Geography column.

#GeoForGood18

28 of 59

Hands-on w/ BigQuery GIS

  • Run a geospatial query in BigQuery
  • Run a geospatial query in BigQuery Geo Viz, and style the map
  • Import shapefile, and repeat

#GeoForGood18

29 of 59

Data we’ll be playing with

​

  • Real-time air quality, from OpenAQ, provided through the BigQuery Public Data Program.

​

  • Zip codes, from US Census Bureau, provided through the Cloud Demos account.

#GeoForGood18

30 of 59

Go to BigQuery & Log in

User name:

​

Password:

guest100@earthoutreach.org

​

Ludwigia2662

Open incognito Chrome window, and

go to http://bigquery.cloud.google.com

and https://bigquerygeoviz.appspot.com

#GeoForGood18

31 of 59

Weather stations within 50 km of a (lat, long) point

#standardSQL�WITH params AS (� SELECT ST_GeogPoint(-122.021,37.406) AS center, 50 AS maxdist_km ),�distance_from_center AS (� SELECT� id,� name,� ST_GeogPoint(longitude, latitude) AS loc,� ST_Distance(ST_GeogPoint(longitude, latitude), params.center) AS dist_meters� FROM� `bigquery-public-data.ghcn_d.ghcnd_stations`, params� WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), params.center, params.maxdist_km*1000)�)�SELECT * from distance_from_center

#GeoForGood18

32 of 59

173 results

Download as CSV

Save Query

#GeoForGood18

33 of 59

Add the

g4g-bigquery

Project ID here.

#GeoForGood18

34 of 59

Click Run. 173 results.

#GeoForGood18

35 of 59

Select the

Geography column.

#GeoForGood18

36 of 59

Current air quality within 100 km of a specific zip code

#standardsql

WITH params AS (

SELECT 94043 AS zipcode,

100 AS maxdist_km

),

zipcode AS (

SELECT ST_GeogFromText(WKT) AS polygon

FROM `cloud-training-demos.demos.zipcode_polygon2017`, params

WHERE ZCTA5CE10 = params.zipcode

),

airquality AS (

SELECT

location, city, country, pollutant, value, unit, timestamp, averaged_over_in_hours, source_name,

ST_GeogPoint(longitude, latitude) AS loc,

ST_Distance(ST_GeogPoint(longitude, latitude), zipcode.polygon) AS dist_meters

FROM

`bigquery-public-data.openaq.global_air_quality`,

params,

zipcode

WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), zipcode.polygon, params.maxdist_km*1000) AND pollutant = "pm25"

)

SELECT * from airquality

ORDER BY dist_meters ASC

LIMIT 1000

​

​

#GeoForGood18

37 of 59

#GeoForGood18

38 of 59

#GeoForGood18

39 of 59

Add style to make the stations with higher PM 2.5 larger...

#GeoForGood18

40 of 59

...and opacity so that air quality stations closer are darker than those farther away.

#GeoForGood18

41 of 59

Next Steps for BigQuery GIS

1

2

Find more BigQuery Public Data here.

3

Find all air quality stations within Mongolia.

4

Visualize the data in BigQuery Geo Viz.

5

Follow along on this article on how to use BigQuery GIS to interpolate values.

#GeoForGood18

42 of 59

medium.com/google-cloud/how-to-load-geographic-data-like-zipcode-boundaries-into-bigquery-25e4be4391c8

#GeoForGood18

43 of 59

Visualize your SQL query

in BigQuery Geo Viz

Easy mapping

No export or loading necessary

Style the viz �interactively

Copy and run�the query

Analyze in �BigQuery

#GeoForGood18

44 of 59

Visualize your findings �in Google Earth Engine

Load data into �Earth Engine

Export data �to GCS

Analyze in �BigQuery

CSV + GeoJSON CSV + WKT

SHP → EE UI

​

#GeoForGood18

45 of 59

Basic geospatial functions in Earth Engine

Buffer

Computed Area Filter

Distance

code.earthengine.google.com/dc36949f83d64443b7e513df2829a0a4

code.earthengine.google.com/0474f4f20412977b8c81e3001c356951

code.earthengine.google.com/ab510198f4a648892122db95ca3c0d5e

#GeoForGood18

46 of 59

#GeoForGood18

47 of 59

#GeoForGood18

48 of 59

#GeoForGood18

49 of 59

Interpolate (inverse distance) PM 2.5 points

// Load a Fusion Table corresponding to mean annual PM2.5 concentrations at points.

var airQualityMeasurements = ee.FeatureCollection('ft:14BLob4jGA6au2MB1cx0GhxYDvZc-lVLAWhqBAZuN');

Map.addLayer(airQualityMeasurements, {}, 'Mean annual PM2.5 concentrations (micrograms/m^3)');

​

// This is the name of the property to interpolate.

var propertyToInterpolate = 'ArithmeticMean';

​

// Combine mean and SD reducers for efficiency.

var combinedReducer = ee.Reducer.mean().combine({

reducer2: ee.Reducer.stdDev(),

sharedInputs: true

});

​

// Estimate global mean and standard deviation (SD) from the points.

var stats = airQualityMeasurements.reduceColumns({

reducer: combinedReducer,

selectors: [propertyToInterpolate]

});

​

// Do the interpolation, valid to 50 kilometers.

var interpolatedPM25 = airQualityMeasurements.inverseDistance({

range: 50 * 1000,

propertyName: propertyToInterpolate,

mean: stats.get('mean'),

stdDev: stats.get('stdDev'),

gamma: 0.5

});

​

// Visualize the resulting interpolated raster.

var vis = {min: 0, max: 15, palette: ['blue', 'green', 'red']};

Map.setCenter(-121.7944, 36.9235, 7);

Map.addLayer(interpolatedPM25, vis, 'Interpolated PM2.5 concentration');

​

#GeoForGood18

50 of 59

#GeoForGood18

51 of 59

Earth Engine Data Catalog

Vector Datasets Catalog

#GeoForGood18

52 of 59

Upload your own data, too!

#GeoForGood18

53 of 59

Next Steps for Earth Engine

1

2

Find more Earth Engine Public Data in the Earth Engine Data Catalog

and the Vector Datasets Catalog.

3

Upload your own shapefiles to Earth Engine.

4

Take an Earth Engine 101 session here at Geo for Good 2018!

#GeoForGood18

54 of 59

Thank you!

Geo for Good User Summit 2018

karintuxen@google.com

#GeoForGood18

55 of 59

Geometry vs. Geography

Geometry

Shortest distance between 2 points�is a straight line

Geography

Shortest distance�is a great circle.

Wait, what??!!

Geography from space

Euclid was right

#GeoForGood18

56 of 59

Try the new UI

click on

#GeoForGood18

57 of 59

Try the new UI

#GeoForGood18

58 of 59

Accessing the BigQuery Public Data

Enter the following URL to open the public datasets in the new BigQuery UI: https://console.cloud.google.com/marketplace/partners/bigquery-public-data.

Once you have the public dataset project open, you can pin the project.

​

#GeoForGood18

59 of 59

Other test usernames & passwords

guest100@earthoutreach.org: Ludwigia2662

guest101@earthoutreach.org: Lupinus5153

guest102@earthoutreach.org: Luzula6823

guest103@earthoutreach.org: Lycium5845

guest104@earthoutreach.org: Lythrum3590

guest105@earthoutreach.org: Madia3550

guest106@earthoutreach.org: Malva3596

guest107@earthoutreach.org: Marah3499

guest108@earthoutreach.org: Medicago7867

guest109@earthoutreach.org: Melica3863

guest110@earthoutreach.org: Menodora4202

guest111@earthoutreach.org: Mentha5660

guest112@earthoutreach.org: Micropus8175

guest113@earthoutreach.org: Mimulus7110

guest114@earthoutreach.org: Montia6419

guest115@earthoutreach.org: Myosotis3552

guest116@earthoutreach.org: Myosurus3514

guest117@earthoutreach.org: Najas4339

guest118@earthoutreach.org: Nolina9727

guest119@earthoutreach.org: Opuntia5504

guest120@earthoutreach.org: Orcuttia7783

guest121@earthoutreach.org: Oxalis1112

guest122@earthoutreach.org: Packera8063

guest123@earthoutreach.org: Panicum9944

guest124@earthoutreach.org: Papaver4038

guest125@earthoutreach.org: Paspalum1803

#GeoForGood18