How can I find out if lightning struck the same place twice?

Question

I am learning how to use SQL and I want to work out if lightning has struck the same location more than once in 2019 using a dataset from NOAA.

This is the first couple of rows of the data table

I have tried multiple strategies for counting unique locations from the centre_point_geom column for example:

COUNT(DISTINCT center_point_geom)

but I keep getting this error message:

Aggregate functions with DISTINCT cannot be used with arguments of type GEOGRAPHY

How do I resolve this?

Answer 1

In order to use geography type in aggregate function - you should transform it into string like in below example

count(distinct to_json_string(center_point_geom))     

Meantime, if you are looking for count of lighting struck same deo_point - you should just use count without distinct - count(center_point_geom)

and full select statement could look like below

select any_value(center_point_geom) center_point_geom
from `project.dataset.table` t
group by to_json_string(center_point_geom)
having count(*) = 2