I have a table in SQL Server database that has a 2 fields X, Y. These fields represent coordinates on a field (american football field). 0,0 (X,Y) is the starting point with the largest value and furthest away point being 145,90 (X,Y).
Each row in the table is a coordinate from the pitch.
I want to be able to measure the distance between different coordinates on the field (different rows in the database) eg. 3,9 to 15,20. I also want to be able to measure the angle between coordinates.
I looked the spatial types etc in SQL Server but its seems to me that is related to Latitude, Longitude.
Is there any way in SQL Server that can I set such up in SQL with a custom map to measure between custom points, angles etc?
You can use the Geometry type. https://msdn.microsoft.com/en-us/library/bb895270.aspx
Here's some quick examples.
Use a derived column to create a Point:
CREATE TABLE #Test (
X INT,
Y INT,
POINT AS GEOMETRY::STGeomFromText('POINT('+CONVERT(VARCHAR(20),X)+' '+CONVERT(VARCHAR(20),Y)+')',0 )
)
INSERT INTO #Test( X, Y) VALUES (3, 9),(15,20)
If you want to view your points in SSMS adding a buffer can make things easier to see:
SELECT *,POINT.STBuffer(5) Pt FROM #Test
Distance is simple (say from the origin):
SELECT X, Y, POINT.STDistance(GEOMETRY::STGeomFromText('POINT(0 0)',0)) DistFromOrigin FROM #Test
X Y DistFromOrigin
----------- ----------- ----------------------
3 9 9.48683298050514
15 20 25
Between any two points is just a matter of selecting the points and doing
Point1.STDistance(Point2)
And for angles, this function should work fine when changed to the Geometry type