March 25, 2014 at 12:30 am
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.
March 25, 2014 at 2:13 pm
Can you provide any DDL?
Look into STPointN and other Geography/Geometry functions.
April 5, 2014 at 12:19 pm
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 N3
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (@POLIGON.STNumPoints()) ROWS ONLY)
SELECT
NM.N
,@POLIGON.STPointN(NM.N) AS PPoint
FROM NUMBERS NM
April 5, 2014 at 1:11 pm
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: tblLatLongRecords
Fieldnames for where clause: Latitude, Longitude
Field names I need to get back: ID,Name
So on a regular statement we would do something like: SELECT ID,Name FROM tblLatLongRecords WHERE ....
April 5, 2014 at 1:39 pm
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: tblLatLongRecords
Fieldnames for where clause: Latitude, Longitude
Field names I need to get back: ID,Name
So 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?
April 5, 2014 at 1:50 pm
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.
April 5, 2014 at 3:14 pm
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 N3
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (20) ROWS ONLY)
INSERT INTO @LLTABLE (LAT,LON)
SELECT
LATN.N AS LATITUDE
,LONN.N AS LONGITUDE
FROM NUMBERS LATN, NUMBERS LONN;
SELECT
LT.POINT_ID
,LT.LAT
,LT.LON
FROM @LLTABLE LT
WHERE geography::STPointFromText(CONCAT
(
'POINT('
,CAST(LT.LON AS VARCHAR(12))
,CHAR(32)
,CAST(LT.LAT AS VARCHAR(12))
,')'
), 4326).STWithin(@POLIGON) = 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply