CODEWITHSUNDEEP
sqlgoogle-bigquerygis
Craig
Craig

Reputation: 843

Matching a point to a polygon in SQL/BigQuery so that the point is at least N meters within the polygon borders (i.e. not near the border)

I'm trying to match points to polygons in BigQuery and want to match on 1) point is within polygon and 2) point is at least 2 meters within the border of the polygon. The goal is to exclude points (which have some error in them) that are very close to the edge of the polygon.

Doing the reverse and matching points that are nearby to a polygon—even if not within—would be easy with ST_DWITHIN(point, polygon, 2). I basically want to implement ST_DWITHIN(point, polygon, -2)

Any tips on how I might accomplish this?

Upvotes: 2

Views: 952

Answers (2)

Michael Entin
Michael Entin

Reputation: 7724

You can translate the requirement almost literally :)

  1. point is within polygon

ST_WITHIN(point, poly) or, in case of point and polygon, ST_INTERSECTS(point, poly) also works and might be faster

  1. point is at least 2 meters within the border of the polygon, let's spell it as point is at least 2 meters aways from the border of the polygon (we already know it is inside):

ST_DISTANCE(point, ST_BOUNDARY(poly)) >= 2. This condition is probably faster if written as NOT ST_DWITHIN(point, ST_BOUNDARY(poly), 2).

So we get

ST_INTERSECTS(point, poly) AND NOT ST_DWITHIN(point, ST_BOUNDARY(poly), 2)

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Wanted to follow up on Michael's answer with some dummy example

SELECT merchant_name, 
  ST_GEOGPOINT(Merchant_GP_Longitude, Merchant_GP_Latitude) geo,
FROM `utilities.Google_Places_Details` m
JOIN `bigquery-public-data.utility_us.us_cities_area` c
ON ST_WITHIN(ST_GEOGPOINT(Merchant_GP_Longitude, Merchant_GP_Latitude), city_geom)
AND ST_DISTANCE(ST_GEOGPOINT(Merchant_GP_Longitude, Merchant_GP_Latitude), ST_BOUNDARY(city_geom)) > 2000
WHERE name = 'Santa Barbara, CA' 
UNION ALL
SELECT 'city', city_geom
FROM `bigquery-public-data.utility_us.us_cities_area`
WHERE name = 'Santa Barbara, CA'

If you comment line with ST_DISTANCE(...) > 2000 you will get all merchants (2980) in Santa Barbara

enter image description here

While if you run above query as is - you will get those SB's merchants which stay not closer than 2000 meters from Santa Barbara boundaries (457 of such)

enter image description here

Upvotes: 2

Related Questions