On 8/26/06, Pablo Santacruz <[EMAIL PROTECTED]> wrote:
Try something like this:
CREATE INDEX ix_polys ON polys (xmin, ymin, xmax, ymax);
SELECT name
FROM polys
WHERE EXISTS
(SELECT x FROM points WHERE point_id = 1 AND xmin < x AND ymin < y AND xmax
> x AND ymax > y);
In your query you do 4 select from points table. Here, you do only one. I
think this should be an important improvement because table points has 5M
records.
Thanks for the suggestion. I actually ended up doing like so
SELECT py.name
FROM polys py JOIN points pt ON
(py.xmin < pt.x AND py.ymin < pt.y AND py.xmax > pt.x AND py.ymax > pt.y)
WHERE py.id = ?
It works quite well. In any case, I also did some benchmark tests, and
the 4 selects that I showed earlier -- they don't really perform
badly.
By the way, if you can, it is faster to create the index after you have
inserted all record in the DB. And it also consume less storage in disk.
Yes, I am aware of the speed hit because of indexing.
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
