CODEWITHSUNDEEP
sqlgoogle-bigquerydistance
Jaskeil
Jaskeil

Reputation: 1232

Calculate distance in miles between two zip codes in BigQuery SQL?

Say I have two zip codes in one row in a table. Is there a way to calculate the distance between the two zip codes?

Upvotes: 1

Views: 3094

Answers (2)

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can try this:

SELECT
  zip1.zipcode zipcode1,
  zip2.zipcode zipcode2,
  ST_DISTANCE(zip1.geog, zip2.geog) distance
FROM
  `fh-bigquery.gis.zipcodes` zip1,
  `fh-bigquery.gis.zipcodes` zip2
WHERE
  zip1.zipcode != zip2.zipcode;

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below example is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.tabel` AS (
  SELECT '91356' home, '90045' work
)
SELECT home, work,
  ST_DISTANCE(home_geo, work_geo) / 1609.34 AS distance,
  home_geo, work_geo,
  ST_MAKELINE(ST_CLOSESTPOINT(home_geo, work_geo), ST_CLOSESTPOINT(work_geo, home_geo)) line
FROM (
  SELECT home, work,
    (
    SELECT ST_GEOGFROMTEXT(zipcode_geom) 
    FROM `bigquery-public-data.utility_us.zipcode_area`
    WHERE zipcode = home
    ) home_geo,
     (
    SELECT ST_GEOGFROMTEXT(zipcode_geom) 
    FROM `bigquery-public-data.utility_us.zipcode_area`
    WHERE zipcode = work
    ) work_geo
  FROM `project.dataset.tabel`
)

with output

home    work    distance            ...
91356   90045   12.794149725735204  ...

enter image description here

Above result would be visualized as below showing both zips and line representing shortest line in between them

enter image description here

Note: obviously, this is not a driving distance but rather shortest distance on the map - see more details on ST_DISTANCE

Upvotes: 3

Related Questions