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

Tue Apr 8 14:31:41 PDT 2008

You can use the SQL command COPY to export a table or a view to a
delimited text file such as CSV, then open in Excel or import into
Access.  The WITH HEADER option will cause the first row to have
column names.

http://www.postgresql.org/docs/8.3/static/sql-copy.html

For small tables or resultsets I have also run the SQL query in
pgAdmin, then selected the table and used copy and paste to paste it
into an Excel spreadsheet.

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

On Tue, Apr 8, 2008 at 4:59 PM, Antonio Garcia <angarben at hotmail.com> wrote:
>
>  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
> > ********************************************
>
> ________________________________
> Tecnología, moda, motor, viajes,.suscríbete a nuestros boletines para estar
> siempre a la última MSN Newsletters
> _______________________________________________
>  postgis-users mailing list
>  postgis-users at postgis.refractions.net
>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>