SQL Geography data type column in GROUP BY clause

Question

I'm using SQL Server and creating a script which will get the workers geographical location out of my database. The script is below.

SELECT w.display_name, w.geo_location
FROM jobs j WITH(NOLOCK)
INNER JOIN workers w WITH(NOLOCK) ON w.worker_id = j.worker_id
WHERE .....

The problem is that I want to add GROUP BY w.display_name, w.geo_location to the script as there are duplicate records being shown. Added a column with the data type geography to a group by clause causes an error to be thrown.

The error being thrown when I add this in is:

The type "geography" is not comparable. It cannot be used in the GROUP BY clause.

Is there a way around this? I cannot convert w.geo_location to a VARCHAR as it is needed in the geography data type.

Answer 1

If you want to continue to use the group by syntax you can convert the geo column to text and back again:

SELECT w.display_name, geography::STGeomFromText(w.geo_location.STAsText(), 4326) as Location
FROM jobs j WITH(NOLOCK)
INNER JOIN workers w WITH(NOLOCK) ON w.worker_id = j.worker_id
WHERE .....
GROUP BY w.display_name, w.geo_location.STAsText()