[postgis-users] SQLite and postGIS

[Martin Chapman]

Rich,

My guess is that GetFeature() is not being used in map server because there
is no need to get a random feature when you want to render all features in
your resultset.  I need GetFeature() because I use GDAL in a multi threaded
environment and if one thread called GetNextFeature, then another thread
also called GetNextFeature on the same resultset the internal cursor of the
OGRLayer would be out of sync with the other thread.  Therefore, I cannot
rely on internal cursors.  Also, I provide functionality that allows the
user to scroll back and forth in a list that populates itself on the fly
with only the visible rows in the list control.  That requires fast random
access.

Best regards,
Martin


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Richard
Greenwood
Sent: Sunday, April 13, 2008 8:52 PM
To: PostGIS Users Discussion
Cc: punkish at eidesis.org
Subject: Re: [postgis-users] SQLite and postGIS

On Sun, Apr 13, 2008 at 8:29 PM, Frank Warmerdam <warmerdam at pobox.com>
wrote:
> Martin Chapman wrote:
>
> > Rich,
> >
> > One thing you should note about OGR performance when accessing certain
> > databases like MySQL, PostgreSQL and SQLite is that OGR uses a strategy
> for
> > these drivers that gets progressively worse for large datasets.  Query
> > execution is always pretty quick in any database, it's accessing the
rows
> > quickly that is the true test of database performance in my opinion.
> > In OGR, the SQLite driver becomes progressively slower over time when
> > iterating over a large resultset from 0 to N.  Note that doesn't mean
that
> > SQLite is slow though, it's actually a really fast database if used
> > optimally.
> > For instance:
> >
> > For each record that is requested after a query is executed, OGR starts
at
> > the beginning of the resultset (row zero) and searches forward until it
> > finds the matching FID.  This pattern is repeated for every request.
This
> > will execute very quickly for the first few thousand FID's or so but the
> > further you iterate through the resultset, and the higher the index of
> each
> > FID becomes, the slower each record request will become.  This is
> obviously
> > a strategy of diminishing returns over any resultset of any size.  The
> > solution to this problem is to use random access.  See my other postings
> to
> > see how to make this change to OGR for MySQL, PostgreSQL and SQLite.
> >
>
>  Folks,
>
>  I would like to stress this behavior is only the case when attempting
>  to fetch features by fid.  The more common access pattern is to set
>  query parameters (spatial and attribute), and then to iterate through
>  those features matching using GetNextFeature().  In this situation there
>  is none of this "search by fid" O(n*n) performance.
>
>  Martin is implementing valuable optimizations for the GetFeature() by
>  FID case but the other alternative that performs well for all OGR
>  datasources is to use the GetNextFeature() iterator.

So in a MapServer itemquery, is GetFeature() or GetNextFeature() being
used? I'm guessing the latter. I'm trying to picture, from a MapServer
perspective, where the senarios you describe would be invoked. Like
when would you do a GetFeature() by FID?

I guess I should look at the code. My questions are purely academic,
so don't waste an undue amount of time trying to enlighten me.

Rich

-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users