finding a point inside a stored polygon

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

finding a point inside a stored polygon

Author

Topic

ddatta88
Starting Member

1 Post

Posted - 2011-02-16 : 06:20:30

in t-sql, is there any way for finding a point inside a polygon stored in sql server 2008 using geography data type?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-16 : 12:41:31

37.03. Points inside Polygons

While not actually part of graph theory, this seemed to be the reasonable place to put this section since it is also related to spatial queries. A polygon can be describes as a set of corner point in an (x, y) co-ordinate system. The usual query is to tell if a given point is inside of outside of the polygon.

This algorithm is due to Darel R. Finley. The main advantage it has is that it can be done in Standard SQL without trigonometry functions. The disadvantage is that it does not work for concave polygons. The work-around is to dissect the convex polygons into concave polygons, then add column for the name of the original area.

-- 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), (1.00, 4.00),
(3.00, 6.00), (6.00, 4.00), (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, -- answer the question in advance!
PRIMARY KEY (xx, yy));
INSERT INTO Points
VALUES (2.00, 2.00, 'corner'),
(1.00, 5.00, 'outside'),
(3.00, 3.00, 'inside'),
(3.00, 4.00, 'inside'),
(5.00, 1.00, 'outside'),
(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 you should start the CASE expression with:

CASE WHEN EXISTS
(SELECT * FROM Polygon
WHERE x = P1.xx AND y = P1.yy)
THEN 1 ..".

--CELKO--
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources