Answered by:
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
Answers

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 Cstolworthy Thursday, March 12, 2009 2:16 PM
Wednesday, March 11, 2009 8:26 AMAnswerer
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 Cstolworthy Thursday, March 12, 2009 2:16 PM
Wednesday, March 11, 2009 8:26 AMAnswerer 
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.OpenGisWktReader.ParsePoint()
at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointTaggedText()
at Microsoft.SqlServer.Types.OpenGisWktReader.ParseGeometryTaggedText()
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 Cstolworthy Thursday, March 12, 2009 2:16 PM
 Unmarked as answer by Cstolworthy 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

Coordinateordering 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 coordinateordering 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/1430218290Thursday, March 12, 2009 7:40 AMAnswerer 
Cstolworthy said:Note that (Longitude, Latitude) ordering actually correlates with the concept of (X, Y) ordering on the plane, not (Y, X).
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.
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