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.

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?

Does this answer your question? [The type "geography" is not comparable. It cannot be used in the GROUP BY clause](https://stackoverflow.com/questions/40043713/the-type-geography-is-not-comparable-it-cannot-be-used-in-the-group-by-clause) — Bill the Lizard, Aug 13 '21 at 13:40

score 0 · Accepted Answer · answered Aug 13 '21 at 18:17

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

This worked, thank you! In case you were interested, 3883 locations experienced more than one lightning strike in the US in 2019. — Lizzie, Aug 27 '21 at 13:00

1 Answers1