[SpatiaLite-Users] Examples of Intersects() that use spatial rtree indexes?

[DavidFawcett]

I am attempting to do a simple point-in-poly intersects exercise. I
have a points layer and a polygon layer, and I want to get the id of
the polygon that a point falls into.

Here is my query:
select pt.id, poly.id
from mypoints pt inner join mypolys poly on intersects(poly.geometry,
pf.geometry)
order by poly.id

This ran very quickly on hundreds of features. When I attempted it on
~40k points and ~2k polys, it takes a long time to run.

My goal is to make this run faster. I guess that really drives two
questions:

1. Is my basic SQL correct, or is it the best SQL for the problem?

2. How can I harness the spatial indexes that I created on each
layer?

Thank you,

David.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

[David Fawcett]

Sorry for the typo on the table alias.

> select pt.id, poly.id
> from mypoints pt inner join mypolys poly on intersects(poly.geometry,
> pf.geometry)
> order by poly.id

Should be:

select pt.id, poly.id
from mypoints pt inner join mypolys poly on intersects(poly.geometry,

pt.geometry)
order by poly.id

Thanks,

[David Fawcett]

I found the SQL test scripts at
http://www.gaia-gis.it/spatialite/test-sql-scripts-2.3.zip. These
definitely helped me rewrite my query to make it more efficient. The
query ran in seconds, although I am not sure if I benefited from any
caching of the query.

I would still appreciate a critique if anyone is willing to help me
optimize this.

The use case is where I have several polygon data sets that will be
static. I will be generating point data sets ranging from 1- ~30,000
points and then doing overlay operations with the different polygon
data sets to determine which polygon from each data set a point falls
into.

So, I want to do everything to pre-index and optimize the poly data
sets, and if necessary, index the point data set after creating it and
before running my queries.

Here is the latest query that I came up with:

SELECT pt.id, poly.id
FROM mypoints as pt, mypolys as poly
WHERE INTERSECTS(pt.geometry, poly.geometry)
AND pt.ROWID IN
(
SELECT pkid FROM idx_mypoints_Geometry
WHERE xmin > MbrMinX(poly.Geometry) AND
xmax < MbrMaxX(poly.Geometry) AND
ymin > MbrMinY(poly.Geometry) AND
ymax < MbrMaxY(poly.Geometry)
)

[a.furieri]

Hi David,

if you are interested in complex spatial queries
optimization on huge datasets, you can find several
useful hints on this previous thread:

http://groups.google.com/group/spatialite-users/browse_thread/thread/d8fdb839b2551cfe

bye Sandro