[postgis-users] RE: Speeding up SQLiteGIS

[Martin Chapman]

I'm working on it right now and will send you the code when I finish.
Basically SQLite is fast and the OGR code that uses sqlite is slow because
of the way they wrote it.  My changes make OGR use random access versus what
they do today and scroll from 0 to n for each record requested.  That means
that row one will return quick but for each sub-sequent request the code
loops until it finds the record requested.  Very inefficient.  It has speed
it up by about 50x on the very small shape file I am using to test.

Best regards,
Martin

-----Original Message-----
From: P Kishor [mailto:punk.kish at gmail.com] 
Sent: Sunday, April 13, 2008 6:41 PM
To: PostGIS Users Discussion
Cc: Richard Greenwood; Stephen Woodbridge; Martin Chapman
Subject: Speeding up SQLiteGIS

(started a new thread)



On 4/13/08, Martin Chapman <mchapman at texelinc.com> wrote:
> I can connect to SQLite using OGR in my application as seen in the
>  attachment.  It's kind of slow compared to shape but I can optimize the
OGR
>  code for SQLite like I did for PostgreSQL and MySQL in my local copy of
the
>  gdal code. Then it's as fast as shape.  I really like SQLite as well.
What
>  is the address of your WMS server?

Martin,

I am right now working on my laptop, but will soon put it on a
publicly accessible server. I am fooling around with a few tricks, and
now I am getting the entire table being selected *and* rendered in
about 92 ms. I think that is *very* good (this is a table of all the
zip code boundaries in the State of Wisconsin, US -- 1121 rows and all
the river segments -- > 183K rows).

Now, could you kindly share the "optimization" of OGR code *for*
SQLite? Have you considered submitted it back to Frank for possible
inclusion in the main branch?

Also, we could request OSGeo to set up some space for us to share
SQLite code, tricks, etc. In the meantime, I have bagged sqlitegis.org
and sqlitegis.com, and would be happy to host a wiki/list or whatever
crap the dreamhost allows me to host with a single-click install.

Seriously, this the first time in years that I have been excited by
all this mapping business. SQLite is just such an incredibly powerful
tool, not for its power and performance (which is plenty fast for me)
but for its incredible simplicity. The fasted program for me is the
one that allows me to install, setup and program the fastest.


>
>  Best regards,
>  Martin
>
>
>  -----Original Message-----
>  From: postgis-users-bounces at postgis.refractions.net
>  [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of P
Kishor
>  Sent: Sunday, April 13, 2008 1:51 PM
>  To: PostGIS Users Discussion; Richard Greenwood
>  Subject: Re: [postgis-users] SQLite and postGIS
>
>
> On 4/13/08, Richard Greenwood <richard.greenwood at gmail.com> wrote:
>  > I should note that ogr2ogr creates a SQLite spatial table even more
>  >  easily that SpatiaLite:
>  >    ogr2ogr -f "SQLite" dest.db source.shp source
>
>  Thanks Rich. You made my day. I am drawing a layer with about 185K
>  rows, and, yes, there is a time lag, but this is out-of-the-box
>  performance.
>
>  One question -- the above ogr2ogr command, for me, does not seem to
>  cooperate when adding multiple shapefiles to the same db. If I do like
>  so
>
>  ogr2ogr -f "SQLite" dest.db source1.shp source2.shp source3.shp
>
>  I get a dest.db with 0 Kb. I got around that by creating dest1.db,
>  dest2.db and so on, and then creating dest.db, attaching the other
>  dbs, and moving the tables into dest.db via
>
>  CREATE TABLE ... AS SELECT * FROM....
>
>  This seems a bit goofy, so I am sure I am doing something wrong with
>  the ogr command. Suggestions?
>
>  Another note -- I wonder if this merits starting a separate SQLitegis
>  mailing list? I am happy here as long as the pg users don't mind.
>
>
>  >
>  >  Rich
>  >
>  >
>  >
>  >
>  >  On Sun, Apr 13, 2008 at 9:26 AM, Richard Greenwood
>  >  <richard.greenwood at gmail.com> wrote:
>  >  > 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
>  >  >



-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/