CODEWITHSUNDEEP
sqlgoogle-bigquerygis
orcaman
orcaman

Reputation: 6561

Checking if a Coordinate is Within a Range - BigQuery GIS

I'm looking at the freely available Solar potential dataset on Google BigQuery that may be found here: https://bigquery.cloud.google.com/table/bigquery-public-data:sunroof_solar.solar_potential_by_censustract?pli=1&tab=schema

Each record on the table has the following border definitions: lat_max - maximum latitude for that region lat_min - minimum latitude for that region lng_max - maximum longitude for that region lng_min - minimum longitude for that region

Now I have a coordinate (lat/lng pair) and I would like to query to see whether or not that coordinate is within the above range. How do I do that with BQ Standard SQL?

I've seen the Geo Functions here: https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions But I'm still not sure how to write this query.

Thanks!

Upvotes: 1

Views: 2451

Answers (2)

Michael Entin
Michael Entin

Reputation: 7744

Agree with Jonathan, just checking if each of the lat/lon value is within the bounds is simplest way to achieve it (unless there are any issues around antimeridian, but most likely you can just ignore them).

If you do want to use Geography objects for that, you can construct Geography objects for these rectangles, using

ST_MakePolygon(ST_MakeLine(
  [ST_GeogPoint(lon_min, lat_min), ST_GeogPoint(lon_max, lat_min), 
   ST_GeogPoint(lon_max, lat_max), ST_GeogPoint(lon_min, lat_max), 
   ST_GeogPoint(lon_min, lat_min)]))

And then check if the point is within particular rectangle using

ST_Intersects(ST_GeogPoint(lon, lat), <polygon-above>)

But it will likely be slower and would not provide any benefit for this particular case.

Two updates:

  • it seems like the table got a new column, center_point which is already of a geography type. Now this makes more sense to use it,
  • BigQuery got new function ST_INTERSECTSBOX,

These two updates make it reasonable to use Geography functions now:

...
WHERE ST_INTERSECTSBOX(center_point, lng1, lat1, lng2, lat2)

which might be faster than filtering by four conditions.

Upvotes: 4

GIS-Jonathan
GIS-Jonathan

Reputation: 4647

Assuming the points are just latitude and longitude as numbers, why can't you just do a standard numerical comparison?

Note: The first link doesn't work without a google account, so I can't see the data. But if you want to become spatial, I'd suggest you're going to need to take the border coordinates that you have and turn them into a polygon using one of: ST_MAKEPOLYGON, ST_GEOGFROMGEOJSON, or ST_GEOGFROMTEXT. Then create a point using the coords you wish to test ST_MAKEPOINT.

Now you have two geographies you can compare them both using ST_INTERSECTION or ST_DISJOINT depending on what outcome you want. If you want to get fancy and see how far aware from the border you are (which I guess means more efficient?) you can use ST_DISTANCE.

Upvotes: 4

Related Questions