[postgis-users] Re: Splitting multistrings

Mark Thomas spatialguru.net at gmail.com
Tue Nov 21 22:36:19 PST 2006


If you're just looking to iterate over all lines in a multilinestring do
something like this...


    for i in 1..NumGeometries(the_geom) loop
        a_geom := GeometryN(the_geom, i);
        --do something
    end loop;


On 11/21/06, postgis-users-request at postgis.refractions.net <
postgis-users-request at postgis.refractions.net> wrote:
>
> 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. runing postgis init scripts on a named schema (Dylan Beaudette)
>    2. Ubuntu install of PostGIS 1.1.6 failing (Allan Doyle)
>    3. RE: runing postgis init scripts on a named schema
>       (Pedro Doria Meunier)
>    4. Re: runing postgis init scripts on a named schema
>       (Dylan Beaudette)
>    5. Spatial Join Performance (Eric Shuman)
>    6. Re: SRID for Saudi Arabia (Markus Schaber)
>    7. lat and long format (Sunitha Bayana)
>    8. looking for some perf data (raphael Jacquot)
>    9. Re: looking for some perf data (Martin Davis)
>   10. Re: looking for some perf data (raphael Jacquot)
>   11. Re: looking for some perf data (Martin Davis)
>   12. Re: looking for some perf data (raphael Jacquot)
>   13. Splitting multistrings (Nick Black)
>   14. Re: Splitting multistrings (Steffen Macke)
>   15. Re: Splitting multistrings (Barend K ? bben)
>   16. Re: looking for some perf data (Paul Ramsey)
>   17. centroid() and AddGeometryColumn() questions
>       (matt.pettis at thomson.com)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Mon, 20 Nov 2006 14:24:32 -0800
> From: Dylan Beaudette <dylan.beaudette at gmail.com>
> Subject: [postgis-users] runing postgis init scripts on a named schema
> To: postgis-users at postgis.refractions.net
> Message-ID: <200611201424.32336.dylan.beaudette at gmail.com>
> Content-Type: text/plain;  charset="us-ascii"
>
> Hi everyone,
>
> I have a fully functional postgis DB up and running, using the
> default 'public' schema. I have just added a new schema, and would like
> to 'spatially-enable' this new one.
>
> does anyone have tips on how this might be accomplished ?
>
>
> thanks in advance!
>
> --
> Dylan Beaudette
> Soils and Biogeochemistry Graduate Group
> University of California at Davis
> 530.754.7341
>
>
> ------------------------------
>
> Message: 2
> Date: Mon, 20 Nov 2006 18:45:50 -0500
> From: Allan Doyle <adoyle at eogeo.org>
> Subject: [postgis-users] Ubuntu install of PostGIS 1.1.6 failing
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <1D9F853E-B379-4D84-B8F2-3E5797FA573E at eogeo.org>
> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
>
> I'm installing onto an up-to-date Ubuntu 6.06 system
>
> proj-4.5.0 installs ok
> geos-3.0.0rc2 installs ok
> postgis-1.1.6 gives the following errors:
>
> =====
> mwow% sudo su postgres
> postgres at mwow:/usr/local/src/postgis-1.1.6$ make check
> make -C regress test
> make[1]: Entering directory `/usr/local/src/postgis-1.1.6/regress'
> Creating spatial db postgis_reg
> ERROR:  function postgis_lib_version() does not exist
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> Something went wrong (no postgis installed in postgis_reg).
> For details, check /tmp/pgis_reg_17211/regress_log
>
> make[1]: *** [test] Error 1
> make[1]: Leaving directory `/usr/local/src/postgis-1.1.6/regress'
> make: *** [check] Error 2
> =====
>
> The contents of /tmp/pgis_reg_17211/regress_log are not enlightening
> to me:
> =====
> CREATE DATABASE
> BEGIN
> psql:lwpostgis.sql:39: NOTICE:  type "histogram2d" is not yet defined
> DETAIL:  Creating a shell type definition.
> psql:lwpostgis.sql:39: ERROR:  could not load library "/usr/local/src/
> postgis-1.1.6/lwgeom/liblwgeom.so.1.1": libgeos_c.so.1: cannot open
> shared object file: No such file or directory
> psql:lwpostgis.sql:44: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
> psql:lwpostgis.sql:52: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
> psql:lwpostgis.sql:62: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
>
> ... snip ...
>
> psql:lwpostgis.sql:3462: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
> psql:lwpostgis.sql:3509: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
> ROLLBACK
> =====
>
> I did check the geos build, there is a step that makes libgeos_c.so.1
> in /usr/local/lib.
>
> I tried explicitly setting --with-geos-libdir=/usr/local/lib but
> there's no difference.
>
> Has anyone bumped into (and solved!) this?
>
> Thanks,
>
>         Allan
>
> --
> Allan Doyle
> +1.781.433.2695
> adoyle at eogeo.org
>
>
>
>
>
> ------------------------------
>
> Message: 3
> Date: Mon, 20 Nov 2006 23:46:04 -0000
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: RE: [postgis-users] runing postgis init scripts on a named
>         schema
> To: <dylan.beaudette at gmail.com>,        "'PostGIS Users Discussion'"
>         <postgis-users at postgis.refractions.net>
> Message-ID: <000001c70cfe$0af31dc0$05d6bed5 at oem41cbbf9e178>
> Content-Type: text/plain;       charset="us-ascii"
>
> Hi Dylan,
>
> That depends...
> What's your operating system?
> Are you compiling from sources?
>
>
> Pedro Doria Meunier
> (351) 91 302 49 72 - (351) 96 247 99 12
> MSN - pdoriam at hotmail.com
> ICQ - 308-182-126
> Skype: pdoriam
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Dylan
> Beaudette
> Sent: segunda-feira, 20 de Novembro de 2006 22:25
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] runing postgis init scripts on a named schema
>
> Hi everyone,
>
> I have a fully functional postgis DB up and running, using the
> default 'public' schema. I have just added a new schema, and would like
> to 'spatially-enable' this new one.
>
> does anyone have tips on how this might be accomplished ?
>
>
> thanks in advance!
>
> --
> Dylan Beaudette
> Soils and Biogeochemistry Graduate Group
> University of California at Davis
> 530.754.7341
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 4
> Date: Mon, 20 Nov 2006 16:30:24 -0800
> From: Dylan Beaudette <dylan.beaudette at gmail.com>
> Subject: Re: [postgis-users] runing postgis init scripts on a named
>         schema
> To: postgis-users at postgis.refractions.net
> Message-ID: <200611201630.24632.dylan.beaudette at gmail.com>
> Content-Type: text/plain;  charset="iso-8859-1"
>
> On Monday 20 November 2006 14:24, Dylan Beaudette wrote:
> > Hi everyone,
> >
> > I have a fully functional postgis DB up and running, using the
> > default 'public' schema. I have just added a new schema, and would like
> > to 'spatially-enable' this new one.
> >
> > does anyone have tips on how this might be accomplished ?
> >
> >
> > thanks in advance!
>
> note that this is on debian linux, postgres 8.1.2, postgis 1.1.1
>
> thanks
>
> --
> Dylan Beaudette
> Soils and Biogeochemistry Graduate Group
> University of California at Davis
> 530.754.7341
>
>
> ------------------------------
>
> Message: 5
> Date: Mon, 20 Nov 2006 17:19:30 -0800
> From: "Eric Shuman" <erics at ameri-title.com>
> Subject: [postgis-users] Spatial Join Performance
> To: "Postgis-Users" <postgis-users at postgis.refractions.net>
> Message-ID: <KMEIKNPGBOLILGCIHBLCKEBHCBAA.erics at ameri-title.com>
> Content-Type: text/plain;       charset="iso-8859-1"
>
> Paul,
>
> I did try using distance function instead of contains and the query
> completed much faster!  30 minutes instead of 2.5 hours. Thanks for the
> tip!!!
>
> But... =>
> Another issue was brought up earlier in this thread mentioning that not
> all
> taxlot will be in just one zone... which has now brought up another road
> block. I have been taking care of this issue by selecting distinct on
> (taxlot,zone) then grouping on the taxlot and concatenating the zone with
> a
> delimiter.
>
> The issue now is that comparing on a point won't pick up all the zones (I
> was just going to accept this), but comparing on the polygon gets false
> hits
> due to topological shifting and errors.  I am experimenting with creating
> a
> negative buffer and comparing its distance to the zone.  This works on a
> test subject lot.
>
> The road block is this...  When I try to run the query on my entire data
> set
> I get this error.
> -----------------------------------------------
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> -----------------------------------------------
>
> To speed up the buffer I have also simplified the_geom.(this works)  I
> broke
> the steps down by creating additional geometry fields in my taxlot layer
> to
> hold the simplified geometry and then the buffered geometry. The simplify
> step works, the buffer is where it bails.
>
> The taxlot geometries as multipolygon.  I ran an isvalid() on the geometry
> and found some to be invalid, but if I exclude them from the query I still
> get the server crash.
>
> Here are the queries:
> --SELECT
> AddGeometryColumn('','taxlot','the_simple_geom','-1','GEOMETRY',2);
> --update taxlot set the_simple_geom = simplify(taxlot.the_geom,5);
> --SELECT
> AddGeometryColumn('','taxlot','the_buffer_geom','-1','GEOMETRY',2);
> --update taxlot set the_buffer_geom = buffer(taxlot.the_simple_geom,-5);
> BAILS HERE
>
>
> Any ideas on what might be wrong???
>
> (should this be a new thread?)
>
> ---------------
> Eric Shuman
>
>
>
>
>
> ------------------------------
>
> Message: 6
> Date: Tue, 21 Nov 2006 13:31:49 +0100
> From: Markus Schaber <schabi at logix-tt.com>
> Subject: Re: [postgis-users] SRID for Saudi Arabia
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4562F1B5.7010304 at logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-15
>
> Hi, Stephen,
>
> Stephen Davies wrote:
> > Could somebody please point me at the correct (latlong) SRID for the
> > Riyadh region of Saudi Arabia.
>
> There are several SRIDs, depending on the projection used.
>
> E. G. all world-wide lat/lon formats like WGS85 (SRID 4326) cover Saudi
> Arabia. But there is an UTM zone, as well...
>
> So without knowing the projection information, we can't tell you the SRID.
>
> HTH,
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in Europe! www.ffii.org
> www.nosoftwarepatents.org
>
>
> ------------------------------
>
> Message: 7
> Date: Tue, 21 Nov 2006 08:44:11 -0800
> From: "Sunitha Bayana" <sbayana at sidestep.com>
> Subject: [postgis-users] lat and long format
> To: <postgis-users at postgis.refractions.net>
> Message-ID:
>         <
> 1C8FFEE4F601A74EBF7E78E38EA5E53807525C1E at mailbox.jayst.sidestep.com>
> Content-Type: text/plain; charset="us-ascii"
>
> Hi
>
>
>
>
>
>
>
> Is there any way in postgis to convert the lat and long from degree system
> to
> decimal system. I have the data in the format as below
>
>
>
>
>
>
>
> 17.21.00S
>
>
>
>
>
>
>
> 145.30.00W
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks in advance.
>
>
>
>
>
>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20061121/7d0ecda0/attachment-0001.html
>
> ------------------------------
>
> Message: 8
> Date: Tue, 21 Nov 2006 17:46:46 +0100
> From: raphael Jacquot <sxpert at sxpert.org>
> Subject: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <45632D76.6040609 at sxpert.org>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> hi there
> I'm looking for some hard verifiable proof that using r-tree based
> geometry indexes is better than using 2 columns and a btree index on it,
> performance wise.
>
> has there any study done on that ?
>
>
> ------------------------------
>
> Message: 9
> Date: Tue, 21 Nov 2006 09:12:31 -0800
> From: Martin Davis <mbdavis at refractions.net>
> Subject: Re: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4563337F.1090404 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> First of all, you need to be more specific.  The performance of an index
> is related to the kinds of queries you are making on it.   If you are
> making  a simple equality query, then a B-tree on the (x,y) pair is
> probably fine.  But if you're wanting to do range searches in two
> dimensions, a B-tree does not support this kind of query, and hence
> performance will be slow compared to an access method which does support
> this (e.g. R-tree, quad-tree, etc etc).
>
> I don't know of any reference which has a "hard verifiable" comparison
> with a simple B-tree for range queries.  I suspect this is because the
> performance difference is so obviously bad for B-trees that researchers
> haven't bothered to document it.
>
> You might try looking at two of the books by Hanan Samet:
>
> Foundations of Multidimensional and Metric Data Structures
> The Design and Analysis of Spatial Data Structures
>
> You could also try the original R-tree papers, or a survey entitled
> "R-Trees are everywhere".  Also a paper entitled "Multidimensional
> Access Methods" by Gaede et al.
>
>
> raphael Jacquot wrote:
> > hi there
> > I'm looking for some hard verifiable proof that using r-tree based
> > geometry indexes is better than using 2 columns and a btree index on
> > it, performance wise.
> >
> > has there any study done on that ?
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Martin Davis
> Senior Technical Architect
> Refractions Research, Inc.
> (250) 383-3022
>
>
>
> ------------------------------
>
> Message: 10
> Date: Tue, 21 Nov 2006 18:14:44 +0100
> From: raphael Jacquot <sxpert at sxpert.org>
> Subject: Re: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <45633404.7020604 at sxpert.org>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> Martin Davis wrote:
> > First of all, you need to be more specific.  The performance of an index
> > is related to the kinds of queries you are making on it.   If you are
> > making  a simple equality query, then a B-tree on the (x,y) pair is
> > probably fine.  But if you're wanting to do range searches in two
> > dimensions, a B-tree does not support this kind of query, and hence
> > performance will be slow compared to an access method which does support
> > this (e.g. R-tree, quad-tree, etc etc).
>
> typically I'm looking to compare using
>
> create table blah1 (
>         lon     double precision,
>         lat     double precision
> )
> with one index on lon and another one on lat
>
> the classic request being
> lon>constant1 and lon<constant2 and lat>constant3 and lat<constant4
>
> and
>
> create table blah2 (
>         position Point
> )
> with a gist r-tree index
>
> using the @ operator
>
> > raphael Jacquot wrote:
> >> hi there
> >> I'm looking for some hard verifiable proof that using r-tree based
> >> geometry indexes is better than using 2 columns and a btree index on
> >> it, performance wise.
> >>
> >> has there any study done on that ?
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
>
>
>
> ------------------------------
>
> Message: 11
> Date: Tue, 21 Nov 2006 09:22:12 -0800
> From: Martin Davis <mbdavis at refractions.net>
> Subject: Re: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <456335C4.1060503 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> Well, those are range queries.  Not too surprising, 99.9% of spatial
> queries are.
>
> The real question is: given that every modern spatial database uses some
> sort of spatial index (R-tree, quad-tree, or grid), why even bother to
> question whether B-trees might be better?
>
> raphael Jacquot wrote:
> >
> > typically I'm looking to compare using
> >
> > create table blah1 (
> >     lon    double precision,
> >     lat    double precision
> > )
> > with one index on lon and another one on lat
> >
> > the classic request being
> > lon>constant1 and lon<constant2 and lat>constant3 and lat<constant4
> >
> > and
> >
> > create table blah2 (
> >     position Point
> > )
> > with a gist r-tree index
> >
> > using the @ operator
>
> --
> Martin Davis
> Senior Technical Architect
> Refractions Research, Inc.
> (250) 383-3022
>
>
>
> ------------------------------
>
> Message: 12
> Date: Tue, 21 Nov 2006 18:21:07 +0100
> From: raphael Jacquot <sxpert at sxpert.org>
> Subject: Re: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <45633583.2010009 at sxpert.org>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> Martin Davis wrote:
> > Well, those are range queries.  Not too surprising, 99.9% of spatial
> > queries are.
> >
> > The real question is: given that every modern spatial database uses some
> > sort of spatial index (R-tree, quad-tree, or grid), why even bother to
> > question whether B-trees might be better?
>
> ask my boss :D
>
> > raphael Jacquot wrote:
> >>
> >> typically I'm looking to compare using
> >>
> >> create table blah1 (
> >>     lon    double precision,
> >>     lat    double precision
> >> )
> >> with one index on lon and another one on lat
> >>
> >> the classic request being
> >> lon>constant1 and lon<constant2 and lat>constant3 and lat<constant4
> >>
> >> and
> >>
> >> create table blah2 (
> >>     position Point
> >> )
> >> with a gist r-tree index
> >>
> >> using the @ operator
> >
>
>
>
> ------------------------------
>
> Message: 13
> Date: Tue, 21 Nov 2006 17:21:22 +0000
> From: "Nick Black" <nickblack1 at gmail.com>
> Subject: [postgis-users] Splitting multistrings
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
>         <223020e60611210921y6f4d68buaf0310e7a5fae6c5 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi,
>
> I'm trying to split multistrings into individual linestrings using SQL
> within PostGIS, but I cant find an appropriate function.  Is it
> possible to do this using SQL?  If not, does anyone know a way this
> can be done?
>
> Many Thanks
>
> Nick
>
>
> ------------------------------
>
> Message: 14
> Date: Tue, 21 Nov 2006 19:28:55 +0200
> From: "Steffen Macke" <sdteffen at gmail.com>
> Subject: Re: [postgis-users] Splitting multistrings
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
>         <3cb1691c0611210928h56023b9dtd3faea5ed6f0b9cd at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Nick,
>
> > I'm trying to split multistrings into individual linestrings using SQL
> > within PostGIS, but I cant find an appropriate function.  Is it
> > possible to do this using SQL?  If not, does anyone know a way this
> > can be done?
>
> Did you have a look at the GeometryN() function?
> Together with the PostgreSQL generate_series() and NumGeometries() you
> should be able to split your lines accordingly.
>
> Or do you have to insert new vertices at the split points? In this
> case, a little bit
> of additional information would help.
>
> Regards,
>
> Steffen
>
>
> ------------------------------
>
> Message: 15
> Date: Tue, 21 Nov 2006 18:54:53 +0100
> From: Barend K ? bben <kobben at itc.nl>
> Subject: Re: [postgis-users] Splitting multistrings
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <C188FBFD.36C0%kobben at itc.nl>
> Content-Type: text/plain;       charset="ISO-8859-1"
>
> Hi ,
>
> Find below an example how to change MultiPolygons to Polygons, Should eb
> simple to convert for (mulit)linestrings...
>
>
> __
> Barend K�bben
> International Institute for Geo-information
> Sciences and Earth Observation (ITC)
> PO Box 6, 7500AA Enschede (The Netherlands)
> ph: +31 (0)53 4874253; fax: +31 (0)53 4874335
>
> +++++++++++++
>
> --
> -- STEP 1: get maximum of NumGeometries into tmp table
> --
>
> CREATE TABLE tmp (maxnumgeoms int4) WITH OIDS;
>
> INSERT into tmp
>     (select gid from world where gid <=
>         (select max(NumGeometries(the_geom)) from world)
>     );
>
> --
> -- STEP 2: create World_exploded table
> --
>
> CREATE SEQUENCE world_exploded_id_seq
>   INCREMENT 1
>   MINVALUE 1
>   MAXVALUE 9223372036854775807
>   START 1
>   CACHE 1;
>
> CREATE TABLE world_exploded
> (
>   id int4 NOT NULL DEFAULT nextval('world_exploded_id_seq'::regclass),
>   gid int4 NOT NULL,
>   the_geom geometry,
>   CONSTRAINT world_exploded_pkey PRIMARY KEY (id),
>   CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
> 'POLYGON'::text OR the_geom IS NULL),
>   CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326)
> ) ;
>
> CREATE INDEX world_exploded_the_geom_gist
>   ON world_exploded USING gist(the_geom);
>
> CREATE INDEX world_exploded_gid
>   ON world_exploded USING btree(gid);
>
> --
> -- STEP 3: insert exploded polygons
> --
>
> insert into world_exploded (gid,the_geom)
>     select
>     gid, GeometryN(world.the_geom,maxnumgeoms)
>     from tmp,world
>
>
>
> ------------------------------
>
> Message: 16
> Date: Tue, 21 Nov 2006 10:53:24 -0800
> From: Paul Ramsey <pramsey at refractions.net>
> Subject: Re: [postgis-users] looking for some perf data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <45634B24.1070907 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-15; format=flowed
>
> It's not so much a matter of "better" as it is a matter of "good
> enough". As long as your data volume remains low enough, you can keep
> your simple non-spatial tables and b-trees. At some point you'll flip
> though and need to move to spatial for "adequate" performance.  It all
> depends on your definitions of "large" and "adequate", none of which are
>   in scope for me :)
>
> P
>
> Martin Davis wrote:
> > Well, those are range queries.  Not too surprising, 99.9% of spatial
> > queries are.
> >
> > The real question is: given that every modern spatial database uses some
> > sort of spatial index (R-tree, quad-tree, or grid), why even bother to
> > question whether B-trees might be better?
> >
> > raphael Jacquot wrote:
> >>
> >> typically I'm looking to compare using
> >>
> >> create table blah1 (
> >>     lon    double precision,
> >>     lat    double precision
> >> )
> >> with one index on lon and another one on lat
> >>
> >> the classic request being
> >> lon>constant1 and lon<constant2 and lat>constant3 and lat<constant4
> >>
> >> and
> >>
> >> create table blah2 (
> >>     position Point
> >> )
> >> with a gist r-tree index
> >>
> >> using the @ operator
> >
>
>
>
> ------------------------------
>
> Message: 17
> Date: Tue, 21 Nov 2006 13:04:07 -0600
> From: <matt.pettis at thomson.com>
> Subject: [postgis-users] centroid() and AddGeometryColumn() questions
> To: <postgis-users at postgis.refractions.net>
> Message-ID:
>         <
> 89C159F45B13A24682D98BDEF58E451F0B331CAA at TLRUSMNEAGMBX28.ERF.THOMSON.COM>
>
> Content-Type: text/plain; charset="us-ascii"
>
> Hi,
>
> I am trying to add a column to my table that lists the centroids the
> polygon described in the 'the_geom' field.  I use the following, which
> works (I think), but of which I have questions:
>
> SELECT AddGeometryColumn( '', 'shp_mcd', 'centroid', -1, 'POINT', 2 );
> UPDATE shp_mcd SET centroid = centroid ( the_geom );
>
> My question is about the final argument to AddGeometryColumn.
> Documentation states that centroid() returns a POINT.  I figure that
> that implies that the last argument should then be a 0, not a 2.  But if
> I use a 0 in the last field, the updated doesn't work -- it tells me
> that i violate a dimension constraint.  Can someone explain why I need
> to have a 2 as the last argument and not a 0?  It doesn't make sense to
> me.
>
> Thanks,
> Matt
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20061121/faf4d354/attachment-0001.html
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 49, Issue 21
> *********************************************
>



-- 
Regards,

Mark Thomas
spatialguru.net at gmail.com
205.529.9013

"Commit to the Lord whatever you do,
    and your plans will succeed." - Proverbs 16:3
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061122/f7d2d709/attachment.html>


More information about the postgis-users mailing list