Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Server 2008 Select All Points within Polygon Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 25, 2014 12:30 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, October 2, 2014 1:59 PM Points: 10, Visits: 21
 I have a table which hold records that contain all the lat/long points. I want to select all records that are within a polygon. Any help with the actual SQL statement I need would be greatly appreciated.
Post #1554288
 Posted Tuesday, March 25, 2014 2:13 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:20 AM Points: 2,533, Visits: 7,115
 Can you provide any DDL?Look into STPointN and other Geography/Geometry functions.
Post #1554674
 Posted Saturday, April 5, 2014 12:19 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:20 AM Points: 2,533, Visits: 7,115
 Here is the code `DECLARE @POLIGON GEOMETRY; /* ms sample from BOL */SET @POLIGON = geometry::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);;WITH NN1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N)),NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM NN1 N1,NN1 N2, NN1 N3ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (@POLIGON.STNumPoints()) ROWS ONLY)SELECT NM.N ,@POLIGON.STPointN(NM.N) AS PPointFROM NUMBERS NM`
Post #1558766
 Posted Saturday, April 5, 2014 1:11 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, October 2, 2014 1:59 PM Points: 10, Visits: 21
 SSC thank you for your reply. I just tried using this in my table and am a bit confused. If my table structure is like this, what would my query be like:Table name: tblLatLongRecordsFieldnames for where clause: Latitude, LongitudeField names I need to get back: ID,NameSo on a regular statement we would do something like: SELECT ID,Name FROM tblLatLongRecords WHERE ....
Post #1558773
 Posted Saturday, April 5, 2014 1:39 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:20 AM Points: 2,533, Visits: 7,115
 ado-712642 (4/5/2014)SSC thank you for your reply. I just tried using this in my table and am a bit confused. If my table structure is like this, what would my query be like:Table name: tblLatLongRecordsFieldnames for where clause: Latitude, LongitudeField names I need to get back: ID,NameSo on a regular statement we would do something like: SELECT ID,Name FROM tblLatLongRecords WHERE ....Slightly different thing, the query lists out all points defining a polygon, but you are asking for all points in a list which are inside a polygon, so WHERE....is the polygon?
Post #1558778
 Posted Saturday, April 5, 2014 1:50 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, October 2, 2014 1:59 PM Points: 10, Visits: 21
 The idea is that a user using the Google maps defines a polygon and we want to show all the records that are within that polygon.Thank you once again.
Post #1558781
 Posted Saturday, April 5, 2014 3:14 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:20 AM Points: 2,533, Visits: 7,115
 This code creates a polygon and 200 points (lat/lon) and then selects the points that are inside the polygon`DECLARE @POLIGON geography; SET @POLIGON = geography::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 4326);DECLARE @LLTABLE TABLE( POINT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL ,LAT FLOAT NULL ,LON FLOAT NULL);WITH NN1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N)),NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM NN1 N1,NN1 N2, NN1 N3ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (20) ROWS ONLY)INSERT INTO @LLTABLE (LAT,LON)SELECT LATN.N AS LATITUDE ,LONN.N AS LONGITUDEFROM NUMBERS LATN, NUMBERS LONN;SELECT LT.POINT_ID ,LT.LAT ,LT.LONFROM @LLTABLE LTWHERE geography::STPointFromText(CONCAT ( 'POINT(' ,CAST(LT.LON AS VARCHAR(12)) ,CHAR(32) ,CAST(LT.LAT AS VARCHAR(12)) ,')' ), 4326).STWithin(@POLIGON) = 1`
Post #1558791
 Posted Wednesday, May 14, 2014 10:26 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 11:25 AM Points: 1,945, Visits: 3,183
 Years ago on Compuserve, we did a thread on polygons and points. Here is an old clipping. Google the winding number and be ready to try to remember geometry.-- set up polygon, with any ordering of the cornersCREATE TABLE Polygon(x FLOAT NOT NULL, y FLOAT NOT NULL, PRIMARY KEY (x,y));INSERT INTO Polygon VALUES (2.00, 2.00);INSERT INTO Polygon VALUES (1.00, 4.00);INSERT INTO Polygon VALUES (3.00, 6.00);INSERT INTO Polygon VALUES (6.00, 4.00);INSERT INTO Polygon VALUES (5.00, 2.00);--set up some sample pointsCREATE TABLE Points(xx FLOAT NOT NULL, yy FLOAT NOT NULL, location VARCHAR(10) NOT NULL, PRIMARY KEY (xx,yy));INSERT INTO Points VALUES (2.00, 2.00, 'corner');INSERT INTO Points VALUES (1.00, 5.00, 'outside');INSERT INTO Points VALUES (3.00, 3.00, 'inside');INSERT INTO Points VALUES (3.00, 4.00, 'inside');INSERT INTO Points VALUES (5.00, 1.00, 'outside');INSERT INTO Points VALUES (3.00, 2.00, 'side');-- do the querySELECT P1.xx, P1.yy, p1.location, SIGN(SUM (CASE WHEN (polyY.y < P1.yy AND polyY.x >= P1.yy OR polyY.x < P1.yy AND polyY.y >= P1.yy) THEN CASE WHEN polyX.y + (P1.yy - polyY.y) /(polyY.x - polyY.y) * (polyX.x - polyX.y) < P1.xx THEN 1 ELSE 0 END ELSE 0 END))AS flag FROM Polygon AS polyY, Polygon AS polyX, Points AS P1GROUP BY P1.xx, P1.yy, p1.location;When flag = 1, the point is inside, when flag = 0, it is outside. xx yy location flag ======================== 1.0 5.0 outside 0 2.0 2.0 corner 0 3.0 3.0 inside 1 3.0 4.0 inside 1 5.0 1.0 outside 0 3.0 2.0 side 1Sides are counted as inside, but if you want to count the corner pointsas inside, then start the CASE expresion with: CASE WHEN EXISTS (SELECT * FROM Polygon WHERE x = P1.xx AND y = P1.yy) THEN 1 ..". Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1570940

 Permissions