CODEWITHSUNDEEP
google-bigquerygis
ronencozen
ronencozen

Reputation: 2201

BigQuery Geo functions - How to compute the shortest distance to the polygon perimeter

I would like to compute the shortest distance from the yellow point in the image below to the polygon boundary using built in BigQuery Geo functions.

enter image description here

I could not find anything myself.

Here is the query that builds the example.

WITH objects AS(
  SELECT 'POLYGON((-84.3043408314983 33.78004925, -84.3058929975152 33.7780287948446, -84.3026549053438 33.77962155, -84.3018234603607 33.7798783, -84.3041030408163 33.7785105714286, -84.2983655895464 33.7814847396304, -84.2869801170094 33.7772419185107, -84.2842584693878 33.7827876938775, -84.2863881748169 33.7848439284835, -84.2963746470588 33.7897689411765, -84.2979002513655 33.790508814658, -84.2978883265306 33.7851126734694, -84.300035153059 33.78268675, -84.3043408314983 33.78004925))' wkt_string
  UNION ALL
  SELECT 'POINT(-84.2998716702097 33.7796025711153)' wkt_string

)

SELECT ST_GEOGFROMTEXT(wkt_string) geo
FROM objects

Upvotes: 0

Views: 1041

Answers (2)

Michael Entin
Michael Entin

Reputation: 7724

Use ST_Distance function to compute shortest distance between shapes:

WITH objects AS(
  SELECT 
  'POLYGON((-84.3043408314983 33.78004925, -84.3058929975152 33.7780287948446, -84.3026549053438 33.77962155, -84.3018234603607 33.7798783, -84.3041030408163 33.7785105714286, -84.2983655895464 33.7814847396304, -84.2869801170094 33.7772419185107, -84.2842584693878 33.7827876938775, -84.2863881748169 33.7848439284835, -84.2963746470588 33.7897689411765, -84.2979002513655 33.790508814658, -84.2978883265306 33.7851126734694, -84.300035153059 33.78268675, -84.3043408314983 33.78004925))' 
   AS poly,
  'POINT(-84.2998716702097 33.7796025711153)' AS point
)
SELECT ST_Distance(ST_GEOGFROMTEXT(poly), ST_GEOGFROMTEXT(point))
FROM objects

One caveat - it computes distance between point and polygon, so if the point is inside the polygon, the distance is 0. If you really want distance to polygon boundary, add ST_Boundary to the mix:

WITH objects AS(
   ...
)
SELECT ST_Distance(ST_Boundary(ST_GEOGFROMTEXT(poly)), ST_GEOGFROMTEXT(point))
FROM objects

Upvotes: 1

ronencozen
ronencozen

Reputation: 2201

this is the function i was looking for:

ST_CLOSESTPOINT(geography_1, geography_2[, use_spheroid]).

Upvotes: 1

Related Questions