CODEWITHSUNDEEP
google-bigquerygeospatial
buttermilk
buttermilk

Reputation: 375

In Bigquery, how to determine if a point is just outside a geographic region?

I have a geographic table that contains polygons that represent US coastal waters. I want to find the geopoints that fall just outside these regions, say that are just outside by 2 miles. I'm testing the DWithin function, but that is displaying points within my polygons.

with base_tbl1 as (SELECT ais.*, ST_GEOGPOINT(lon, lat) as geo_point
FROM `AIS_positions` as ais)

SELECT *
FROM (
SELECT a.*, ST_DWITHIN(US.geometry, a.geo_point, 0) as test
     FROM base_tbl1 as a,
`US_EEZ` as US

)
where test = True

Upvotes: 0

Views: 496

Answers (1)

Michael Entin
Michael Entin

Reputation: 7724

ST_DWITHIN means "distance between geographies is within X meters", so that includes both regions outside and within the polygon itself.

There are several approaches you can use, two I can think of:

  1. simply exclude the inner points:
WHERE ST_DWITHIN(US.geometry, a.geo_point, 2 * 1609) AND
      NOT ST_INTERSECTS(US.geometry, a.geo_point)
  1. buffer the US polygon by 2 miles, subtract the original polygon - this gives you approximated 2 miles buffer zone without polygons itself. Note that buffer is an expensive operation, so I would probably do it once and save the result to be used in following queries:
ST_DIFFERENCE(ST_BUFFER(geometry, 2 * 1609), geometry)

You can then test if a point falls into this buffer zone with ST_INTERSECTS check.

Upvotes: 3

Related Questions