[postgis-users] error in geometry operations

P Kishor punkish at eidesis.org
Sun Aug 20 15:32:28 PDT 2006


On 8/20/06, Paul Ramsey <pramsey at refractions.net> wrote:
> Versions of software?

Postgres 8.1.4/PostGIS 1.1.3, WinXP SP2

> Don't forget to run VACUUM ANALYZE after creating your indexes.

Did that.

> How much stuff in your tables? How many a's in b's?
 a couple of gigs of data...
a: 5,115,110 rows (POINT)
b: 210,763 rows (POLY)

> What does the EXPLAIN output of this look like?

rather long, but healthy... I am including below the last few lines
(would be happy to send the whole shebang separately, if required).

Limit  (cost=2.09..22311847.95 rows=1 width=20)
  ->  Nested Loop  (cost=2.09..22311847.95 rows=1 width=20)
        Join Filter: within("outer".the_geom, "inner".the_geom)
        ->  Seq Scan on b  (cost=0.00..38507.71 rows=210571 width=2631)
        ->  Bitmap Heap Scan on a  (cost=2.09..105.39 rows=26 width=29)
              Filter: (a.the_geom && "outer".the_geom)
              ->  Bitmap Index Scan on idx_a_the_geom
(cost=0.00..2.09 rows=26 width=0)
                    Index Cond: (a.the_geom && "outer".the_geom)


> On 20-Aug-06, at 3:12 PM, P Kishor wrote:
>
> > -- Executing query:
> > SELECT  a.id, b.id
> > FROM    a, b
> > WHERE   a.the_geom && b.the_geom AND WITHIN(b.the_geom, a.the_geom)
> > LIMIT 10
> >
> > oh crap! row number -1 is out of range 0..-1
> > Total query runtime: 360258 ms.
> >
> >
> > any ideas, anyone?
> >
> > additionally, for a query like above, what all indexes are advised? I
> > have the_geom in both tables indexed using GiST, fwiw.


-- 
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/



More information about the postgis-users mailing list