I need to calculate the distance between points within the place where I work, so I calculated the coordinates of each location in X and Y and put coordinates in the system.
I am doing query to calculate this distance, but since it is not always in a straight line that will be calculated I had to put intersection points to calculate when it leaves one street and goes to another, p>
For example: From 1AF-001-10 to 1AD-013-10 it leaves the street "AF" and goes to street "AD".
For this he needs to calculate this:
De Para
1AF-001-10 || interseçãoAF = valor1
De Para
interseçãoAF || interseçãoAD = valor2
De Para
interseçãoAD || 1AD-013-10 = valor3
Ou seja :
De Para
1AF-001-10 || 1AD-013-10 = soma(valor1,valor2,valor3)
I got the calculus straight, I used the case with substr saying that if the initials of the street is equal to the end he does the calculation and I do not know how to put in the else as he has to proceed with the other calculations.
Below is a query:
[Select
local_inicial,
local_final,
case when substr(local_inicial,2,2) = substr(local_final,2,2) then Round (SQRT (POWER (eixoxf - eixoxi,2) + POWER (eixoyf - eixoyi,2)))*1.2 else 0 end as "dist",
carton,
usuario,
tempoi,
tempof
From
(Select
linhas,
loci as local_inicial,
LEAD(loci, 1,loci) OVER (PARTITION BY usu,hh,dia1 ORDER BY linhas) as local_final,
ctn as carton,
usu as usuario,
hhmmssi as tempoi,
LEAD(hhmmssi, 1,hhmmssi) OVER (PARTITION BY usu,hh,dia1 ORDER BY linhas) as tempof,
exi as eixoxi,
LEAD(exi, 1,exi) OVER (PARTITION BY usu,hh,dia1 ORDER BY linhas) as eixoxf,
eyi as eixoyi,
LEAD(eyi, 1,eyi) OVER (PARTITION BY usu,hh,dia1 ORDER BY linhas) as eixoyf
From
(Select
rownum as linhas,
D.frstol as loci,
D.To_subnum as ctn,
D.usr_id as usu,
To_Char (D.trndte, 'HH24') as hh,
To_Char (D.trndte, 'DD') as dia1,
To_Char (D.ins_dt, 'HH24:MI:SS') as hh24,
To_Char (D.ins_dt, 'HH24') *3600 + To_Char (D.ins_dt, 'MI') *3600/60 + To_Char (D.ins_dt, 'SS') as hhmmssi,
Substr(L.top_left_x,1,2) as exi,
Substr(L.top_left_y,1,2) as eyi
From DLYTRN D, LOCMST L
Where D.WH_ID = L.WH_ID
And D.frstol = L.stoloc
And D.trndte >= trunc (sysdate) -5
And D.oprcod = 'UPK'
And D.actcod in ('CASPCK','PLAPCK','OTRPCK')
And D.usr_id <> all ('EDREIS','ARSILVA','EGONCALVES','AAKOJO')
And D.usr_id = 'ADFLORENCIO'
And L.top_left_x > '0'
And L.top_left_y > '0'
Order By D.usr_id, To_Char (D.trndte, 'DD'), To_Char (D.ins_dt, 'HH24:MI:SS')))
I thought of doing another query inside the case saying that if the initial location acronym is "X" it needs to pass through the intersection points "A", "B" etc. and in the end add the two. but I do not know how to do it.
Query that brings only the points of intersection:
[Select
Substr(stoloc,2,2) as inter,
Substr(L.top_left_x,1,2) as exi,
Substr(L.top_left_y,1,2) as eyi
From LOCMST L
Where L.top_left_x > '0'
And L.top_left_y > '0'
And L.stoloc like '1%PD-%'
Below is an image of the layout to make it easier to understand the points of intersection.
