[postgis-users] SQLite and postGIS

[Stephen Woodbridge]

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