Calculating distance with latitude longitude pair in query

Question

I have the following table in sqlfiddle

a   b   alat    alon    blat    blon
A   X   40      -76      26     -80
B   Y   41      -74      24     -81
C   Z   42      -73      24     -80

I would like to use a select query to add a numeric distance column.

My current try is

select a,b,alat,alon,blat,blon,
GEOGRAPHY::Point(alat, alon,4326).STDistance(GEOGRAPHY::Point(blat, blon, 4326))
from lat_lon

but I get the error

Incorrect syntax near 'GEOGRAPHY'

How can I include a distance column in my select statement?

Depend the SQLFiddle you use MySql. But you tagged sql-server. Which one is correct? — Zeki Gumus, Dec 21 '18 at 15:59
Theres an answer to this already, using cos and sin, i think its applicable to both ms sql and mysql http://sqlfiddle.com/#!9/383af/9/0 https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql — ivan, Dec 21 '18 at 16:02
Your syntax is correct, take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=7643fa6ca8e13bdf505feb995c80a5cf — John Cappelletti, Dec 21 '18 at 16:04
Which version of SQL Server you using? You need to have at least 2008 for GEOGRAPHY and at least 2012 for STDistance. — Zeki Gumus, Dec 21 '18 at 16:11
@JohnCappelletti and satischcse pointed out that this query for this example seems to work. For whatever reason it fails on my real data. Something I need to solve. I found the alternative by ivan to be most useful for a simple alternative which does not require GEOGRAPHY. — Alex, Dec 21 '18 at 16:29

score 0 · Answer 1 · answered Dec 21 '18 at 16:17

0

Your query is running fine (please see screenshot below):

answered Dec 21 '18 at 16:17

sacse

score 0 · Answer 2 · answered Dec 21 '18 at 22:50

0

How can I include a distance column in my select statement?

Check your select statement what are you running, it might be missing . before STDistance.

And SQL you posted here working fine..

answered Dec 21 '18 at 22:50

CR241

2 Answers2