[postgis-users] SQLite and postGIS

[mchapman at texelinc.com]

You should also read what Frank says about it at http://www.gdal.org/ogr/drv_sqlite.html .  

I've thought about this as well and I think it would be really useful.  

1.  I have code for an rtree that could be used for a very very fast spatial index.  It supports serializing itself too.  

2.  I would need help integrating into the sqllite query plan but my guess is that it would be fairly easy.  We could look at the OGR code to help us get a feel for the sqlite c api and ask the sqlite developers for assistance.

3. Geos is a piece of cake to use and I can provide any c/c++ code we need to work with the geos api. I also have code the reads ands writes any ogc wkb or wkt for any ogc geometry type.

4. I would have concerns about the ability to lock the database as frank points out in the OGR docs but we could always wrap the database with a service and place a mutex around each database call.  A wrapper service would also open the doors to accepting http requests, if sqlite  doesn't have that support already.

5. I think extending one of the projects you mentioned earlier or using OGR would be the way to go.

Please let me know if anyone is going to embark on this effort because I have been thinking about doing it myself.

Best regards,
Martin

Sent via BlackBerry by AT&T

-----Original Message-----
From: Stephen Woodbridge <woodbri at swoodbridge.com>

Date: Mon, 07 Apr 2008 22:02:45 
To:punkish at eidesis.org, PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Subject: Re: [postgis-users] SQLite and postGIS


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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users