Sql-server – Geography Inside a CTE

sql serversql-server-2012

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?

Best Answer

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             |

Related Solutions

Sql-server – Top 2 rows per partition from an absolute value date difference

This is just a starting point and will likely need some tweaking.

Essentially this will get you the 4 closest capture dates to your specified date (2 closest after and 2 closest before).

You will need to add some logic to your outer select to pick which ones to use, but you will be doing a DATEDIFF on 4 fields instead of all of them.

WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
INNER JOIN CTE c
    ON r1.location = c.location
    AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)

Sql-server – limiting sql server backups inside .bak file

The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

master.dbo.xp_delete_file

Best Answer

Related Solutions

Sql-server – Top 2 rows per partition from an absolute value date difference

Sql-server – limiting sql server backups inside .bak file

Related Question