Read column data into Geography function

Jankowski, Mark 1 Reputation point

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.

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

    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  
     ;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  


    Best regards,

    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