SQL Server 2008 Spatial - How to find the distance between two points?
One of the methods that the SQL Server 2008 Spatial
types support is STDistance. This returns the distance between two
geometries.
declare
@p1 geometry = 'POINT (0
10)';
declare
@p2 geometry = 'POINT (0
20)';
select
@p1.STDistance (@p2);
If you are doing this for points on the earths surface then you need to use
the geography type.
declare
@p1 geography = 'POINT (-80
100)';
declare
@p2 geography = 'POINT (-80
110)';
select
@p1.STDistance (@p2)
For the geography type be aware that the values are the latitude and
longitude. Also be aware that the order of these is may change to be in line
with the other spatial applications in the world already (See this forum post
for the discussion into this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2431933&SiteID=1)
In the examples above I use the built in parse functionality of CLR types
rather than using the static methods of the Geometry and Geography types. This
makes the code slightly simpler but does impose the default Spatial Reference Id
of 0 for Geometeries and 4326 for Geography types.
-