[postgis-users] Re: Speeding up SQLiteGIS

Sun Apr 13 17:55:44 PDT 2008

On 4/13/08, Martin Chapman <mchapman at texelinc.com> wrote:
> 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.

Fantastic! Please consider sharing the code with Frank. He is the
ultimate custodian for OGR, and we want OGR itself to become better to
the extent that it can.

In the meantime, Frank, thanks yet again for making this possible (and
for help with the OGR syntax).

la la la (me happy again).

>
>
>  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/
>
>