0

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

ughai
  • 9,830
  • 3
  • 29
  • 47
John Doe
  • 9,843
  • 13
  • 42
  • 73
  • Do you mean that `loc` column of geography type and not `VARCHAR` – ughai May 20 '15 at 09:49
  • also do both your tables `tbl_1` and `tbl_2` have a column `loc`? – ughai May 20 '15 at 09:51
  • What is `"school"` a string literal or a column? sql server is treating it as a column which is why I think you are getting your error. Try this `declare @p1 geography select 1 WHERE @p1 = "school"` – ughai May 20 '15 at 09:54
  • Got it. The string literal 'school' is between single quotes and not double qoutes :-(( – John Doe May 20 '15 at 09:58
  • what you are trying to do is fundamentally incorrect. how can `'school'` a string be equal to a `GEOGRAPHY` type column `loc`?? – ughai May 20 '15 at 10:02
  • column 'loc' is data type char(15) and WKT is data type geography – John Doe May 20 '15 at 10:08

1 Answers1

2

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.

ughai
  • 9,830
  • 3
  • 29
  • 47
leppie
  • 115,091
  • 17
  • 196
  • 297