I am using SQL Server 2012. I have a table where I am tracking single point instances in a geography column. Storing them as a single point is working fine but I am trying to group some of them together into a new table where they would be a multipoint. I can get it working by inserting lat and long into a multipoint column like this:
DECLARE @g geography;
SET @g = geography::STMPointFromText('MULTIPOINT(-104.952784 39.524092, -104.935269 39.542652)', 4326);
INSERT INTO test(loc) values(@g)
What I want to do is select the values from a table that are already a geography data type. I am not sure if I can do this with a basic query or if I have to build it with a loop? I cannot seem to get it working either way.
Also, after I have that is there a method that will return me the center point of a multipoint column? I have been playing with some of the methods like STStartPoint and STEndpoint but I cannot seem to find a methods that return the center point?
Any help on these questions would be great and highly appreciated.
Thanks!
What you're looking for is the STCentroid() method but, unfortunately, it doesn't work for MultiPoint objects. I raised a Connect issue for this a few years back which has been closed by Microsoft as "Won't Fix", but you're welcome to vote it up anyway: https://connect.microsoft.com/SQLServer/feedback/details/588316/make-geometry-stcentroid-method-work-on-geometries-other-than-polygons
Meanwhile, you'll have to manually sum and then average the X and Y coordinate values individually to get the average "centre" of a Multipoint.