Query Spatialite for all features that pass in over or through an extent

Question

I have a Spatialite Database created with QGIS which contains 200000+ multipolygons with more points that I care to count. I am looking to only load the polygons that are needed for the current viewport extent.

I have the xmin, xmax, ymin, ymax but I cant seem to figure out how to properly form the query to retrieve all polygons that pass partially over the given extent. Below is a quick tossed together example using all of my variables (not working or tested). I am doing these queries using express, sqlite, spaitalite and nodejs if you are wondering about the code structure.

var query = 'SELECT ogc_fid,pkid,xmin,xmax,ymin,ymax, AsText(GEOMETRY) as geom '
+'FROM idx_layer_GEOMETRY '
+'INNER JOIN layer ON pkid = ogc_fid '
+'WHERE '
+'( '+req.query.xmin+' < xmin '
+'AND '+req.query.xmax+' > xmax '
+'AND '+req.query.ymin+' < ymin '
+'AND '+req.query.ymax+' > ymax )'

Answer 1

The WHERE-condition in your example will only return those geometries that are completely within the bounding box as expressed by req.query. That is to say: for every GEOMETRY in the table, both min and max on each axis (x and y) are asked be entirely within the limits (x and y respectively) of the requested bounding box.

To include partially overlapping objects, the WHERE-condition would have to be:

+'WHERE '
+'( '+req.query.xmin+' < xmax '
+'AND '+req.query.xmax+' > xmin '
+'AND '+req.query.ymin+' < ymax '
+'AND '+req.query.ymax+' > ymin )'

(in other words: after the greater/lesser-then symbols the xmin/xmax and ymin/ymax variables needed to be switched).

An shorter alternative (not translated to your code) would be:

WHERE ST_Intersects(GEOMETRY, BuildMbr(req.query.xmin, req.query.ymin, req.query.xmax, req.query.ymax))

or, to make use of a spatial index in your spatialite database:

WHERE search_frame = BuildMbr(req.query.xmin, req.query.ymin, req.query.xmax, req.query.ymax)

where the outcome of the BuildMbr() function is a Polygon containing a rectangle. The function will accept an SRID as the fifth parameter.

The last example, using "search_frame", may however not be what you want because it can return objects that are not actually overlapping with the requesting bounding box. For example: some "L-shaped" object that runs tightly around a corner of the requesting bounding box, without actual overlap, will still be returned because the rectangle around that L-shaped object is large enough to overlap with the requesting bounding box. As a means of limiting the amount of data is it may still be useful though.