Reputation: 65
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
Upvotes: 0
Views: 247
Reputation: 368
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
Upvotes: 1
Reputation: 31
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 @[email protected](@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.
Upvotes: 0