emireadcode

select
state_geom, state_name, state
from
`bigquery-public-data.geo_us_boundaries.states`
limit 10 offset 10

emireadcode

select
state_geom, state_name, state
from
`bigquery-public-data.geo_us_boundaries.states`

# this is a comment - it won't impact your code and you can delete it if you want

# use the where clause to select rows based on a condition
# in this case an exact match to where the state_name column equals Wisconsin

emireadcode

select
state_geom, state_name, state
from
`bigquery-public-data.geo_us_boundaries.states`

# this time we will use the LEFT() function to find states where the first letter = M
# this function takes a string and a number as arguments
# in this case it takes the state_name column and 1 
# meaning we get the first leftmost letter returned from every column in the dataset

emireadcode

select
state_name, state
from
`bigquery-public-data.geo_us_boundaries.states`

# using and we can query on two parameters

where st_area(state_geom) >= 2e+11
and left(state_name, 1) = 'M'

emireadcode

select

state_name,
st_perimiter(state_geom) * 1609 as border_miles

from
`bigquery-public-data.geo_us_boundaries.states`

order by st_perimiter(state_geom) desc

emireadcode

select
state_name,

# left will return the first letter of the text in the state_name column

left(state_name, 1) as first_letter

from
`bigquery-public-data.geo_us_boundaries.states`

emireadcode

SELECT  

# st_asgeojson() turns a geometry into geojson

# https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_asgeojson

st_asgeojson(zip_code_geom) as zip_geojson
FROM `bigquery-public-data.geo_us_boundaries.zip_codes` 
limit 10

emireadcode

SELECT  

# the CAST() function allows you to change data types

zip_code, cast(zip_code as int64) as zip_numeric
FROM `bigquery-public-data.geo_us_boundaries.zip_codes` 
order by zip_code asc

emireadcode

select

state_name,
st_area(state_geom) / 2.59e+6 as square_miles

from
`bigquery-public-data.geo_us_boundaries.states`

# we can order by any value in our table, or by a return value from a function
# we don't need to change it to square miles

emireadcode

select

# this will create a new column using a function 
# this won't be a part of the dataset, just the query itself
# we can calculate the area of the geometry in sq. meters
# then transform that using math to sq. miles
# we can also use a column alias here - as square_miles
# you can call this column anything you want, just change "square_miles"

st_area(state_geom) / 2.59e+6 as square_miles