Find the furthest distance of points

Question

In this scenario I have a set of four points for ID 1 and 2. I need to find and select the two points that are furthest from my set points with the same ID.

The table is scripted here:

ID  name    point                                           id_point
====================================================================
1   DN700   POINT (-550493.96 -1218974.69)                  1
1   DN700   POINT (-550513.92733318976 -1218929.5493905835) 2
1   DN700   POINT (-550490.62291509821 -1218980.7209425652) 3
1   DN700   POINT (-550512.43436134933 -1218933.2777663434) 4
2   DN700   POINT (-550235.5039543492 -1219120.0737476321)  5
2   DN700   POINT (-550278.61165674869 -1219099.6880138929) 6
2   DN700   POINT (-550301.89265282557 -1219088.8117909778) 7
2   DN700   POINT (-550330.76399739366 -1219075.4882849427) 8

For ID 1, the farthest points are id_point 2 and 3. I will use it in another procedure, where I have to define the start and end point, which in this case is exactly point 2 and 3.

I know the solution is to use the STDistance() function to compare points with the same ID, then select a MAX value from the result, but I'm stuck here.

Any help is appreciated, thanks a lot!

Here is an sample of my query, which I describe in the comments. The #points_on_lin is a table which I previously scripted in question. The only difference is that the points are stored as geometry:

DECLARE @max_id int, @current_id int = 1;
SET @max_id = (SELECT MAX(ID_point) FROM #points_on_lin)
    WHILE @current_id <= @max_id
        BEGIN
            DECLARE @point1 geometry, @point2 geometry, @ID int, @IDP int;
            SET @point1 = (SELECT points FROM #points_on_lin WHERE ID_point = @current_id)
            SET @point2 = (SELECT points FROM #points_on_lin WHERE ID_point = @current_id + 1)
            SET @ID = (SELECT ID FROM #points_on_linia WHERE ID_point = @current_id)
            SET @name = (SELECT name FROM #points_on_lin WHERE ID_point = @current_id)
            SET @IDP = (SELECT ID_point FROM #points_on_lin WHERE ID_point = @current_id)
                INSERT INTO #points_dist
                    SELECT @ID, @name, @point1.STDistance(@point2), @IDP
            SET @current_ID = @current_ID +1
        END

And the results from table #points_dist are here:

ID  name    distance            IDP
1   DN700   49.3595888677907    1
1   DN700   56.228317019116     2
1   DN700   52.216799572342     3
1   DN700   334.040699536517    4
2   DN700   47.6849257758674    5
2   DN700   25.696244924673     6
2   DN700   31.7973324390265    7
2   DN700   544.411492523736    8

Answer 1

Try this:

;WITH CTE AS
(
    SELECT ID FROM #points_on_lin GROUP BY ID
)
SELECT CTE.ID, Point1, Point2, Distance FROM CTE
CROSS APPLY
(
    SELECT TOP 1 
        T1.id_point AS Point1, T2.id_point AS Point2, T1.Point.STDistance(T2.Point) AS Distance 
    FROM 
        #points_on_lin T1 join #points_on_lin T2 on T1.ID = T2.ID AND T1.id_point < T2.id_point
    WHERE 
        T1.ID = CTE.ID
    ORDER BY
        Distance DESC   
) A

Output:

ID  Point1  Point2  Distance
1   2       3       56.228317019116
2   5       8       105.177655821281

Answer 2

I did this:

create table #MainTable
(
    id int,
    name nvarchar(10),
    point nvarchar(max),    
    id_point int
)

insert into #MainTable
values
        (1 ,'DN700','POINT (-550493.96 -1218974.69)' ,1),
        (1 ,'DN700','POINT (-550513.92733318976 -1218929.5493905835)' ,2),
        (1 ,'DN700','POINT (-550490.62291509821 -1218980.7209425652)' ,3),
        (1 ,'DN700','POINT (-550512.43436134933 -1218933.2777663434)' ,4),
        (2 ,'DN700','POINT (-550235.5039543492 -1219120.0737476321)' ,5),
        (2 ,'DN700','POINT (-550278.61165674869 -1219099.6880138929)' ,6),
        (2 ,'DN700','POINT (-550301.89265282557 -1219088.8117909778)' ,7),
        (2 ,'DN700','POINT (-550330.76399739366 -1219075.4882849427)' ,8)

create table #Result
(
    id int,
    name nvarchar(10),
    distance float,
    id_point int 
)

declare @minId as int=(select min(id) from #MainTable)
declare @maxId as int=(select max(id) from #MainTable)
declare @p1 as int=0
declare @p2 as int=0
DECLARE @point1 geometry, @point2 geometry
declare @Distance as float=0

while @minId<=@maxId
    begin
        set @p1=(select min(id_point) from #MainTable where id=@minId)
        set @p2=(select max(id_point) from #MainTable where id=@minId)
        while @p1<@p2
            begin
                set @point1=(select geometry::STGeomFromText(point,0) from #MainTable where id_point=@p1)
                set @point2=(select geometry::STGeomFromText(point,0) from #MainTable where id_point=@p1+1)
                set @Distance=@point1.STDistance(@point2)
                insert into #Result (id,name,distance,id_point)
                select id,name,@Distance,@p1 from #MainTable where id_point=@p1

                insert into #Result (id,name,distance,id_point)
                select id,name,@Distance,@p1+1 from #MainTable where id_point=@p1+1
                set @p1=@p1+1
            end     
        set @minId=@minId+1
    end

select r1.* 
from #Result r1 
inner join (select id,max(distance)maxDistance
            from #Result
            group by id)r2 on r1.id=r2.id
                                and r1.distance=r2.maxDistance
order by id

drop table #MainTable,#Result

and here is the result: Result of code

hope it's helpfull.