[postgis-users] error in geometry operations

[P Kishor]

On 8/20/06, Paul Ramsey <pramsey at refractions.net> wrote:
> I think this is just an issue of size, you have at least 200K rows on
> one side of the point, and depending on cross-matching, 5M on the far
> side... it's the size of the poly side which worries me.  If you have
> a gid on your poly table, try something like
>
> SELECT  a.id, b.id
> FROM    a, b
> WHERE   a.gid < 100 AND a.the_geom && b.the_geom AND WITHIN
> (b.the_geom, a.the_geom)
>
> And see if it's fast.

Yes, that is fast, however, instead of batching it up, I will work on
config tuning PostGres. Thanks for the link.

That said, I think I have bigger problems. Actually, the query above
didn't return anything at all, which should not be the case. The
points (a table) is exactly the same extent as the polys (the b
table). In other words, all the points fall within some poly or the
other, although all the polys may not necessarily contain at least one
point -- some polys may be devoid of points. So, 100 points should
have been returned. I think this may be because of me fubar-ing the
SRID stuff.

As mentioned in another thread, my points were 4326 (lon/lats
converted to points) so I TRANSFORMed(their geom, 102005), and my
polys were -1 (although they started their life as ESRI defined
USA_Contiguous_Lambert_Conformal_Conic) so I SetSRIDed(their geom,
102005) where the SRID 102005 refers to the said SRS duly added the
the srs table and the geometry_columns table. The points table churned
away for an hour and a half and successfully seems to have
Transformed... but who knows, maybe it dint.

Any advice on figuring out if the SRID settings are copacetic or not?

> If it is, consider batching the process up as
> an iterative thing, rather than trying to do the whole thing in one go.
>
> If you want to do it in one go, you may find that adding to your
> shared buffers, and increasing your work_mem quite substantially
> gives you a chance of succeeding.  If you haven't done any
> configuration tuning on your PostgreSQL, now is the time to do so.
>
> <http://www.varlena.com/GeneralBits/Tidbits/perf.html>
>
> On 20-Aug-06, at 3:32 PM, P Kishor wrote:
>
> > 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/