Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 Select All Points within Polygon Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 12:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 4,176, Visits: 10,769
Can you provide any DDL?

Look into STPointN and other Geography/Geometry functions.
Post #1554674
Posted Saturday, April 5, 2014 12:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 4,176, Visits: 10,769
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

Post #1558766
Posted Saturday, April 5, 2014 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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: 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 ....

Post #1558773
Posted Saturday, April 5, 2014 1:39 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 4,176, Visits: 10,769
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?
Post #1558778
Posted Saturday, April 5, 2014 1:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 4,176, Visits: 10,769
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

Post #1558791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse