[postgis-users] SQLite and postGIS

Mon Apr 7 22:24:12 PDT 2008

Hello Martin,

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

Databases basically do not work over NFS mounted filesystems because of 
locking and caching issues. But this is not a SQLite issue, MYSQL, 
Postgres, etc all would have the same issues. I think it is safe to 
ignore that, ot maybe detect that the requested db file is on a non-safe 
filesystem and throw an error.

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

Have you checked out http://www.gaia-gis.it/spatialite/ this guys as 
made a good start.

I'm not sure I would be very good at integrating the indexing code into 
the code. I'm still just learning to use the library, but I would be 
happy to build application based on it and be your alpha test guinea 
pig, so to speak.

There seems to be a few vocal supporters here for such an effort if you 
were interested in working on this.

Best regards,
   -Stephen Woodbridge
    http://imaptools.com/

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