[postgis-users] SQLite and postGIS

[P Kishor]

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
>  >
>  >  --
>  >
>  > Richard Greenwood
>  >  richard.greenwood at gmail.com
>  >  www.greenwoodmap.com
>  >
>
>
>
>  --
>  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
>


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