I guess it works the same as PostGIS. If you are working in geometry type with geometry functions, the function just calculate with the unit the map has. In your case it seems to be lon lat degrees. Then your distance will not make much sense because the lat and lon degrees is of different length except on the equator.
So, what you have to do is transform your data to some meter based projection or use the geography type with geodetic functions.
HTH
Nicklas
Here's something that should work with Geography datatypes. You may want to play around with the tolerances in the buffers to suit your data better. I've used CTEs to try and make the method clearer
DECLARE @PointID INT;
SET @PointID = 1;
DECLARE @SearchCount INT;
SET @SearchCount = 10;
DECLARE @SearchDistance INT;
SET @SearchDistance = 15000;
WITH Candidate AS ( -- Original Query
SELECT TOP(@SearchCount)
a.PointID,
a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget],
a.pointGeom,
b.lineGeom,
b.LineID
FROM
LINES b,
POINTS a
WHERE
a.PointID = @PointID
AND (a.pointGeom.STDistance(b.lineGeom)) < @SearchDistance
ORDER BY a.pointGeom.STDistance(b.lineGeom)
),
BufferIntersection AS ( -- Get first geography from the intersection of buffer
-- May require a check to ensure a linestring
SELECT PointID, pointGeom, LineID,
pointGeom.BufferWithTolerance(pointGeom.STDistance(lineGeom) + 0.05, .01, 0).STIntersection(lineGeom).STGeometryN(1) AS Intersection
FROM Candidate
),
IntPoint AS ( -- Calculate the mid point for the intersection line.
SELECT PointID, pointGeom, LineID,
Intersection.STIntersection(Intersection.STPointN(1).BufferWithTolerance(Intersection.STLength() / 2.0, 0.001, 0)).STPointN(2) intPoint
FROM BufferIntersection
)
--put it together
SELECT PointID, LineID,
Geography::STGeomFromText('LINESTRING(' +
CAST(CAST(pointGeom.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(pointGeom.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ', ' +
CAST(CAST(intPoint.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(intPoint.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ')',pointGeom.STSrid)
FROM IntPoint;
Best Answer
Hopefully you've found the answer you're looking for by now but I just came across this and figured I'd take a stab at answering it for others at least.
From C# here's what I do for inserting a Polygon. I'm deriving this on the fly from some working code I have but not actually testing it so forgive any typo errors.
You'll need the following assemblies. System.Data.SqlClient; System.Data.SqlTypes; System.Data.SqlServer.Types;
I hope this helps someone.