# Query to find all points within a polygon

• ### Question

• I am very new to the spatial data, and I have been spending quite a bit of time playing with the different aspects of it.  One thing that I am not quite sure how to do is this:

I have one table filled with points (lat,lon, and another table filled with polygons.
I want to grab a specific polygon and query for all points that fall within that polygon.  I am not quite sure how I would formulate the query to do this.  Any help is greatly appreciated.
Wednesday, March 11, 2009 3:54 AM

• Hi there,

So, assuming your data looks something like this:
 declare @polygons table( name varchar(32), shape geometry ) insert into @polygons values ('square', geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))', 0)), ('triangle', geometry::STGeomFromText('POLYGON((3 1, 7 1, 5 4, 3 1))', 0)), ('circle', geometry::STGeomFromText('POINT(4 4)',0).STBuffer(2)) declare @points table( id int identity(1,1), position geometry ) insert into @points values (geometry::Point(1,1,0)), (geometry::Point(2,3,0)), (geometry::Point(5,4,0)), (geometry::Point(4,2,0))

So,
 select shape from @polygons union all select position from @points

looks a bit like this:

To select just those points that are contained within the square, you can do:
 SELECT * FROM @points WHERE position.STWithin((SELECT shape FROM @polygons WHERE name='square'))=1

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
• Marked as answer by Thursday, March 12, 2009 2:16 PM
Wednesday, March 11, 2009 8:26 AM

### All replies

• Hi there,

So, assuming your data looks something like this:
 declare @polygons table( name varchar(32), shape geometry ) insert into @polygons values ('square', geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))', 0)), ('triangle', geometry::STGeomFromText('POLYGON((3 1, 7 1, 5 4, 3 1))', 0)), ('circle', geometry::STGeomFromText('POINT(4 4)',0).STBuffer(2)) declare @points table( id int identity(1,1), position geometry ) insert into @points values (geometry::Point(1,1,0)), (geometry::Point(2,3,0)), (geometry::Point(5,4,0)), (geometry::Point(4,2,0))

So,
 select shape from @polygons union all select position from @points

looks a bit like this:

To select just those points that are contained within the square, you can do:
 SELECT * FROM @points WHERE position.STWithin((SELECT shape FROM @polygons WHERE name='square'))=1

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
• Marked as answer by Thursday, March 12, 2009 2:16 PM
Wednesday, March 11, 2009 8:26 AM
• Thank you that looks like exactly the query I need.  However when I started to input some test data I got this exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'geography':
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException:
at Microsoft.SqlServer.Types.LatLongValidatingGeometryDataBuilder.ValidateXCoordinate(Double d)
at Microsoft.SqlServer.Types.GeometryDataBuilder.AddPoint(Double x, Double y, Boolean hasZ, Double z, Boolean hasM, Double m)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)

Here is the insert statement and the table structure:
--CREATE TABLE Polygons(
--name varchar(32),
--shape geography
--)
--GO
--
--CREATE TABLE Properties(
--id int identity(1,1),
--name nvarchar(max),
--loc geography
--)

INSERT into Properties (Name, loc) VALUES ('West_Valley', geography::STGeomFromText('POINT(-111.95617675781251 40.69306473790602)', 4326))

I was kind of surprised because I know that my latitude and longitude are correct.
Also is it possible to do this query if the Properties table was simply storing the latitude and longitude, but did not have a Point object?

• Marked as answer by Thursday, March 12, 2009 2:16 PM
• Unmarked as answer by Thursday, March 12, 2009 2:16 PM
Thursday, March 12, 2009 3:07 AM
• I found something online that says that coordinates must be supplied in a Y,X format (or longitude, latitude format)?  Switching to this seemed to work, but I am confused if this is actually true or not.
Thursday, March 12, 2009 3:41 AM
• Coordinate-ordering for any of the STxxxxFromText() methods is as follows:
- For the geometry datatype: (X,Y)
- For the geography datatype: (Long, Lat)

What you've written above is correct to describe a point in Salt Lake City using the geography datatype.

What version of SQL Server are you using? Is it one of the CTP releases? If so, the coordinate-ordering was changed shortly before release, so you'll want to upgrade to the full RTM version. (an easy way of telling is to execute SELECT * FROM Properties - do you get a spatial results tab that shows a graphical output of the query?)

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Thursday, March 12, 2009 7:40 AM
• Cstolworthy said:

I found something online that says that coordinates must be supplied in a Y,X format (or longitude, latitude format)?  Switching to this seemed to work, but I am confused if this is actually true or not.

Note that (Longitude, Latitude) ordering actually correlates with the concept of (X, Y) ordering on the plane, not (Y, X).

This fact has also helped me keep straight in my mind which dimension is which, because before the "Great WKT Ordering Change of 2008", I always got them confused... ;-).

Thursday, March 12, 2009 3:23 PM