question

JankowskiMark-3149 avatar image
0 Votes"
JankowskiMark-3149 asked MelissaMa-msft commented

Read column data into Geography function

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.

sql-server-transact-sql
gpd-table1.jpg (138.9 KiB)
gpd-table2.jpg (45.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

update...I have been working on this for last few hours and this is where I have gotten, close, but i still need to figure out how to reference (Lat/Long) or (geog) in the @Destination()......121440-gpd-table3.jpg


0 Votes 0 ·
gpd-table3.jpg (265.8 KiB)

Hi @JankowskiMark-3149

Could you please validate the answer and provide any update?

If it is not working, please provide all your sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @JankowskiMark-3149

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.


output.png (6.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.