Monday, October 17, 2016

Example efficient two pass Spatialite query to find if a point is inside a polygon

A spatial query can be expensive to run in terms of CPU processing power. In order to improve the query performance, typically, a first pass query is done to reduce the candidates first before running a second pass query to do actual spatial query. The following is an example using SpatiaLite to perform a two pass spatial query to find whether a point is contained within a polygon.
Point 1 is totally outside of any polygons; Point 2 is outside but within a bounding rectangle of a polygon; Point 3 is totally inside a polygon.
The two pass SpatiaLite query has the following syntax.
Note: the first approximate query uses the MBRCONTAINS function and the second finer query uses the ST_CONTAINS function.
SELECT ST_CONTAINS(polygonGeometry, geomfromtext('POINT(116.4688 -31.5502)', 4326))
FROM(
SELECT
polygonGeometry,
MBRCONTAINS(polygonGeometry, geomfromtext('POINT(116.4688 -31.5502)', 4326)) AS inMBR
FROM
(
SELECT geometry AS polygonGeometry FROM MyPolygon
)
WHERE inMBR = 1
)



Case 1: Totally outside 
For Point 1 (Totally outside), the query returns no rows since the point is outside of any bounding rectangles, as shown in the screenshot below.


Case 2: Outside of any polygons but within a bounding rectangle
For Point2, the query returns a result of 0 value, since the point is inside a bounding rectangle but outside of any polygon.

Case 3: Totally inside a polygon
For Point 3, the query returns a result of 1 since the point is inside a bounding rectangle and also inside a polygon.


No comments: