CODEWITHSUNDEEP
google-bigquerygeolocationgeospatial
Afroditi Atmatsidou
Afroditi Atmatsidou

Reputation: 39

Linking latitude and longitude data to zipcodes in Big Query

I have the latitude and the longitude of some locations and I was wondering whether there is a way in BQ to link them to zipcodes. If it makes any difference, I am interested in Europe, and I need the zipcodes for Netherlands and Spain.

Upvotes: 0

Views: 2589

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

If you have a database with the geometry of each zipcode:

WITH data AS (
  SELECT ST_GEOGPOINT(-122.4194, 37.7749) point
  UNION ALL 
  SELECT ST_GEOGPOINT(-74.0060, 40.7128) point
)
SELECT zip_code, city, county
FROM `bigquery-public-data.geo_us_boundaries.zip_codes` 
JOIN data
ON ST_WITHIN(point, zip_code_geom)

enter image description here

I used US data for this example - do you have a database of European zipcodes?

Upvotes: 4

Related Questions