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
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:59 AM Points: 6,545, Visits: 17,215
 Can you provide any DDL?Look into STPointN and other Geography/Geometry functions.
Post #1554674
 Posted Saturday, April 5, 2014 12:19 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:59 AM Points: 6,545, Visits: 17,215
 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:59 AM Points: 6,545, Visits: 17,215
 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:59 AM Points: 6,545, Visits: 17,215
 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

 Permissions