[postgis-users] SQLite and postGIS

[Richard Greenwood]

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