Tuesday, February 10, 2009 7:33 PMHey guys; hopefully this is a simple one. I'm just starting to work with spatial datatypes in sql server, and wanted to know if there is a simple way (short of string operations) to create geometry polygon from existing coordinate pairs.
I have a table like this:
And I'd like to be able to create a polygon, like what I'm seeing in the examples, like this:
But instead of those (hardcoded string) values, I'd like to build it based on the lat and lon coordinates from my Locations table. There are multiple locations per ID, hence why I need to build a polygon for one ID.
Tuesday, February 10, 2009 7:47 PMOkay I read a little more, and all I'm really trying to do is determine if a point is within an array of locations. They will be orderless, and will look more like a wall with five hundred darts thrown at it. What I'm trying to do (in my mind) is put a string around the farthest points, creating a polygon made up of the furthest points, and then determine if the point is in the center of the string.
I'll keep hunting and pecking; perhaps I'm looking for the wrong shape.
Tuesday, February 10, 2009 8:22 PMModeratorHi Eric,
I think you're looking for STConvexHull. To see it in action, try something like:
declare @g geometry = 'multipoint((10 10),(4 10), (3 -2), (7 16))'The one complicating thing is that you don't have a multipoint, you have a table of points, so you'll need to union them together first. You can write a union aggregate using SQL CLR---or you can download the SQL Server Spatial Tools project and use the union aggregate contained in it.
Isaac Kunen, Microsoft SQL Server
Tuesday, February 10, 2009 10:09 PMAnswererTo elaborate on Isaac's answer a bit more, you can also achieve the same effect using pure T-SQL as follows:
Let's suppose your source table looks a bit like this:
To create the convex hull (the polygon formed by the rubber band wrapped around the farthest points) of these values, you first need to use the STUnion() method to combine these separate points into a single MultiPoint instance. You can write a CLR UDF to do this, or you can use a cursor, as follows:
The @MultiGeom variable now contains the following multipoint instance, representing each of the source latitude and longitude values in the original #Points table:
Now, to create the convex hull of these points, you use STConvexHull() as follows:
@ConvexHull is the blue polygon shown as follows:
Finally, to test whether another point lies within this convex hull polygon, you can use the STWithin() method, as follows:
The result, 1, confirms that @TestPoint lies within the convex hull.
Tuesday, February 10, 2009 11:33 PMYou guys are awesome. Seriously.
Okay, I made some progress, and I think I'm getting close. Rather than get cheap and rebuild geometry data all the time I just created an extra pair of columns on my location table. One for geometry, and one for geography. The insert procedure just converts the inbound lon/lat values into the respective geography and geometry datatypes and puts them in with the rest of the insert.
And I see the neat-o "Spatial results" tab which now, which is the cats meow. However, I'm getting a blue box (rectangle) rather than the nifty ConvexHull. It's creating the rectangle around the farthest points, but that's not really what I'm expecting. Any ideas on what I'm doing wrong here?
Now that I'm looking at it... is it the GeometryEnvelopeAggregate? I know above it was recommended that I do a union, unless I'm missing something geography is for unions, and convexhulls are for geometry, and geometry is for evelopes. Man this stuff is intense!
Wednesday, February 11, 2009 12:03 AMModeratorHi Eric,
Happy to help!
Yeah, I think the GeometryEnvelopeAggregate is getting in your way. You want to do two steps: (1) find a single object that represents all of the things you want to find a convex hull around, and (2) compute the convex hull of this aggregated object.
(1) is done by a union, either through the CLR or through T-SQL like what Tanoshimi posted. The name GeometryEnvelopAggregate is a little obtuse: it calculates a bounding box around the collection of geometries. This bounding box will be bigger than the convex hull you're looking for---and the convex hull of this envelope will be the envelope itself.
To see what's going on here, try taking the aggregate out of the equation. For example, look at the difference between the results of these two calls:
declare @g geometry = 'linestring (0 0, 8 2, 2 4, 10 10)' select @g.STConvexHull() select @g.STEnvelope()
Isaac Kunen, Microsoft SQL Server
Wednesday, February 11, 2009 8:17 AMAnswererOf course, if you're feeling super-helpful, you could always write a GeometryConvexHullAggregate function and contribute it back to the SQL Server Spatial Tools project :)