2008 geometry question

Question

Hey 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:

create table Locations

(   Lat decimal(18,15),

Lon decimal(18,15),

Id  int

)

And I'd like to be able to create a polygon, like what I'm seeing in the examples, like this:

DECLARE @poly geometry = 'POLYGON ((10 10, 13 30, 30 30, 30 15, 10 10))'

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.

Any suggestions?

Answer 1

Okay 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.

Answer 2

Hi 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))'
select @g.STConvexHull()

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.

Cheers,
-Isaac

---

Isaac Kunen, Microsoft SQL Server

Answer 3

To 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:

CREATE TABLE #Points
(
Lat decimal(18,15),
Lon decimal(18,15),
Id  int
)
GO
INSERT INTO #Points VALUES
(50, 0, 1),
(52, 0, 1),
(52, 2, 1),
(50, 2, 1),
(49, 1, 1),
(51, 2.5, 1),
(51, 1, 1),
(51, 0.5, 1),
(51.5, 1.5, 1)
GO

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:

DECLARE @Geom geometry

DECLARE @MultiGeom geometry =

geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', 0)

DECLARE GeomCursor CURSOR FOR SELECT geometry::Point(Lat,Lon,0) FROM #Points

OPEN GeomCursor

FETCH NEXT FROM GeomCursor INTO @Geom

WHILE @@FETCH_STATUS = 0

BEGIN

SET @MultiGeom = @MultiGeom.STUnion(@Geom)

FETCH NEXT FROM GeomCursor INTO @Geom

END

CLOSE GeomCursor

DEALLOCATE GeomCursor

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:

DECLARE @ConvexHull geometry = @MultiGeom.STConvexHull()

@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:

DECLARE @TestPoint geometry = geometry::Point(50.5,1.5,0)

SELECT @TestPoint.STWithin(@ConvexHull)

The result, 1, confirms that @TestPoint lies within the convex hull.

Answer 4

 You 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?

DECLARE

@MultiGeo geometry,

@ConvexHull geometry,

@TestPoint geometry

SET @MultiGeo =

(select dbo.GeometryEnvelopeAggregate(GeometryColumn)

FROM    dbo.Locations

group by LocationID)

SET @ConvexHull = @MultiGeo.STConvexHull();

SET @TestPoint = geometry::Point(-117.4,32.55,0)

SELECT

@ConvexHull as ConvexHull,

@TestPoint as TestPoint,

@TestPoint.STWithin(@ConvexHull) as Within

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!

Answer 5

Hi 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()

Cheers,
-Isaac

---

Isaac Kunen, Microsoft SQL Server

Answer 6

Of 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 :)