I manage a sql express database (using v17.9.1 SSMS) containing animal records (species names with geographic coords). The names and locations of species are changing frequently and I want to use SSMS to calculate the maximum linear distance for all species (in kilometres). I can do this manually in ArcMap by creating minimum bounding rectangles grouped by species name, changing the CRS to projected and calculating the lengths of the rectangles in metres, but this is done separately to the database and will not changed when the database is changed. I recently discovered geometry and geography data types and spatial functions like convexhullaggregate in sql express, which leads to believe I can do this in sql express.

The answer might lie in the answer to this question Calculate Convex Hull of points from a set of (lat/long) points, but I need some help getting it to work and am unsure if this is the right path to follow. I think it makes more sense to create a rectangle around a set of points because finding the longest edge of a rectangle should be straightforward. Anyway, here is my progress using the above example, starting with the geometry coordinates field 'position' and a field from the same table '_fldProjectID' that I will later substitute for the species name field (which lives in another table):

```
with cte as (
SELECT _fldProjectID, geometry::ConvexHullAggregate(position) AS Hull
FROM tblsite
where position is not null
group by _fldProjectID
)
select _fldProjectID, Number, Edge.Long as Long, Edge.Lat as Lat
from cte
cross apply (
select Number, Hull.STPointN(Number) as Edge
from dbadmin.dbo.Numbers
where Number < Hull.STNumPoints()
) as HullEdges
```

Currently, SSMS does not recognise 'Edge' and 'Number', and I am unsure what to replace 'dbadmin.dbo.numbers' with. After this I can see many examples on how to calculate the distance using STDistance, e.g. Calculating distance between two points (Latitude, Longitude)

Hope someone can help,

Mike

Posted in S.E.F

via

**StackOverflow & StackExchange Atomic Web Robots**

## No comments:

Post a Comment