Ggtcf

Female=gender counterpart?

Partial sums of primes

Did US corporations pay demonstrators in the German demonstrations against article 13?

Pronouncing Homer as in modern Greek

What if somebody invests in my application?

Can I rely on these GitHub repository files?

Why are on-board computers allowed to change controls without notifying the pilots?

Lifted its hind leg on or lifted its hind leg towards?

Reply ‘no position’ while the job posting is still there (‘HiWi’ position in Germany)

Why does this part of the Space Shuttle launch pad seem to be floating in air?

How can I successfully establish a nationwide combat training program for a large country?

Lightning Web Component - do I need to track changes for every single input field in a form

How to prevent YouTube from showing already watched videos?

For airliners, what prevents wing strikes on landing in bad weather?

Is exact Kanji stroke length important?

Could solar power be utilized and substitute coal in the 19th century?

Do all polymers contain either carbon or silicon?

Have I saved too much for retirement so far?

Teaching indefinite integrals that require special-casing

Is there enough fresh water in the world to eradicate the drinking water crisis?

Is a naturally all "male" species possible?

What does the "3am" section means in manpages?

Perfect riffle shuffles

The most efficient algorithm to find all possible integer pairs which sum to a given integer

Find the length of a bounding rectangle polygon

Calculating distance between two points (Latitude, Longitude)Calculate Convex Hull of points from a set of (lat/long) pointsFinding duplicate values in a SQL tableHow do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?Select statement to find duplicates on certain fieldsFind all tables containing column with specified name - MS SQL ServerHow to change default database from SQL Server 2005 to SQL Server 2008Sql Server spatial data type Geometry, STDistance and units : Get meters?SQL query finding entries that lies close to each otherFind distance between two latitude and longitude in SQL querySQL Server spatial feature: .STDistance Does it return miles or meters?Can you execute WHERE clauses in a PostgreSQL stored procedure IF clause?

0

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

sql sql-server ssms

share|improve this question

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey! As the author of the linked to answer, I can tell you that dbadmin.dbo.Numbers is just a numbers table (also called a "tally table"). There are a lot of methods for making one, a lot of them collected at sqlservercentral.com/blogs/dwainsql/2014/03/27/…. One note here is that a convex hull probably won't be a rectangle (or even a regular polygon). So if that's a requirement, this approach probably won't get you what you're looking for.
  
  – Ben Thul
  Mar 8 at 17:19
  

  
  
  
  

add a comment | 

0

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

sql sql-server ssms

share|improve this question

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey! As the author of the linked to answer, I can tell you that dbadmin.dbo.Numbers is just a numbers table (also called a "tally table"). There are a lot of methods for making one, a lot of them collected at sqlservercentral.com/blogs/dwainsql/2014/03/27/…. One note here is that a convex hull probably won't be a rectangle (or even a regular polygon). So if that's a requirement, this approach probably won't get you what you're looking for.
  
  – Ben Thul
  Mar 8 at 17:19
  

  
  
  
  

add a comment | 

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

sql sql-server ssms

share|improve this question

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

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

sql sql-server ssms

sql sql-server ssms

share|improve this question

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

share|improve this question

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

edited Mar 8 at 8:16

jarlh

29.8k52138

edited Mar 8 at 8:16

jarlh

29.8k52138

asked Mar 8 at 8:13

MikeMike

1

asked Mar 8 at 8:13

MikeMike

1