SpatialIndex Optimization

[Alex Mandel]

So I'm looking at the examples on
http://www.gaia-gis.it/spatialite/spatialite-2.2_tutorial.html#query2

and I'm trying to apply the tips using either the Rtree index or
MbrCache but can't seem to find a way to use the data from a second
table as the examples only use hard coded numbers.

What I'm trying to do is the inverse of the example where Towns are
selected if they are in a selected Region. I need to find out which
region each point is in.

I have a query that does this:
SELECT Towns.Name, Regions.Name FROM Towns,Regions
WHERE Within(Towns.Geometry,Regions2.Geometry)

But looking at my real data which is 50,000+ points over 4000+ polygons
and running a test, I calculated the query would take 6.6 hours to
complete. I'd really like to speed this up as it will need to be
repeated on more intensive datasets.

Here's as close as I've come getting all the pieces in place but it
doesn't even run:
SELECT Towns.Name, Regions.Name FROM Towns,Regions
WHERE ROWID
IN (SELECT rowid FROM cache_Regions_Geometry,
WHERE mbr = FilterMbrContains(box.minX, box.minY, box.maxX, box.maxY))
IN ((SELECT MbrMinX(Towns.Geometry) as minX ,MbrMinY(Towns.Geometry) as
minY, MbrMaxX(Towns.Geometry) as maxX, MbrMaxY(Towns.Geometry) as maxY
FROM towns) as box);

Any ideas, thanks?
Alex

[Alessandro Furieri]

Hi, Alex

in order to solve your problem you can use (at your choice)
one of the followings:

Solution A:
---------------------
build an RTree spatial index on Towns.Geometry,
and then use the following SQL query:

SELECT Towns.Name, Regions.Name
FROM Towns,Regions

WHERE Within(Towns.Geometry,Regions.Geometry)
AND Towns.ROWID IN
(
SELECT rowid
FROM idx_Towns_Geometry
WHERE xmin <= MbrMaxX(Regions.Geometry)
AND xmax >= MbrMinX(Regions.Geometry)
AND ymin <= MbrMaxY(Regions.Geometry)
AND ymax >= MbrMinY(Regions.Geometry)
)


Solution B:
---------------------
build an MBRcache spatial index on Towns.Geometry,
and then use the following SQL query:

SELECT Towns.Name, Regions.Name
FROM Towns,Regions

WHERE Within(Towns.Geometry,Regions.Geometry)
AND Towns.ROWID IN
(
SELECT rowid
FROM cache_Towns_Geometry
WHERE mbr = FilterMbrIntersects(
MbrMinX(Regions.Geometry),
MbrMinY(Regions.Geometry),
MbrMaxX(Regions.Geometry),
MbrMaxY(Regions.Geometry) )
)

bye,
Sandro Furieri