Read column data into Geography function

Jankowski, Mark 1 Reputation point
2021-08-08T16:34:44.45+00:00

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;

121359-gpd-table1.jpg

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;

121437-gpd-table2.jpg

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-09T06:35:14.207+00:00

    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:
    121563-output.png

    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.

    0 comments No comments