Read column data into Geography function

Question

good morning;

I am learning about the geography data type and this is exciting, I have a project at work with power bi that i would like to add, i have figured out how to create a map using excel and linking it to power bi visual but this is more interesting. i have a number of sites that contain Lat/Long in decimal format and i have imported that data into sql table and created a temp table as follows;

I am trying to figure out how to get the following function to read the Lattitude/Longitude columns to read the data from the table and send that data to a temp table..

I have started creating the script as follows;

is this the correct approach?....i really would like to understand this but any help would be appreciated. ideally what i think i want to do is have a fixed location look at 11 possible other gps locations and have a table that tells me the distance in miles from the fixed location out to each of the other 11 locations and if possible , can i also put a parameter in that will only grab the top 3 locations that are closet?, or is that just using another select statement?...thanks for any help.

Answer 1

Hi @Jankowski, Mark ，

Welcome to Microsoft Q&A!

It is recommended to provide your sample data of your table 'WED_GPS$' and your code instead of snapshots so that we could copy and paste into a query window to develop a tested query quickly.

Please refer below one simple example and check whether it is helpful to you.

create table #temp2  
 (mobile varchar(20),  
 LATITUDE float,  
 LONGITUDE float)  
      
 insert into #temp2 values  
 ('SCBBP62_123212',27.8950996398925,-82.7725982666015),  
 ('SCBBP63_126719',27.8950996398925,-82.7725982666015),  
 ('SCBBP65_123211',27.8952007293701,-82.7726974487304),  
 ('SCBBP66_124694',27.9362007141113,-82.8116989135742)  
      
 ;WITH X AS   
 (  
    SELECT *,  
       geography::Point(28.0936, -82.7643, 4326) SOURCE  
      ,geography::Point(LATITUDE, LONGITUDE, 4326) DESTINATION  
    FROM #temp2  
 )  
      
 SELECT top 3 mobile,LATITUDE,LONGITUDE,  
    SOURCE.STDistance(DESTINATION)/1609.344 AS MILES  
 FROM X  
 ORDER BY SOURCE.STDistance(DESTINATION)/1609.344  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.