SQL Server spatial query, part 1

A traditional GIS analysis question - Does the polygon contain the point, is the point inside the polygon? There are lots of ways to use a GIS application to find the answer to this question, but let’s take a look at a few ways SQL Server Spatial can help us with this traditional exercise using a SQL Server spatial query.

The Setup

The first step is to have some polygons/points to test with. In the query example, we will create a polygon (@thePolygon), a point located inside the poly (@pointIN), and a point located outside the poly (@pointOUT). Although this article doesn’t get into detail on the GEOGRAPHY/GEOMETRY types or spatial references - it should be noted that in the first example I changed the standard WGS84 Geographic spatial reference (4326), to use NAD83 Geographic (4269) instead. No real reason, just wanted to mention in case you compare with other samples.

DECLARE @thePolygon GEOGRAPHY, @pointIN GEOGRAPHY, @pointOUT GEOGRAPHY;
SET @thePolygon = GEOGRAPHY::STGeomFromText('POLYGON((-78.50932668617881 45.024933647425115, -78.53403351361905 44.9898648154388, -78.48446979547693 44.97239241709962, -78.45973073293072 45.007441690111115, -78.50932668617881 45.024933647425115))', 4269);
SET @pointIN = GEOGRAPHY::STGeomFromText('POINT(-78.51 45.00)', 4269);
SET @pointOUT = GEOGRAPHY::STGeomFromText('POINT(-65.00 35.00)', 4269);

The SQL Server Spatial Query

Now that the features are created, we just need to use SQL Server’s intersect function (STIntersection) to perform the analysis.

Example 1 - where the point falls inside the polygon:
Query: SELECT @pointIN.STIntersection(@thePolygon).ToString();
Returns: POINT (-78.51 45)

Example 2 - where the point falls outside the polygon:
Query: SELECT @pointOUT.STIntersection(@thePolygon).ToString();
Returns: GEOMETRYCOLLECTION EMPTY

Example 3 - Reversing the order of the Base and parameter in the analysis:
Query: SELECT @thePolygon.STIntersection(@pointIN).ToString();
Returns: POINT (-78.51 45)

When the point is inside the polygon, it is returned by the query, otherwise, an empty geometry is returned. Notice how the result in example 3 returns the same result as Example 1, regardless of which feature calls STIntersection. This is because the STIntersection function only returns the points that are common between the two shapes. In this case, only the one point is common. If we intersected two partially overlapping polygons, the output would return a new polygon of the overlap area.

Aside: Some SQL Server spatial functions can even return a collection of geometries. For example, a union of a line feature and a partially overlapping polygon feature will return a collection containing both lines and polygons.

Here is a full SQL Server spatial script for Enterprise Manager:

Coming soon: Part 2 - Create a SQL procedure to evaluate if an input feature intersects a reference layer. Gives me time to think of a shorter title. Update 2014-09-13: SQL Server spatial query, part 2.