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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 AM
Points: 7, Visits: 17
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
Can you provide any DDL?

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 AM
Points: 7, Visits: 17
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 AM
Points: 7, Visits: 17
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
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
Posted Wednesday, May 14, 2014 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 1,945, Visits: 2,862
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 corners

CREATE 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 points
CREATE 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 query
SELECT 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 P1
GROUP 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 1

Sides are counted as inside, but if you want to count the corner points
as 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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1570940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse