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

Question

I have a query like this :

SELECT WorkId, 
RegisterDate , Location

FROM (

SELECT dbo.[Work].WorkId ,
dbo.[Work].RegisterDate , dbo.Look.Location

FROM dbo.Municipality INNER JOIN
dbo.[Work] ON dbo.Municipality .Municipality Id = dbo.[Work].MunicipalityWorkId INNER JOIN
dbo.Look ON dbo.[Work].LookWorkId = dbo.Look.LookId
WHERE (dbo.Look.Location IS NOT NULL) AND Type= 1

) E

GROUP BY WorkId ,RegisterDate , Location

And I get this error :

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

I need to add Location to Group By, because I need to display Location in the database. What is the solution for this situation? Thanks.

convert/cast to something else then group? it kinda makes sense to me not be able to group by geography... — gsharp, Oct 14 '16 at 13:00
Please post the custom user type for `geography`? You may be missing a flag in your definintion - https://technet.microsoft.com/en-us/library/ms187630(v=sql.105).aspx . According to that site `You can perform ORDER BY, GROUP BY, and PARTITION BY operations with user-defined types if the type supports binary ordering. A type is binary ordered if it has the IsByteOrdered flag set to true in the SqlUserDefinedType attribute that is specified as part of the type definition. This flag indicates that the binary representation for the type is in the semantically correct order for that type.` — Steve Mangiameli, Oct 14 '16 at 13:03
Unless you have horribly misnamed columns, you shouldn't need to use `GROUP BY`. You're grouping by `WorkId`. That name indicates it's unique in the `Work` table. You're joining on the `MunicipalityId` and `LookId` columns of the `Municipality` and `Look` tables. Those names indicate they're unique in their tables. What duplicates were you getting that made you think to do any grouping at all? — , Oct 14 '16 at 13:10

score 4 · Accepted Answer · edited May 23 '17 at 12:01

4

Here is one way.

Convert the Location to text using Stastext and use it Group by. Then convert it back to geo in Select using STGeomFromText

SELECT WorkId,
       RegisterDate,
       geography::STGeomFromText(Location.STAsText(), 4326)
FROM   (SELECT dbo.[Work].WorkId,
               dbo.[Work].RegisterDate,
               dbo.Look.Location
        FROM   dbo.Municipality
               INNER JOIN dbo.[Work]
                       ON dbo.Municipality.MunicipalityId = dbo.[Work].MunicipalityWorkId
               INNER JOIN dbo.Look
                       ON dbo.[Work].LookWorkId = dbo.Look.LookId
        WHERE  ( dbo.Look.Location IS NOT NULL )
               AND Type = 1) E
GROUP  BY WorkId,
          RegisterDate,
          Location.STAsText()

Referred from this answer

Note : The geography function are CASE sensitive it should be used as it is

STGeomFromText
STAsText

edited May 23 '17 at 12:01

Community

1
1

answered Oct 14 '16 at 13:03

Pரதீப்

91,748
19
131
172

In last line, Should it be `STGeomFromText`? Thanks. – jason Oct 14 '16 at 13:09
No no it should be `Stastext` it is used to convert Geography data to `nvarchar` so that it can be used in `Group by` – Pரதீப் Oct 14 '16 at 13:12
So where should I use `STGeomFromText` ? – jason Oct 14 '16 at 13:13
@jason - It should be used in `SELECT`. I have already used that in my query `geography::STGeomFromText(Location.Stastext(), 4326)` – Pரதீப் Oct 14 '16 at 13:13
I get this : `Could not find method 'Statstext' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'` – jason Oct 14 '16 at 13:16
@jason - It is because of `Stastext` it should be `STAsText`. Updated check now – Pரதீப் Oct 14 '16 at 13:17
Thank you very much – jason Oct 14 '16 at 13:19
the problem with this approach is that equal geography can represented differently in WKT – CervEd Nov 02 '21 at 14:27

ScaisEdge · Answer 2 · 2016-10-14T13:16:38.300

0

You an not using aggregate function like sum or count so you could avoid group by and order by

SELECT 
    WorkId
  , RegisterDate 
  , Location

FROM (
    SELECT 
          dbo.[Work].WorkId 
        , dbo.[Work].RegisterDate 
        , dbo.Look.Location
    FROM dbo.Municipality 
    INNER JOIN dbo.[Work] ON dbo.Municipality.Municipality Id = dbo.[Work].MunicipalityWorkId 
    INNER JOIN dbo.Look ON dbo.[Work].LookWorkId = dbo.Look.LookId
WHERE (dbo.Look.Location IS NOT NULL) AND Type= 1
ORDER  BY WorkId ,RegisterDate , Location
) E

edited Oct 14 '16 at 13:16

answered Oct 14 '16 at 13:00

ScaisEdge

131,976
10
91
107

Isn't there another solution? Because my actual query is based on Group By – jason Oct 14 '16 at 13:01
I changed my query to what you said and I got this : `The geography data type cannot be selected as DISTINCT because it is not comparable.` – jason Oct 14 '16 at 13:03

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

2 Answers2

Linked