You can create an STRtree
, which is a query-only R-tree created using the Sort-Tile-Recursive (STR) algorithm for two-dimensional spatial data. More details are at the documentation page of JTS.
To create a STRtree index in NTS,
ISpatialIndex spatialIndex = new STRtree();
You would insert geometries into the index using the geometry's envelope,
IGeometry geometry = WktReader.Read(//linestring WKT);
spatialIndex.Insert(geometry.EnvelopeInternal, geometry);
Now you can retrieve the items whos bounds intersect an envelope using,
IGeometry boundaryGeom = WktReader.Read(//polygon Wkt);
Envelope envelope = inProcessGeometry.EnvelopeInternal;
IList<object> intersectingObjects = spatialIndex.Query(envelope);
Effectively forcing the planner to do the thing you want might help. In this case, sub-setting the polygon table prior to executing the spatial join with the points table. You might be able to outwit the planner using "WITH" syntax:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
)
SELECT *
FROM polys JOIN data ON ST_Intersects(data.point, polys.polygon)
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19');
The trouble with trying to play these games is that you are coding into your statement the assumption "my polygon list will always be more selective than my other query portions". Which might not be true for all parameterizations of your query, or for all applications of a particular query over a heterogeneously distributed dataset.
But it might work.
UPDATE: This goes even further down the dangerous road of assuming you know the selectivity of your clauses beforehand, this time we also take the attribute selection on the point table out and do it separately before the spatial join:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
),
WITH points AS (
SELECT * FROM data
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19')
)
SELECT *
FROM polys JOIN points ON ST_Intersects(points, polys.polygon);
Best Answer
There are a couple of reasons why what you're trying to do here won't work.
Quadtree is similar to STRTree and does support on-the-fly insert / remove operations. It's slower, but still OK in practice.
However, all that aside, is there some reason why you need the actual Point instead of just Coordinates? If Coordinate is OK or if you're fine with creating Point objects later on, then Dictionary<Coordinate, int> just seems like a much simpler solution than a spatial index.