英文:
TSQL stored procedure to read data from 2 tables and insert result into 3rd table
问题
I have 3 tables:
Outlets (Out_id, Out_name, Out_Latitude, Out_Longitude)
Competition (Comp_Name, comp_Latitude, comp_Longitude)
Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)
For each of the Out_ID, I want to calculate the radial distance corresponding to each competition, and if the radial distance is less than 5KM, then the corresponding details should be inserted into the Mapping Table.
Example sample data
Outlet
| Out_id | Out_name | Out_Latitude | Out_Longitude |
|---|---|---|---|
| 1 | ABC | 32.445 | 72.447 |
| 2 | DEF | 56.667 | 78.332 |
Competition
| Comp_Name | comp_Latitude | comp_Longitude |
|---|---|---|
| PQR | 13.334 | 45.662 |
| UVW | 12.445 | 78.587 |
So for Out_ID 1, if the radial distance is less than 5 for both competitions, and for Out_Id 2, only the first competition's radial distance is less than 5, then the mapping table should look like below:
Mapping
| Out_Id | Out_Name | Comp_Name | Comp_Distance |
|---|---|---|---|
| 1 | ABC | PQR | 4.2 |
| 1 | ABC | UVW | 3.8 |
| 2 | DEF | PQR | 12.2 |
The formula used for distance calculation is below:
Comp_Distance = (sqrt(power((comp_Latitude - Out_Latitude), 2) + Power((comp_Longitude - Out_Longitude), 2))) * 111
How can I achieve this using T-SQL Stored procedure on a Synapse Dedicated Pool? All the data is dummy data here.
英文:
I have 3 tables:
Outlets (Out_id, Out_name, Out_Latitude, Out_Longitude)
Competition (Comp_Name, comp_Latitude, comp_Longitude)
Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)
For each of the Out_ID I want to calculate the radial distance corresponding to each competition and if the radial distance is less than 5KM then the corresponding details should be inserted to the Mapping Table.
Example sample data
Outlet
| Out_id | Out_name | Out_Latitude | Out_Longitude |
|---|---|---|---|
| 1 | ABC | 32.445 | 72 |
| 2 | DEF | 56.667 | 78.332 |
Competition
| Comp_Name | comp_Latitude | comp_Longitude |
|---|---|---|
| PQR | 13.334 | 45.662 |
| UVW | 12.445 | 78.587 |
So for out_ID 1 if the Radial distance is less than 5 for both the competition and for Out_Id 2 only the first competition radial distance is less than 5 then the mapping table should look like below:
Mapping
| Out_Id | Out_Name | Comp_Name | Comp_Distance |
|---|---|---|---|
| 1 | ABC | PQR | 4.2 |
| 1 | ABC | UVW | 3 |
| 2 | DEF | PQR | 1 |
Formula used for distance calculation is below
Comp_Distance = (sqrt(power((comp_Latitude-Out_Latitude),2) + Power((comp_Longitude-Out_Longitude),2)))*111
How can I achieve this using T-SQL Stored procedure on synapse dedicated pool. All the data are dummy data here.
答案1
得分: 0
我无法确定这个公式返回的是米还是千米等单位，但我猜想你需要使用类似这样的内容：
插入映射 (Out_Id, Out_Name, Comp_Name, Comp_Distance)
选择 Out_Id, Out_Name, Comp_Name, Comp_Distance
从
(
选择
o.Out_Id, o.Out_Name, c.Comp_Name,
(sqrt(power((c.comp_Latitude - o.Out_Latitude), 2) +
power((c.comp_Longitude - o.Out_Longitude), 2))) * 111 as Comp_Distance
从 出口 o
交叉连接 竞争 c
) q
其中 Comp_Distance < 5
备注： 我猜测这个公式返回的单位是千米。
英文:
I couldn't figure out that the formula returns in meter or kilometer or etc. but I suppose you need to use something like this:
insert Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)
select Out_Id, Out_Name, Comp_Name, Comp_Distance
from
(
select
o.Out_Id, o.Out_Name, c.Comp_Name,
(sqrt(power((c.comp_Latitude - o.Out_Latitude), 2) +
power((c.comp_Longitude - o.Out_Longitude), 2))) * 111 as Comp_Distance
from Outlets o
cross join Competition c
) q
where Comp_Distance < 5
note: I suppose that the formula returns in kilometers.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论