[postgis-users] RE: postgis-users Digest, Vol 66, Issue 8

Tue Apr 8 13:59:41 PDT 2008

Hello,

How can I export a Postgis table to access or excel table?

Thanks.
Saludos, ----------- Antonio García Benlloch Correo: angarben at hotmail.com angarben at aaa.upv.es Web: http://personales.alumno.upv.es/~angarben> From: postgis-users-request at postgis.refractions.net> Subject: postgis-users Digest, Vol 66, Issue 8> To: postgis-users at postgis.refractions.net> Date: Tue, 8 Apr 2008 12:00:52 -0700> > Send postgis-users mailing list submissions to> postgis-users at postgis.refractions.net> > To subscribe or unsubscribe via the World Wide Web, visit> http://postgis.refractions.net/mailman/listinfo/postgis-users> or, via email, send a message with subject or body 'help' to> postgis-users-request at postgis.refractions.net> > You can reach the person managing the list at> postgis-users-owner at postgis.refractions.net> > When replying, please edit your Subject line so it is more specific> than "Re: Contents of postgis-users digest..."> > > Today's Topics:> > 1. 1.3.3 Pre-Release Notice (Paul Ramsey)> 2. SQLite and postGIS (Stephen Woodbridge)> 3. Re: SQLite and postGIS (William Kyngesburye)> 4. Re: SQLite and postGIS (Shoaib Burq)> 5. Loading only data that intersects another feature> (Marcus C. England)> 6. Re: postgis mapfile php (nickthegreek-)> 7. RE: Loading only data that intersects another feature> (Randall, Eric)> 8. Re: SQLite and postGIS (Stephen Woodbridge)> 9. Re: SQLite and postGIS (P Kishor)> 10. Re: SQLite and postGIS (Stephen Woodbridge)> 11. Re: SQLite and postGIS (P Kishor)> 12. Re: SQLite and postGIS (mchapman at texelinc.com)> 13. Re: SQLite and postGIS (Stephen Woodbridge)> 14. Best graphical viewer for PostGIS data (Pierre Racine)> 15. Re: Best graphical viewer for PostGIS data (Chris Hermansen)> 16. RE: Best graphical viewer for PostGIS data (Obe, Regina)> 17. RE: Best graphical viewer for PostGIS data (Randall, Eric)> 18. Re: Best graphical viewer for PostGIS data (Nicolas Ribot)> 19. R: [postgis-users] Best graphical viewer for PostGIS data> (P.Rizzi Ag.Mobilit? Ambiente)> 20. Re: Best graphical viewer for PostGIS data (Kevin Neufeld)> 21. Re: SQLite and postGIS (Martin Davis)> 22. Re: SQLite and postGIS (P Kishor)> > > ----------------------------------------------------------------------> > Message: 1> Date: Mon, 7 Apr 2008 14:12:42 -0700> From: "Paul Ramsey" <pramsey at cleverelephant.ca>> Subject: [postgis-users] 1.3.3 Pre-Release Notice> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <30fe546d0804071412h6d0c4dd7q25b0e34be63a8f1d at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > Folks,> > 1.3.3 is going to be coming out by the end of this week. If you have> time, please pull the SVN version and give it a try, to ensure we> haven't left any big bugs in the bin.> > Thanks!> > Paul> > > ------------------------------> > Message: 2> Date: Mon, 07 Apr 2008 17:34:20 -0500> From: Stephen Woodbridge <woodbri at swoodbridge.com>> Subject: [postgis-users] SQLite and postGIS> To: Paul Ramsey <pramsey at cleverelephant.ca>, PostGIS Users Discussion> <postgis-users at postgis.refractions.net>> Message-ID: <47FAA16C.8050104 at swoodbridge.com>> Content-Type: text/plain; charset=UTF-8; format=flowed> > 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?> > Thanks,> -Steve> > > ------------------------------> > Message: 3> Date: Mon, 7 Apr 2008 16:50:28 -0500> From: William Kyngesburye <woklist at kyngchaos.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>> Message-ID: <F1360DB6-5F15-4FF0-9F96-07F00F26C4B6 at kyngchaos.com>> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes> > Someone started something already. Basic for now - good for > exchanging geodata.> > http://www.gaia-gis.it/spatialite/> > On Apr 7, 2008, at 5:34 PM, Stephen Woodbridge 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?> >> > Thanks,> > -Steve> > _______________________________________________> > postgis-users mailing list> > postgis-users at postgis.refractions.net> > http://postgis.refractions.net/mailman/listinfo/postgis-users> > -----> William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>> http://www.kyngchaos.com/> > "Oh, look, I seem to have fallen down a deep, dark hole. Now what > does that remind me of? Ah, yes - life."> > - Marvin> > > > > ------------------------------> > Message: 4> Date: Tue, 8 Apr 2008 07:52:20 +1000> From: "Shoaib Burq" <shoaib at nomad-labs.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <b8b84f110804071452n47dbe3f1y9f8a05b75e30f7f7 at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > You are not alone... I have been using SQLite for my web mapping> applications precisely because its so quick to get it running.> > I have not had the need to do any advance spatial functions but I can> imagine it would be nice to have.> > Has anyone done a preliminary check of how hard it would be to add> support for Simple Features and Spatial Indices to start with? Perhaps> its time to start. I'm going to be in the USofA 1-24 May if anyone> wants to meetup> > Shoaib Burq> --> http://rails.nomad-labs.com> > On Tue, Apr 8, 2008 at 8:34 AM, 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?> >> > Thanks,> > -Steve> > _______________________________________________> > postgis-users mailing list> > postgis-users at postgis.refractions.net> > http://postgis.refractions.net/mailman/listinfo/postgis-users> >> > > ------------------------------> > Message: 5> Date: Mon, 07 Apr 2008 16:22:31 -0700> From: "Marcus C. England" <marcuscengland at gmail.com>> Subject: [postgis-users] Loading only data that intersects another> feature> To: postgis-users at postgis.refractions.net> Message-ID: <47FAACB7.7020907 at gmail.com>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed> > Hi all,> > I have a postgis polygon layer representing a site boundary (we will > call the layer "boundary") and another postgis point layer with a larger > extent representing California condor observations (we will call the > layer "condors"). I am using gvSIG which allows SQL restrictions of > layer loads from postgis. What SQL statement would I use (keep in mind > the above layer names) to only load the "condors" data that intersects > "boundary"?> > -Marcus> > > ------------------------------> > Message: 6> Date: Mon, 7 Apr 2008 16:39:00 -0700 (PDT)> From: nickthegreek- <nickthegreek_ at hotmail.com>> Subject: Re: [postgis-users] postgis mapfile php> To: postgis-users at postgis.refractions.net> Message-ID: <16543003.post at talk.nabble.com>> Content-Type: text/plain; charset=us-ascii> > > <select namex="mydropdown">> > <?php > for ($lt = 0; $lt < pg_numrows($myresult); $lt++) {> $id = pg_result($myresult, $lt, 0);> > ?>> <option value="name"> <?php echo $id;?> </option>> <?php> }> > ?>> </select>> > <input type="checkbox" name="layer[]"> value="countries" <?php echo $countries;?> >> > > i have this select whick takes the results from a column from a database...> i haven;t put this select in a form cause it will be inside an othe form ..> but i think it works perfect as it nows... > > how can i take the selected value from the menu? which is ? $namex? > $mydropdown??> > and how can i put it here.. like ..> > the_geom from ( select gid, the_geom from europe_country where> name='$namex') as foo using unique gid using srid=-1> -- > View this message in context: http://www.nabble.com/postgis-mapfile-php-tp16530438p16543003.html> Sent from the PostGIS - User mailing list archive at Nabble.com.> > > > ------------------------------> > Message: 7> Date: Mon, 7 Apr 2008 19:49:10 -0400> From: "Randall, Eric" <ERandall at eriecountygov.org>> Subject: RE: [postgis-users] Loading only data that intersects another> feature> To: <marcuscengland at gmail.com>, "PostGIS Users Discussion"> <postgis-users at postgis.refractions.net>> Message-ID:> <CD32AE6ACD828644B29601ADC966FDCB01B4A9B7 at ex1.eriecountygov.local>> Content-Type: text/plain; charset="iso-8859-1"> > select condors.*> from condors, boundary> where st_within(condors.the_geom,boundary.the_geom)> > or > > select condors.*> from condors, boundary> where st_intersects(condors.the_geom,boundary.the_geom)> > > I would suggest that you download and install OpenJump and the dbquery plugin.> That way you can experiment with ad hoc spatial queries> using the many postgis functions and visually see the results of each.> A great way to see postgis functionality. Mezogis is also good for this but I like OpenJump.> > -Eric> > -----Original Message-----> From: postgis-users-bounces at postgis.refractions.net> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of> Marcus C. England> Sent: Monday, April 07, 2008 7:23 PM> To: postgis-users at postgis.refractions.net> Subject: [postgis-users] Loading only data that intersects another> feature> > > Hi all,> > I have a postgis polygon layer representing a site boundary (we will > call the layer "boundary") and another postgis point layer with a larger > extent representing California condor observations (we will call the > layer "condors"). I am using gvSIG which allows SQL restrictions of > layer loads from postgis. What SQL statement would I use (keep in mind > the above layer names) to only load the "condors" data that intersects > "boundary"?> > -Marcus> _______________________________________________> postgis-users mailing list> postgis-users at postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users> > > ------------------------------> > Message: 8> Date: Mon, 07 Apr 2008 20:17:20 -0500> From: Stephen Woodbridge <woodbri at swoodbridge.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: William Kyngesburye <kyngchaos at kyngchaos.com>> Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>> Message-ID: <47FAC7A0.5000109 at swoodbridge.com>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed> > William,> > Thanks for the pointer. I will check it out.> Doesn't look like they have spatial indexes, but it does look like an > interesting start.> > -Steve> > William Kyngesburye wrote:> > Someone started something already. Basic for now - good for exchanging > > geodata.> > > > http://www.gaia-gis.it/spatialite/> > > > On Apr 7, 2008, at 5:34 PM, Stephen Woodbridge 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?> >>> >> Thanks,> >> -Steve> >> _______________________________________________> >> postgis-users mailing list> >> postgis-users at postgis.refractions.net> >> http://postgis.refractions.net/mailman/listinfo/postgis-users> > > > -----> > William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>> > http://www.kyngchaos.com/> > > > "Oh, look, I seem to have fallen down a deep, dark hole. Now what does > > that remind me of? Ah, yes - life."> > > > - Marvin> > > > > > > > ------------------------------> > Message: 9> Date: Mon, 7 Apr 2008 21:18:24 -0400> From: "P Kishor" <punk.kish at gmail.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <cdf6db500804071818g55286e19odc8e06e23da1f177 at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > 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.> > 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.> > I often wonder what would it take to graft Geos inside SQlite. Too bad> I know nothing about C++.> > > >> > Thanks,> > -Steve> > _______________________________________________> > 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/> > > ------------------------------> > Message: 10> Date: Mon, 07 Apr 2008 22:02:45 -0500> From: Stephen Woodbridge <woodbri at swoodbridge.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: punkish at eidesis.org, PostGIS Users Discussion> <postgis-users at postgis.refractions.net>> Message-ID: <47FAE055.90002 at swoodbridge.com>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed> > 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> > > ------------------------------> > Message: 11> Date: Mon, 7 Apr 2008 22:06:57 -0400> From: "P Kishor" <punk.kish at gmail.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <cdf6db500804071906l4aa515aey78ccfe30704ff297 at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > 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> > > ------------------------------> > Message: 12> Date: Tue, 8 Apr 2008 02:37:21 +0000> From: mchapman at texelinc.com> Subject: Re: [postgis-users] SQLite and postGIS> To: "PostGIS Users Discussion"> <postgis-users at postgis.refractions.net>, punkish at eidesis.org> Message-ID:> <1902653510-1207622475-cardhu_decombobulator_blackberry.rim.net-2120766039- at bxe130.bisx.prod.on.blackberry>> > Content-Type: text/plain> > 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> > ------------------------------> > Message: 13> Date: Tue, 08 Apr 2008 00:24:12 -0500> From: Stephen Woodbridge <woodbri at swoodbridge.com>> Subject: Re: [postgis-users] SQLite and postGIS> To: mchapman at texelinc.com, PostGIS Users Discussion> <postgis-users at postgis.refractions.net>> Cc: punkish at eidesis.org> Message-ID: <47FB017C.9030601 at swoodbridge.com>> Content-Type: text/plain; charset=UTF-8; format=flowed> > 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> > > > ------------------------------> > Message: 14> Date: Tue, 8 Apr 2008 11:21:49 -0400> From: "Pierre Racine" <Pierre.Racine at sbf.ulaval.ca>> Subject: [postgis-users] Best graphical viewer for PostGIS data> To: <postgis-users at postgis.refractions.net>> Message-ID:> <D58FF82475EA0A4A9347D3CE16B46E5F32898E at exch-be03.ulaval.ca>> Content-Type: text/plain; charset="US-ASCII"> > Hi,> > I would like to hear from you guys about the viewer you use to "see"> PostGIS geometries. Which one is the best and why? How do they support> views, editing, creation, etc...> > I can list a couple of them:> > QGIS> uDIG> GRASS> MapServer> Any others?> > Pierre> > > ------------------------------> > Message: 15> Date: Tue, 08 Apr 2008 08:26:15 -0700> From: Chris Hermansen <chris.hermansen at timberline.ca>> Subject: Re: [postgis-users] Best graphical viewer for PostGIS data> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>> Message-ID: <47FB8E97.1010406 at timberline.ca>> Content-Type: text/plain; charset=ISO-8859-1> > I seem to use OpenJump and QGIS most often. I like OpenJump's QA tools> because I seem to regularly receive data that was hiding in the corner> when the QA was done.> > Pierre Racine wrote:> > Hi,> >> > I would like to hear from you guys about the viewer you use to "see"> > PostGIS geometries. Which one is the best and why? How do they support> > views, editing, creation, etc...> >> > I can list a couple of them:> >> > QGIS> > uDIG> > GRASS> > MapServer> > Any others?> >> > Pierre> > _______________________________________________> > postgis-users mailing list> > postgis-users at postgis.refractions.net> > http://postgis.refractions.net/mailman/listinfo/postgis-users> > > > > -- > Regards,> > Chris Hermansen · mailto:clh at timberline.ca> tel:+1.604.714.2878 · fax:+1.604.733.0631> Timberline Natural Resource Group · http://www.timberline.ca> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5> > C'est ma façon de parler.> > > > ------------------------------> > Message: 16> Date: Tue, 8 Apr 2008 11:47:59 -0400> From: "Obe, Regina" <robe.dnd at cityofboston.gov>> Subject: RE: [postgis-users] Best graphical viewer for PostGIS data> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <53F9CF533E1AA14EA1F8C5C08ABC08D203A2BF7E at ZDND.DND.boston.cob>> Content-Type: text/plain; charset="iso-8859-1"> > I think there is a gvSIG you can add to the list although I personally haven't used it so can't comment. > > -----Original Message-----> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Chris Hermansen> Sent: Tuesday, April 08, 2008 11:26 AM> To: PostGIS Users Discussion> Subject: Re: [postgis-users] Best graphical viewer for PostGIS data> > I seem to use OpenJump and QGIS most often. I like OpenJump's QA tools> because I seem to regularly receive data that was hiding in the corner> when the QA was done.> > Pierre Racine wrote:> > Hi,> >> > I would like to hear from you guys about the viewer you use to "see"> > PostGIS geometries. Which one is the best and why? How do they support> > views, editing, creation, etc...> >> > I can list a couple of them:> >> > QGIS> > uDIG> > GRASS> > MapServer> > Any others?> >> > Pierre> > _______________________________________________> > postgis-users mailing list> > postgis-users at postgis.refractions.net> > http://postgis.refractions.net/mailman/listinfo/postgis-users> > > > > -- > Regards,> > Chris Hermansen · mailto:clh at timberline.ca> tel:+1.604.714.2878 · fax:+1.604.733.0631> Timberline Natural Resource Group · http://www.timberline.ca> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5> > C'est ma façon de parler.> > _______________________________________________> postgis-users mailing list> postgis-users at postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users> -----------------------------------------> The substance of this message, including any attachments, may be> confidential, legally privileged and/or exempt from disclosure> pursuant to Massachusetts law. It is intended> solely for the addressee. If you received this in error, please> contact the sender and delete the material from any computer.> > > > ------------------------------> > Message: 17> Date: Tue, 8 Apr 2008 11:50:01 -0400> From: "Randall, Eric" <ERandall at eriecountygov.org>> Subject: RE: [postgis-users] Best graphical viewer for PostGIS data> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <CD32AE6ACD828644B29601ADC966FDCB01B4A9BB at ex1.eriecountygov.local>> Content-Type: text/plain; charset="iso-8859-1"> > I use OpenJump the most because of the dbquery plugin. Immediate viewing of any query that returns a geometry, derived or otherwise, is the most important feature for me. I use it as sort of a tester/designer for automating recurring tasks that have a spatial analytical component. All editing is done in other software, outside of postgres/postgis, and reloaded monthly.> > -----Original Message-----> From: postgis-users-bounces at postgis.refractions.net> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of> Pierre Racine> Sent: Tuesday, April 08, 2008 11:22 AM> To: postgis-users at postgis.refractions.net> Subject: [postgis-users] Best graphical viewer for PostGIS data> > > Hi,> > I would like to hear from you guys about the viewer you use to "see"> PostGIS geometries. Which one is the best and why? How do they support> views, editing, creation, etc...> > I can list a couple of them:> > QGIS> uDIG> GRASS> MapServer> Any others?> > Pierre> _______________________________________________> postgis-users mailing list> postgis-users at postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users> > > ------------------------------> > Message: 18> Date: Tue, 8 Apr 2008 17:56:55 +0200> From: "Nicolas Ribot" <nicky666 at gmail.com>> Subject: Re: [postgis-users] Best graphical viewer for PostGIS data> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <28de5e310804080856s33d96e25xe95e01a69a8daedf at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > > I think there is a gvSIG you can add to the list although I personally haven't used it so can't comment.> >> > Pretty impressive software to display both shapefile and postgis layers.> Also supports rasters, other spatial DB and some other file formats.> Very fast to render features on screen.> > GUI is.... to close to ArcView 3.2 to my taste...> > I personnally use Jump/OpenJump, as the databaseQuery plugin allows to> directly see results of a PostGIS query and it is so simple to add new> features by pasting WKT representation into a newly created layer.> > Nicolas> > > ------------------------------> > Message: 19> Date: Tue, 8 Apr 2008 18:01:04 +0200> From: P.Rizzi Ag.Mobilit? Ambiente <paolo.rizzi at ama-mi.it>> Subject: R: [postgis-users] Best graphical viewer for PostGIS data> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>> Message-ID:> <6B1D057DB7670E4CA686E2A33445D49F3CD302 at SRVEXCHANGE2003.agenzia.dom>> Content-Type: text/plain; charset="iso-8859-1"> > Yes, I too use OpenJUMP as a viewer because it's so easy and fast> to experiment with spatial queries and immediately see their result.> Never used for editing.> > Bye> Paolo> > > -----Messaggio originale-----> > Da: postgis-users-bounces at postgis.refractions.net> > [mailto:postgis-users-bounces at postgis.refractions.net]Per conto di> > Randall, Eric> > Inviato: martedì 8 aprile 2008 17.50> > A: PostGIS Users Discussion> > Oggetto: RE: [postgis-users] Best graphical viewer for PostGIS data> > > > > > I use OpenJump the most because of the dbquery plugin. > > Immediate viewing of any query that returns a geometry, > > derived or otherwise, is the most important feature for me. > > I use it as sort of a tester/designer for automating > > recurring tasks that have a spatial analytical component. > > All editing is done in other software, outside of > > postgres/postgis, and reloaded monthly.> > > > -----Original Message-----> > From: postgis-users-bounces at postgis.refractions.net> > [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of> > Pierre Racine> > Sent: Tuesday, April 08, 2008 11:22 AM> > To: postgis-users at postgis.refractions.net> > Subject: [postgis-users] Best graphical viewer for PostGIS data> > > > > > Hi,> > > > I would like to hear from you guys about the viewer you use to "see"> > PostGIS geometries. Which one is the best and why? How do they support> > views, editing, creation, etc...> > > > I can list a couple of them:> > > > QGIS> > uDIG> > GRASS> > MapServer> > Any others?> > > > Pierre> > _______________________________________________> > 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> > > > > ------------------------------> > Message: 20> Date: Tue, 08 Apr 2008 10:33:51 -0700> From: Kevin Neufeld <kneufeld at refractions.net>> Subject: Re: [postgis-users] Best graphical viewer for PostGIS data> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>> Message-ID: <47FBAC7F.208 at refractions.net>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed> > I prefer OpenJump as well. Through plugins I wrote myself, I can view > or edit data directly in PostGIS pulled either from a single table or > any SQL query that returns a single geometry column. Since the database > I often work with is ~150GB I need to be able to load data into Jump > dynamically, that is, only load data whose bounding box intersects the > current viewport. (Though the plugin can also ignore the bounding box > filter and statically load the entire content of any SQL query or table > entirely in memory).> > I don't know the state of any publicly available plugins that permit > dynamic data editing, but OpenJump does come with a limited dynamic > viewer plugin that permits the viewing of a single table (optionally > with filters applied).> > Cheers,> Kevin> > Randall, Eric wrote:> > I use OpenJump the most because of the dbquery plugin. Immediate viewing of any query that returns a geometry, derived or otherwise, is the most important feature for me. I use it as sort of a tester/designer for automating recurring tasks that have a spatial analytical component. All editing is done in other software, outside of postgres/postgis, and reloaded monthly.> >> > -----Original Message-----> > From: postgis-users-bounces at postgis.refractions.net> > [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of> > Pierre Racine> > Sent: Tuesday, April 08, 2008 11:22 AM> > To: postgis-users at postgis.refractions.net> > Subject: [postgis-users] Best graphical viewer for PostGIS data> >> >> > Hi,> >> > I would like to hear from you guys about the viewer you use to "see"> > PostGIS geometries. Which one is the best and why? How do they support> > views, editing, creation, etc...> >> > I can list a couple of them:> >> > QGIS> > uDIG> > GRASS> > MapServer> > Any others?> >> > Pierre> > _______________________________________________> > 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> > > > > ------------------------------> > Message: 21> Date: Tue, 08 Apr 2008 10:37:22 -0700> From: Martin Davis <mbdavis at refractions.net>> Subject: Re: [postgis-users] SQLite and postGIS> To: punkish at eidesis.org, PostGIS Users Discussion> <postgis-users at postgis.refractions.net>> Message-ID: <47FBAD52.9090407 at refractions.net>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed> > > > P Kishor wrote:> >> > 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.> >> > > >> I'm curious about your SQLite spatial solution. > > What approach did you use for storing geometry? How did you load the > shapefiles?> > Since SQLite has no spatial index, did you just iterate over all > geometries for you PIP test? What was your PIP test written in?> > What was the advantage of doing this over just using the raw shapefiles > - was it performance, or ease of data management?> > The SQLite Spatial direction seems like an interesting one to pursue for > just this kind of bulk processing use case.> >> >> > > > -- > Martin Davis> Senior Technical Architect> Refractions Research, Inc.> (250) 383-3022> > > > ------------------------------> > Message: 22> Date: Wed, 9 Apr 2008 00:26:13 +0530> From: "P Kishor" <punkish at eidesis.org>> Subject: Re: [postgis-users] SQLite and postGIS> To: "Martin Davis" <mbdavis at refractions.net>> Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>> Message-ID:> <cdf6db500804081156o6f1d9990h4a5800f77c4d2304 at mail.gmail.com>> Content-Type: text/plain; charset=ISO-8859-1> > (the reply may look screwy because I am temporarily saddled with a> hobbled gmail interface on IE something or the other, and it seems to> have a mind of its own)> > I unpacked the shapefiles via a module on CPAN and stored the> coordinates as text strings (well, everything is a string for SQLite)> readymade for a p-in-p algorithm that I adapted from the Wolf book> (Algorithms with Perl or some such title -- Hietanemi et al). IIRC, I> stored all the x-coords as a string in one col, all the y-coords as a> string in another col, and that allowed me to reconstruct the arrays I> needed to do the p-in-p.> > Iterating over all the geoms was decidedly silly -- 250k * 7.5m> results in more than a trillion transactions, if my math is correct.> Instead, I stored the bounding box of each shape in separate cols, and> as a first pass, use SQL selects to narrow down the points per poly.> Then ran the p-in-p on each set.> > Re. advantages -- well, I first tried working with ArcGIS/SDE. An> existing solution was taking about 8 days from start to finish (a lot> of pre and post work on either side of the overlay), and that is if> the process didn't explode prematurely. My process took about 2.5 to 3> days. I do recall building the SQLite db with a bigger page size (I am> writing all this from memory. I am very far from my computer right> now).> > I have briefly looked at the Spatialite solution and that does look> like a very cool start. In fact, it gives me enough incentive to try> and learn C.> > I do have one question for you all -- why on earth does Spatialite> (and also PostGIS as well as ArcGIS) store the geometry as a BLOB?> What are the advantages? Is it space? Is it speed? Both? I can't> really understand the reason for all that AsWKT and WKB and AsPoints> and whatever back-and-forth. Why not just store coords as numbers, or> a string of coords as, well, a string of coords? Especially in the> case of PostGIS, PostgreSQL has a col type of arrays, so why not just> use that? SQLite stores everything as strings anyway, so why bother> with the back and forth unless there is speed advantage. I don't care> about the space -- hard disk is cheap.> > Many thanks for this conversation. This is very interesting for me. I> would love to see a truly lightweight but truly real geospatial db> solution realized, and I am a committed fan of SQLite.> > On 4/8/08, Martin Davis <mbdavis at refractions.net> wrote:> >> >> > P Kishor wrote:> > >> > > 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.> > >> > >> > >> > I'm curious about your SQLite spatial solution.> >> > What approach did you use for storing geometry? How did you load the> > shapefiles?> >> > Since SQLite has no spatial index, did you just iterate over all> > geometries for you PIP test? What was your PIP test written in?> >> > What was the advantage of doing this over just using the raw shapefiles> > - was it performance, or ease of data management?> >> > The SQLite Spatial direction seems like an interesting one to pursue for> > just this kind of bulk processing use case.> > >> > >> > >> >> > --> > Martin Davis> > Senior Technical Architect> > Refractions Research, Inc.> > (250) 383-3022> >> >> > > -- > 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/> > > ------------------------------> > _______________________________________________> postgis-users mailing list> postgis-users at postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users> > > End of postgis-users Digest, Vol 66, Issue 8> ********************************************
_________________________________________________________________
MSN Noticias
http://noticias.msn.es/comunidad.aspx
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080408/11d69a10/attachment.html>