[postgis-users] SQLite and postGIS

[Martin Chapman]

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.   

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 9:27 AM
To: PostGIS Users Discussion
Cc: punkish at eidesis.org
Subject: Re: [postgis-users] SQLite and postGIS

On Fri, Apr 11, 2008 at 9:29 PM, Frank Warmerdam <warmerdam at pobox.com>
wrote:
>  Puneet, Rich,
>
>  SQLite is already supported as a spatial database by OGR.  The caveat
>  is that in GDAL 1.5 it is just using a text column with WKT geometries so
>  the spatial performance is not great.
>
>  To use this with MapServer you would use CONNECTIONTYPE OGR and the
>  CONNECTION string would be the path for the sqlite database.  The
>  DATA statement should hold the table name be accessed.

Totally cool!

I used SpatiaLite (http://www.gaia-gis.it/spatialite/) LoadShapefile()
function to import a shapefile into a SQLite db. The geometries are
stored as BLOBs in a field named "geom" Then:
   sqlite> alter table ownership add column WKT_GEOMETRY;
   sqlite> update ownership set WKT_GEOMETRY=astext(geom);

Getting MapServer to use the SQLite table was very easy. Recent
versions of MS4W have SQLite support in GDAL. So simply adding
   CONNECTIONTYPE OGR
   CONNECTION "path/to/SQLite.db"
gets MapServer drawing geometries from SQLite.

I'm playing with a table containing about 15,000 polygons and
performance is fine.

Thanks Frank, for pointing me in the right direction.

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