set based approach to remove contained points

Question

I have this data:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
CREATE TABLE #temp
    (
      Id INT IDENTITY(1, 1) ,
      X FLOAT NOT NULL ,
      Y FLOAT NOT NULL
    )

INSERT INTO #temp (X, Y) VALUES (0, 0)
INSERT INTO #temp (X, Y) VALUES (0, 1)
INSERT INTO #temp (X, Y) VALUES (0, 2)
INSERT INTO #temp (X, Y) VALUES (0.5, 1)
INSERT INTO #temp (X, Y) VALUES (1, 1)
INSERT INTO #temp (X, Y) VALUES (1, 2)
INSERT INTO #temp (X, Y) VALUES (1.5, 0.5)
INSERT INTO #temp (X, Y) VALUES (2, 0)
INSERT INTO #temp (X, Y) VALUES (2, 1)

I would like to remove points that are contained within other points, such as:

(0, 1)
(1, 1)
(1.5, 0.5)

to obtain the outer most points that define the outer polygon consisting of only vertical and horizontal lines without redundancies (e.g. (0, 1) is a redundant point). Can this be achieved with a set based TSQL approach in SQL Server 2014?

PS:

A scatter plot of the data is as follows:

I would like to remove the encircled points. Ultimately, I am after the outer border (drawn as red lines). Hope this makes it clearer.

Answer 1

I believe this might work. It seems to deliver on your test data. A bit rough. Some of the SELECT MIN and SELECT MAX could perhaps be calculated in advance if your real data is large.

SELECT * 
-- uncomment this to delete the desired points
-- DELETE #temp
FROM #temp t
WHERE 
(
    -- Internal points
    (
            ( X > (SELECT MIN(X) FROM #temp) AND X < (SELECT MAX(X) FROM #temp) )
        AND ( Y > (SELECT MIN(Y) FROM #temp) AND Y < (SELECT MAX(Y) FROM #temp) )
    )
    -- Exceptions (points with nothing strictly outside them) [Don't want to lose (1,1)]
    AND EXISTS (SELECT * FROM #temp WHERE X > t.X AND Y > t.Y)
)
OR
    -- redundant edge points [(0,1) would be included as an "exception"]
(
    ( (t.X = (SELECT MIN(X) FROM #temp) OR t.X = (SELECT MAX(X) FROM #temp)) 
        AND EXISTS (SELECT * FROM #temp WHERE X = t.X AND Y > t.Y) 
        AND EXISTS (SELECT * FROM #temp WHERE X = t.X AND Y < t.Y)  )
    OR
    ( (t.Y = (SELECT MIN(Y) FROM #temp) OR t.Y = (SELECT MAX(Y) FROM #temp)) 
        AND EXISTS (SELECT * FROM #temp WHERE Y = t.Y AND X > t.X) 
        AND EXISTS (SELECT * FROM #temp WHERE Y = t.Y AND X < t.X)  )
)