emireadcode

select

state_name,
st_centroid(state_geom) as geom
from
`bigquery-public-data.geo_us_boundaries.states`

emireadcode

select
state_name,

# first, we create the centroid for each state
# then using the buffer function, we create
# a 50 mile buffer around each centroid

# https://docs.carto.com/spatial-extension-bq/sql-reference/transformations/

bqcarto.transformations.st_buffer(

emireadcode

select

state_name,
st_x(st_centroid(state_geom)) as lng,
st_y(st_centroid(state_geom)) as lat

# we can use two functions together
# st_centroid returns a geography
# which we can pass to st_x and st_y which return the lat/longs of the point

emireadcode

select
  state_name
from
  `bigquery-public-data.geo_us_boundaries.states`
where
  st_touches(state_geom,
    (
    select
      state_geom
    from

emireadcode

# applicable to PostGIS
# transforms any geometry to any other EPSG projection
# you can add any to your database using this site
# http://epsg.io/

select 
st_transform(geom, 3857) as geom
from
states

emireadcode

create table `ADD_YOUR_PROJECT.ADD_YOUR_DATASET.airports` as

select
  a.*,
  b.geom as polygon,
  c.country as country_name
from
  `bigquery-public-data.geo_whos_on_first.spr` a
join
  `bigquery-public-data.geo_whos_on_first.geojson` b

emireadcode

SELECT
  DISTINCT name,
  latitude
FROM
  `cartodb-gcp-solutions-eng-team.matt.airports`
WHERE
  country = 'CA'
ORDER BY
  latitude ASC,
  name DESC

emireadcode

SELECT
 name,
 st_y(geom)
FROM
  `YOUR_PROJECT.YOUR_DATASET.airports`
WHERE
  country = 'CA'
ORDER BY
  st_y(geom) ASC,
  name ASC

emireadcode

SELECT
 DISTINCT name,
 st_y(geom)
FROM
  `YOUR_PROJECT.YOUR_DATASET.airports`
WHERE
  country = 'CA'
ORDER BY
  st_y(geom) DESC,
  name DESC

emireadcode

SELECT
  *
FROM
  `bigquery-public-data.geo_us_boundaries.states`
WHERE
  state_name BETWEEN 'Alaska'
  AND 'Kentucky'
ORDER BY
  state_name ASC