[postgis-users] SQLite and postGIS

[P Kishor]

On 4/7/08, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:
> P Kishor wrote:
>
> > On 4/7/08, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:
> >
> > > Hi Paul,
> > >
> > >  I have started using SQLite for some projects, mostly just as a backing
> > > store for manipulating some data. I can't help but think it would be
> cool if
> > > it would be possible to get something like postGIS running in it.
> > >
> > >  I know you guys did some analysis of various databases a while back
> with an
> > > eye to their spatial potential and suitability for postGIS like
> inclusion -
> > > well at least that was my impression.
> > >
> > >  Did you look at SQLite?
> > >  Have you worked with SQLite?
> > >  Got any thoughts on this? Anyone?
> > >
> > >  I wish I could say I had a client interested in funding, but <sigh> I
> > > don't. But I have found myself googling for info on it 4 separate times
> in
> > > the past week, which is strange because I have no immediate use.
> > >
> > >  The use case for something like this would be to build a standalone
> > > application or web service that has a SQL/Spatial back-end without the
> need
> > > for installing and administering a postgres database.
> > >
> > >  As best as I can tell, the major hurdle would be whether or not it is
> > > possible for a reasonable amount of effort to integrate a spatial index
> > > system into SQLite.
> > >
> > >  Anyway, thought I would ask? See what other people thought?
> > >
> >
> > Did you see a thread that I started on OSGeo Discuss a few months
> > ago... it kinda devolved into (dare I say, degenerated into) a very
> > long and unproductive back and forth, but my idea was a SQLiteGIS on
> > the lines of PostGIS.
> >
>
>  Yes, I think I came across a few of your posting while searching google.
>
>
> > I actually wrote a pretty useful point-in-polygon routine using Perl
> > DBD::SQLite unwrapping Shapefiles into a SQLite db and then using
> > SQLite for boundary matching. It was for a very large p-in-p (7.5
> > million points against 250k polys) that ArcGIS was choking over. Works
> > very sweet in Perl/SQLite in about 20% to 30% of the time taken.
> >
>
>  Did you try this same example in postgis using indexes?

In that specific case, PostGIS was *not* an option. I could not run
another server (PostGres) on the computer, but running a file-based
program was not a problem Somehow, SQLite just seems like the right
thing, small, flexible, powerful. So, I made do with what I had. And
the success in that encouraged me to think that this could be
extended.

I also wrote a bunch of Perl modules to work on ArcGIS data
(Shapefiles as well general crap in SDE) and all-in-all, it worked out
very well.




> It would be
> interesting to see how it compares. P-in-p can be optimized to work much
> faster than the GEOS code so it is likely that some purposed code would be
> much faster than the generalized code in postGIS although there has been
> some discussions on the dev list about changing that. It might even be in
> the newest code, I haven't had a chance to upgrade yet.
>
>
> > I often wonder what would it take to graft Geos inside SQlite. Too bad
> > I know nothing about C++.
> >
>
>  There is a C-API to GEOS, so you do not need to know C++ and I imagine that
> with the work already done at
> http://www.gaia-gis.it/spatialite/ it should be pretty to
> write wrapper functions to expose more of GEOS within spatialite.
>
>  I also think that there might be a Perl GEOS SWIG wrapper, but I haven't
> looked for it.
>
>  The real limiting issue will be spatial indexes and this is probably the
> most difficult item to develop and integrate into the existing code. I have
> not clue what all it would take.
>
>  -Steve
>


-- 
Puneet Kishor