CODEWITHSUNDEEP
google-bigquerypolygon
Zayn_SE
Zayn_SE

Reputation: 173

Query for Polygon in BigQuery

I am trying to query for records within the following Polygon using #standardSQL:

POLYGON(( -139.833984375 16.97274101999902, -138.33984375 15.029685756555674, -67.060546875 48.516604348867475,  -68.37890625 49.89463439573421, -139.833984375  16.97274101999902))

This is my query: 

SELECT *

FROM `ais-data-analysis.adsb.target_updates` a

WHERE (timestamp BETWEEN '2019-09-16 13:00:00' AND '2019-09-16 14:30:59')  AND ST_DWithin( ST_GeogFromText( 'POLYGON(( -139.833984375 16.97274101999902, -138.33984375 15.029685756555674, -67.060546875 48.516604348867475,  -68.37890625 49.89463439573421, -139.833984375  16.97274101999902))'),
      ST_GeogPoint(-139.833984375 ,
        16.97274101999902),
      10)
ORDER BY timestamp ASC;

My Query runs but the data returned doesn't account for the polygon parameters and instead returns all records from the given time above. I myself am also unsure as to how to approach polygons in BigQuery.

Upvotes: 1

Views: 557

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

below expression is always true that is why! 

ST_DWithin(
  ST_GeogFromText('POLYGON(( -139.833984375 16.97274101999902, -138.33984375 15.029685756555674, -67.060546875 48.516604348867475,  -68.37890625 49.89463439573421, -139.833984375  16.97274101999902))'), 
  ST_GeogPoint(-139.833984375, 16.97274101999902), 
  10)

The reason is that the first vertex in involved poligon is the same as the point you use

You can also see this from visualization below

enter image description here

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

You need to test again a variable, not a constant:

SELECT ST_DWithin( 
  ST_GeogFromText( 
    'POLYGON(( -139.833984375 16.97274101999902, -138.33984375 15.029685756555674, -67.060546875 48.516604348867475,  -68.37890625 49.89463439573421, -139.833984375  16.97274101999902))')
  , ST_GeogPoint(-139.833984375 , 16.97274101999902)
  , 10)

The value of that statement is true - so everything will be returned.

Instead have ST_GeogPoint(-139.833984375 , 16.97274101999902) be a column from the queried table.

Upvotes: 1

Related Questions