How to Cast geometry to Geography in SQL script in MS SQL Server?

Question

I have a Table with some spatial Data in MS SQL Server. The datatype of the spatial column is geometry.

I'm trying to get the closest data from an input file using the following query:

DECLARE @g geometry;  
SET @g= geometry::STGeomFromText('POINT(-74.19432 43.745)', 4326); 

SELECT TOP(1) Shape,  Shape.ToString(), UID, Colour, Shape.STDistance(@g) FROM dbo.roads  
WHERE Shape.STDistance(@g) IS NOT NULL  
ORDER BY Shape.STDistance(@g);

This query runs successfully, but I want the distance between my input point and the Shape in meters; Right now the value is in Decimal Degrees, since my data is WGS84-lat-long.

To get Distance in Meters, I need to convert the geometry on the fly to Geography

I can't find the syntax to do this Casting in the Select Clause of the Query.

(All the examples That I find, do the casting outside the Actual Select statement. )

How do I temporarily cast the geometry to geography on the fly?

Answer 1

I'm not sure this is the most efficient way of doing it, but it certainly works:

SELECT geography::STGeomFromText([geom].STAsText(),4326)

where 4326 is the spatial reference ID (SRID)