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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Greenwood Sent: Sunday, April 13, 2008 9:27 AM To: PostGIS Users Discussion Cc: [EMAIL PROTECTED] Subject: Re: [postgis-users] SQLite and postGIS On Fri, Apr 11, 2008 at 9:29 PM, Frank Warmerdam <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] www.greenwoodmap.com _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
