Geek asks and answers

SQL geometry data column in GROUP BY clause

I use SQL Server and create a script that will output the geographic location of workers from my database. script 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 what I want to add GROUP BY w.display_name, w.geo_locationto the script as duplicate entries are displayed. An added column with the geography of the data type in the group by clause causes an error.

The error that occurs when adding this parameter is as follows:

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

Is there any way around this? I cannot convert w.geo_locationto VARCHARas needed in a geography data type.

+3
source share
2 answers

row_number() - .

declare @g geography;
set @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

declare @T table
(display_name varchar(10), geo_location geography)

insert into @T values ('1', @g)
insert into @T values ('1', @g)
insert into @T values ('1', @g)

insert into @T values ('2', @g)
insert into @T values ('2', @g)

select display_name, geo_location
from 
  (
    select *,
           row_number() over(partition by display_name, geo_location.ToString() order by (select 0)) as rn
    from @T
  ) as T
where rn = 1

:

display_name geo_location
------------ --------------------------------------------------------------------------------
1            0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
2            0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
+4

, - :

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()
+4

More articles:


All Articles