Filtering dataset by geolocation data using SQL in BigQuery

I trying to determine if NCAA basketball games have an affect on liquor sales in the surrounding areas around the stadiums where the teams play their games. I am writing a query that filters and sorts the Iowa Liquor Sales dataset to include on the areas around the schools. Full query at the end of the post.

In the Iowa Liquor sales dataset it contains a column for the geography location which is formatted like POINT(long,lat). Here is my SELECT statement for the geo data, I have seen different types of commands for what to use and I chose this one as it works before I add the where clause.

ST_ASTEXT(store_location) AS store_location,

I have researched how to use the geo data and I have come up with a filter for the WHERE clause, the purpose of this filter is have only the stores that are within 5 miles of the sports arena.

This portion of the WHERE clause is after a date filter.

  AND store_location IS NOT NULL
  AND (ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-93.634821,42.021009), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-91.554604,41.663778), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-93.652720,41.604430), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-92.467260,42.514382), 8050))

I am receiving an error.

This error appears only after I put in the geolocation filter in the WHERE clause.

++Query error: No matching signature for function ST_GEOGFROMTEXT Argument types: GEOGRAPHY Signature: ST_GEOGFROMTEXT(STRING, [BOOL]) Argument 1: Unable to coerce type GEOGRAPHY to expected type STRING Signature: ST_GEOGFROMTEXT(STRING, [oriented => BOOL], [planar => BOOL], [make_valid => BOOL]) Argument 1: Unable to coerce type GEOGRAPHY to expected type STRING at [24:19] at [5:1]++

Attempted fixes: Tried different variations in the select clause on the store_location using different commands. Tried using different commands in the WHERE clause.

My question is how would I fix this and what the changes do and why I do it that way. For instance should I use a CTE, or try something else in the WHERE clause?

SELECT
  ST_ASTEXT(store_location) AS store_location
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE
store_location IS NOT NULL
  AND (ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-93.634821,42.021009), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-91.554604,41.663778), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-93.652720,41.604430), 8050)
    OR ST_DWITHIN(ST_GEOGFROMTEXT(store_location), ST_GEOGPOINT(-92.467260,42.514382), 8050))

Solution

Assuming you actually needed to use ST_GEOMFROMTEXT on some calculated column, yes you would need to subquery or use a CTE. Because the SELECT aliased columns are not accessible in the WHERE.

But it appears you actually have a geography typed column anyway. So ST_GEOMFROMTEXT isn't necessary. All that is happening is it's trying to use a stringified WKT instead of an actual geography value.

SELECT
  ST_ASTEXT(s.store_location) AS store_location
FROM `bigquery-public-data.iowa_liquor_sales.sales` AS s
WHERE
store_location IS NOT NULL
  AND (ST_DWITHIN(s.store_location, ST_GEOGPOINT(-93.634821,42.021009), 8050)
    OR ST_DWITHIN(s.store_location, ST_GEOGPOINT(-91.554604,41.663778), 8050)
    OR ST_DWITHIN(s.store_location, ST_GEOGPOINT(-93.652720,41.604430), 8050)
    OR ST_DWITHIN(s.store_location, ST_GEOGPOINT(-92.467260,42.514382), 8050))