locked
Query to find all points within a polygon RRS feed

  • 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 AM
    Answerer

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 AM
    Answerer
  • 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
  • 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
    Answerer
  • 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