1

I have the following schema (simplified):

Locations

Id INT,
StaticLocation GEOGRAPHY

Capacities

LocationId INT
SpareCapacity INT

I'm trying to find, for each location a sum of the SpareCapacity of all locations within x miles of it.

This is complicated by the fact that there are a few CTEs which I'm joining to but the principal is the same (I hope). I'm confused because this works as a standalone query:

SELECT sum(SpareCapacity)
FROM Capacities UC 
    JOIN Locations L ON L.Id = UC.LocationId
WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10   
GROUP BY u.StaticLocation

When I try to join it to a previous CTE though, I get the error:

Column 'Locations.StaticLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the column to the GROUP BY clause I get another error:

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

The query inside my CTE looks like:

SELECT k.LocationId, 
    (
    SELECT sum(SpareCapacity) Capacities
    FROM Capacities UC 
        JOIN Location u ON u.id = UC.LocationId
    WHERE u.StaticLocation.STDistance(uk.StaticLocation) / 1609.344 < 10    
    --GROUP BY u.StaticLocation
) Vacancies
FROM Cte_SomeData k -- cte which has some simple columns
JOIN Locations uk on k.LocationId = uk.Id
GROUP BY k.LocationId -- tried adding StaticLocation here without luck

If I replace uk.StaticLocation with geography::Point(53, -2, 4326) it works (but obviously I need it relative to the Location's geography not a static point. Am I missing a group by somewhere?

0
1

Using the examples from SQL Geography data type column in GROUP BY clause, I was able to group by a geography location. See if this works for you.

--demo set up
Declare @Locations table (ID int, StaticLocation GEOGRAPHY)
Declare @Capacity table (LocationId int, SpareCapacity int)
insert into @Locations(id, StaticLocation) values
(1,geography::Point(41.878899,-87.636297, 4326)),
(2,geography::Point(40.749014,-73.985317, 4326))

insert into @Capacity(LocationId,SpareCapacity) values
(1,100),
(1,1000),
(2,200),
(2,2000)

--Solution
SELECT GEOGRAPHY::STGeomFromText(L.StaticLocation.STAsText(), 4326) as StaticLocation
    ,sum(SpareCapacity) as SumSpareCapacity
FROM @Capacity UC
JOIN @Locations L
    ON L.Id = UC.LocationId
--WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10   
GROUP BY L.StaticLocation.STAsText()

| StaticLocation                                 | SumSpareCapacity |
|------------------------------------------------|------------------|
| 0xE6100000010C821FD5B0DF5F444057CC086F0F7F52C0 | 2200             |
| 0xE6100000010C59BE2EC37FF04440BF620D17B9E855C0 | 1100             |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.