1

I need to execute a BigQuery database query whereby I'm trying to ascertain if a given lat, lon coordinate is within one of a few US counties.

One of those counties is Collin County in Texas, shown here in Google Maps:

Collin County, TX, USA

BigQuery supports GIS functions I'd need like ST_CONTAINS, but I can't figure out how to get, say, a GeoJSON representation of that polygon Google Maps is showing from either that service or OpenStreetMap.

The closest I've gotten with OpenStreetMap + Overpass is the following query:

[out:json][timeout:180];
relation(1837688);
out geom;

But the polygon I try to create from that response using members doesn't line up with what OpenStreetView has https://www.openstreetmap.org/relation/1837688.

OpenStreetMap's Collin County

My Collin County

Is there a more general solution I can use to create these bounding polygons for a region?

2

BigQuery has a few public datasets, including US counties, that you can directly use.

SELECT geo_id, state_fips_code, county_name, county_geom 
FROM `bigquery-public-data.geo_us_boundaries.counties` 
WHERE county_name LIKE '%Collin%'

This returns two counties, the one with geo_id = 48085 seems what you need. Here it is in BigQuery GeoViz:

enter image description here

1
  • 🤯 that's amazing! Thank you very much! – Mike Sukmanowsky Jan 9 '20 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.