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?

1 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)

  • This was really helpful for my issue! Thanks! ;-) Commented Oct 15, 2021 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.