CODEWITHSUNDEEP
sqlgoogle-bigquerygis
Rajjy
Rajjy

Reputation: 186

How to filter on geography data in BigQuery

I am in need to filter my BigQuery table based on column which is of type Geography. I tried below queries SELECT * FROM bigqueryproject1-279667.mycreatedataset1.newTabelwithAllDatatype where location = ST_GEOGPOINT(-6, 6) but this shows me validation error Equality is not defined for arguments of type GEOGRAPHY . If I replcae '=' with like operator I get message stating like is applicable only for String and Byte. I refereed this documentation but not getting how to have filter.

Upvotes: 0

Views: 1166

Answers (2)

Michael Entin
Michael Entin

Reputation: 7764

For geography, equality operator is not defined.

You can use ST_Equals, like ST_Equals(location, ST_GEOGPOINT(-6, 6)) if you really want only rows with geography value (almost) exactly equal to this point.

More often you would want to filter on other conditions, e.g. intersections with specific point, use ST_Intersects(location, ST_GEOGPOINT(-6, 6)) or on condition of being within some distance of a point, use ST_DWithin(location, ST_GEOGPOINT(-6, 6), 100) where last argument is distance in meters.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I think you want something like this:

WHERE ST_INTERSECTS(location, ST_GEOGPOINT(-6, 6))

Upvotes: 2

Related Questions