Search code examples
sql-servergeographysqlgeographygeographic-distance

STDistance – Calculates distance between two objects

I try to calculate the distance between two objects.

declare @p1 geography
declare @p2 geography
SELECT @p1 = WKT from tbl_1 where loc = "school"
SELECT @p2 = WKT from tbl_2 where loc = "school"
select round(@p1.STDistance(@p2)/1000,0) Distance_KM

But i get an error for the column loc

Invalid column name

This column exists and data type is geography.

Column WKT is populated using:

UPDATE [dbo].[lbl_1]
SET [WKT] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GO

What's wrong ??

Solution

  • Your string literal is incorrect.

    For SQL, you want single quotes, iow 'school' and not "school".

    SQL treats it as a column and not a string literal.