[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] unsuscribe
From: "sebastian barahona" <sebastian.barahona () gmail ! com>
Date: 2006-02-17 22:58:31
Message-ID: c6eeb9e30602171458r1109f1ag () mail ! gmail ! com
[Download RAW message or body]
unsuscribe
2006/2/17, postgis-users-request@postgis.refractions.net <
postgis-users-request@postgis.refractions.net>:
>
> Send postgis-users mailing list submissions to
> postgis-users@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@postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner@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. intersection direction (David Bitner)
> 2. Re: intersection direction (Stephen Woodbridge)
> 3. Problem with intersection from postgresql 8.0.7 to 8.1.3
> win32 (Ren? F. Viancos S.)
> 4. RE: Problem with intersection from postgresql 8.0.7 to8.1.3
> win32 (Bruce Rindahl)
> 5. Re: Problem with intersection from postgresql 8.0.7 to8.1.3
> win32 (Ren? F. Viancos S.)
> 6. postgis javadoc (Laugier Vincent)
> 7. Re: Problems with restore dump datafile (Markus Schaber)
> 8. Query Problem pg 8.0.3 and 8.1.7 (Ren? F. Viancos S.)
> 9. Re: postgis javadoc (alex bodnaru)
> 10. Intersection and Geometrytype (First Last)
> 11. Re: postgis javadoc (Markus Schaber)
> 12. Re: postgis javadoc (Markus Schaber)
> 13. Partitioning spatial table (Arnaud Lesauvage)
> 14. Re: Partitioning spatial table (Markus Schaber)
> 15. Re: Partitioning spatial table (Arnaud Lesauvage)
> 16. Point within Polygon (Ezequias Rodrigues da Rocha)
> 17. Re: Point within Polygon (Paul Ramsey)
> 18. Re: Partitioning spatial table (Markus Schaber)
> 19. Re: Point within Polygon (Ezequias Rodrigues da Rocha)
> 20. Re: Point within Polygon (Paul Ramsey)
> 21. Re: Partitioning spatial table (Arnaud Lesauvage)
> 22. Re: Partitioning spatial table (Bill Binko)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 16 Feb 2006 14:51:13 -0600
> From: David Bitner <osgis.lists@gmail.com>
> Subject: [postgis-users] intersection direction
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID:
> <71c3c6c50602161251u60420c6ev19be384e58a4a7f@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Is there a way to get at directionality in an intersection result?
>
> Internally, intersection must be finding the from/to pair of each line
> segment that are crossing before it interpolates, if I could calculate
> the azimuth of each of those pairs, I could compare those and then
> (assuming from/to equates to left/right) determine if the intersecting
> line came from above or below. Any other ideas of how to get at this
> information?
>
> Basically, I have a line that I need to test other lines against and
> if they come through from one side I don't care, but from the other
> side I need to know about it.
>
>
> ------------------------------
>
> Message: 2
> Date: Thu, 16 Feb 2006 16:41:31 -0500
> From: Stephen Woodbridge <woodbri@swoodbridge.com>
> Subject: Re: [postgis-users] intersection direction
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F4F18B.4060307@swoodbridge.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> David,
>
> I think you can determine that by looking at the z value of a cross
> product of the lone segments that intersect. It will be negative or
> positive based on the direction of travel.
>
> -Steve
>
> David Bitner wrote:
> > Is there a way to get at directionality in an intersection result?
> >
> > Internally, intersection must be finding the from/to pair of each line
> > segment that are crossing before it interpolates, if I could calculate
> > the azimuth of each of those pairs, I could compare those and then
> > (assuming from/to equates to left/right) determine if the intersecting
> > line came from above or below. Any other ideas of how to get at this
> > information?
> >
> > Basically, I have a line that I need to test other lines against and
> > if they come through from one side I don't care, but from the other
> > side I need to know about it.
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
> ------------------------------
>
> Message: 3
> Date: Thu, 16 Feb 2006 19:32:48 -0300
> From: Ren? F. Viancos S. <rviancos@gmail.com>
> Subject: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to 8.1.3 win32
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <842c1e660602161432u418a6efco@mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Dear users
>
> i have the following SQL sentence that work's fine with postgreSQL 8.0.7,
> win32, and PostGIS
>
> SELECT DISTINCT(intersection((SELECT collect(the_geom) FROM ejes_13
> WHERE nombre = 'BALMACEDA' AND nom_com1 = 'PUENTE ALTO'),(SELECT
> collect(the_geom) FROM ejes_13 WHERE nombre = 'EYZAGUIRRE' AND
> nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE (nombre = 'BALMACEDA'
> OR nombre = 'EYZAGUIRRE') AND nom_com1 = 'PUENTE ALTO';
>
> this sql sentence give the point for the intersection of two streets,
> where
> "ejes_13" is the table with the street geometry, "nombre" is the field
> with
> the name of the street, and "nom_com1" is the field with the town name.
>
> when i run this sentence in postgreSQL 8.1.3, win32, and PostGIS, the data
> engine gives me the following error.
>
> ERROR: GEOS Intersection() threw an error!
>
> does somebody any ideas about this.....
>
> Regards
>
>
> --
> René F. Viáncos S.
> Director de Geomática y TIC
> Vicerrectoría de Investigación y Desarrollo
> Universidad de Chile
> Tel (56-2) 632 62 09
> Cel (56 9) 933 72 66
> rviancos@uchile.cl
> rviancos@gmail.com
> www.investigacion.uchile.cl
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/de6fef95/attachment-0001.html
>
> ------------------------------
>
> Message: 4
> Date: Thu, 16 Feb 2006 15:36:12 -0700
> From: "Bruce Rindahl" <rindahl@lrcwe.com>
> Subject: RE: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to8.1.3 win32
> To: "'PostGIS Users Discussion'"
> <postgis-users@postgis.refractions.net>
> Message-ID: <001f01c63349$64842990$2500a8c0@BRUCE>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Has the srid been set for both geometries and are they the same?
>
>
>
> -----Original Message-----
> From: postgis-users-bounces@postgis.refractions.net
> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of René
> F. Viancos S.
> Sent: Thursday, February 16, 2006 3:33 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Problem with intersection from postgresql 8.0.7
> to8.1.3 win32
>
>
>
> Dear users
>
> i have the following SQL sentence that work's fine with postgreSQL
> 8.0.7, win32, and PostGIS
>
> SELECT DISTINCT(intersection((SELECT collect(the_geom) FROM ejes_13
> WHERE nombre = 'BALMACEDA' AND nom_com1 = 'PUENTE ALTO'),(SELECT
> collect(the_geom) FROM ejes_13 WHERE nombre = 'EYZAGUIRRE' AND
> nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE (nombre = 'BALMACEDA'
> OR nombre = 'EYZAGUIRRE') AND nom_com1 = 'PUENTE ALTO';
>
> this sql sentence give the point for the intersection of two streets,
> where "ejes_13" is the table with the street geometry, "nombre" is the
> field with the name of the street, and "nom_com1" is the field with the
> town name.
>
> when i run this sentence in postgreSQL 8.1.3, win32, and PostGIS, the
> data engine gives me the following error.
>
> ERROR: GEOS Intersection() threw an error!
>
> does somebody any ideas about this.....
>
> Regards
>
>
> --
> René F. Viáncos S.
> Director de Geomática y TIC
> Vicerrectoría de Investigación y Desarrollo
> Universidad de Chile
> Tel (56-2) 632 62 09
> Cel (56 9) 933 72 66
> rviancos@uchile.cl
> rviancos@gmail.com
> www.investigacion.uchile.cl
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/d41728e5/attachment-0001.html
>
> ------------------------------
>
> Message: 5
> Date: Thu, 16 Feb 2006 19:39:07 -0300
> From: Ren? F. Viancos S. <rviancos@gmail.com>
> Subject: Re: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to8.1.3 win32
> To: rindahl@lrcwe.com, PostGIS Users Discussion
> <postgis-users@postgis.refractions.net>
> Message-ID: <842c1e660602161439i5021021bi@mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> yes, the srid's are the same, and the geometry is the same.
>
> 2006/2/16, Bruce Rindahl <rindahl@lrcwe.com>:
> >
> > Has the srid been set for both geometries and are they the same?
> >
> >
> >
> > -----Original Message-----
> > *From:* postgis-users-bounces@postgis.refractions.net [mailto:
> > postgis-users-bounces@postgis.refractions.net] *On Behalf Of *René F.
> > Viancos S.
> > *Sent:* Thursday, February 16, 2006 3:33 PM
> > *To:* PostGIS Users Discussion
> > *Subject:* [postgis-users] Problem with intersection from postgresql
> 8.0.7
> > to8.1.3 win32
> >
> >
> >
> > Dear users
> >
> > i have the following SQL sentence that work's fine with postgreSQL 8.0.7
> ,
> > win32, and PostGIS
> >
> > SELECT DISTINCT(intersection((SELECT collect(the_geom) FROM ejes_13
> > WHERE nombre = 'BALMACEDA' AND nom_com1 = 'PUENTE ALTO'),(SELECT
> > collect(the_geom) FROM ejes_13 WHERE nombre = 'EYZAGUIRRE' AND
> > nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE (nombre = 'BALMACEDA'
> > OR nombre = 'EYZAGUIRRE') AND nom_com1 = 'PUENTE ALTO';
> >
> > this sql sentence give the point for the intersection of two streets,
> > where "ejes_13" is the table with the street geometry, "nombre" is the
> field
> > with the name of the street, and "nom_com1" is the field with the town
> name.
> >
> >
> > when i run this sentence in postgreSQL 8.1.3, win32, and PostGIS, the
> data
> > engine gives me the following error.
> >
> > ERROR: GEOS Intersection() threw an error!
> >
> > does somebody any ideas about this.....
> >
> > Regards
> >
> >
> > --
> > René F. Viáncos S.
> > Director de Geomática y TIC
> > Vicerrectoría de Investigación y Desarrollo
> > Universidad de Chile
> > Tel (56-2) 632 62 09
> > Cel (56 9) 933 72 66
> > rviancos@uchile.cl
> > rviancos@gmail.com
> > www.investigacion.uchile.cl
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
>
>
> --
> René F. Viáncos S.
> Director de Geomática y TIC
> Vicerrectoría de Investigación y Desarrollo
> Universidad de Chile
> Tel (56-2) 632 62 09
> Cel (56 9) 933 72 66
> rviancos@uchile.cl
> rviancos@gmail.com
> www.investigacion.uchile.cl
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/16d4c48a/attachment-0001.html
>
> ------------------------------
>
> Message: 6
> Date: Fri, 17 Feb 2006 00:53:20 +0100
> From: Laugier Vincent <vincent.laugier@enst-bretagne.fr>
> Subject: [postgis-users] postgis javadoc
> To: postgis-users@postgis.refractions.net
> Message-ID: <43F51070.9030002@enst-bretagne.fr>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> hello,
>
> I am working on postgis and udig for my final educational project
>
> I have been looking all around the mailing list and internet for a link
> to the org.postgis javadoc. I have not found anything.
>
> I have seen that someone that was looking for it too was adviced to read
> the readme file but this is a little bit light to make some development
>
> does anyone knows the path to the javadoc ?
>
> cheers
>
> vincent
>
>
>
>
> ------------------------------
>
> Message: 7
> Date: Fri, 17 Feb 2006 01:21:54 +0100
> From: Markus Schaber <schabi@logix-tt.com>
> Subject: Re: [postgis-users] Problems with restore dump datafile
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F51722.4070001@logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi, Pablo,
>
> Pablo Silva schrieb:
>
> > could not access file "$libdir/libpostgis.so.0.8":
> > No such File or directory
>
> This is because the dump contains the old function definitions that
> reference the 0.8 backend library.
>
> > So, what's the next?, the solution for this problem
> > could be read the chapter 2 upgrade and just to do it?
> > or... I need some magics steps for this?
>
> AFAIR, the postgis upgrade script only works with binary dumps.
>
> However, you could try to edit your dump and remove all PostGIS function
> / type / table definitions, as well as the spatial_ref_sys table. You
> might also have to tweak the geometry_columns table. Then install plain
> PostGIS into a fresh database, and insert the dump there.
>
> HTH,
> Markus
>
>
> ------------------------------
>
> Message: 8
> Date: Thu, 16 Feb 2006 22:19:25 -0300
> From: Ren? F. Viancos S. <rviancos@gmail.com>
> Subject: [postgis-users] Query Problem pg 8.0.3 and 8.1.7
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Cc: jaime villanueva <jaimehvillanueva@gmail.com>, Orion Aramayo
> <orion.aramayo@gmail.com>, Ori?n Aramayo B. <
> orion.aramayo@unarte.cl>,
> Alejandro Silva <alejandro.silva.a@gmail.com>, Sebastian Barahona
> <seba.barahona@gmail.com>
> Message-ID: <842c1e660602161719j7fb3ddafv@mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Dear users, i have a problem with the following query.
>
> SELECT DISTINCT(intersection((SELECT collect(the_geom) FROM r13_ejes_32719
> WHERE nombre = 'LOS RECUERDOS'),(SELECT collect(the_geom) FROM
> r13_ejes_32719 WHERE nombre = 'LOS NOGALES'))) FROM r13_ejes_32719 WHERE
> (nombre = 'LOS RECUERDOS' OR nombre = 'LOS NOGALES');
>
> where 'r13_ejes_32719' is the table with the street data, 'nombre' the
> flied
> with the street name.
>
> In postgresql 8.0.3 works fine, but doesn't in postgresql 8.1.7 and i have
> executed the postgis_full_version() in both versions;
>
> postgresql 8.0.3, win32 binary package, has the folowing:
> POSTGIS="0.9.1" GEOS="2.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
> DBPROC="0.0.1" RELPROC="0.0.1"
>
> postgresql 8.1.7, win32 binary package, has the folowing;
> POSTGIS="1.0.4" GEOS="2.1.4" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
> DBPROC="0.3.0" RELPROC="0.3.0"
>
>
>
> In postgresql 8.0.3 the shape dumper creates this DDL
>
> CREATE TABLE "public"."r13_ejes_32719" (
> "gid" SERIAL,
> "fnode_" BIGINT,
> "tnode_" BIGINT,
> "lpoly_" BIGINT,
> "rpoly_" BIGINT,
> "length" NUMERIC,
> "svial05_" BIGINT,
> "svial05_id" BIGINT,
> "iniizq" NUMERIC(20,0),
> "terizq" NUMERIC(20,0),
> "inider" NUMERIC(20,0),
> "terder" NUMERIC(20,0),
> "nombre" VARCHAR,
> "clase" VARCHAR,
> "prefijo" VARCHAR,
> "observ" VARCHAR,
> "transito" NUMERIC(20,0),
> "id_saf" NUMERIC(20,0),
> "the_geom" "public"."geometry",
> CONSTRAINT "r13_ejes_32719_pkey" PRIMARY KEY("gid"),
> CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) =
> 'MULTILINESTRING'::text) OR (the_geom IS NULL)),
> CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = 32719)
> ) WITH OIDS;
>
>
> In postgresql 8.1.7 the shape dumper creates this DDL
>
> CREATE TABLE "public"."r13_ejes_32719" (
> "gid" SERIAL,
> "fnode_" BIGINT,
> "tnode_" BIGINT,
> "lpoly_" BIGINT,
> "rpoly_" BIGINT,
> "length" NUMERIC,
> "svial05_" BIGINT,
> "svial05_id" BIGINT,
> "iniizq" NUMERIC(20,0),
> "terizq" NUMERIC(20,0),
> "inider" NUMERIC(20,0),
> "terder" NUMERIC(20,0),
> "nombre" VARCHAR,
> "clase" VARCHAR,
> "prefijo" VARCHAR,
> "observ" VARCHAR,
> "transito" NUMERIC(20,0),
> "id_saf" NUMERIC(20,0),
> "the_geom" "public"."geometry",
> CONSTRAINT "r13_ejes_32719_pkey" PRIMARY KEY("gid"),
> CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2),
> CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) =
> 'MULTILINESTRING'::text) OR (the_geom IS NULL)),
> CONSTRAINT "enforce_srid_
> the_geom" CHECK (srid(the_geom) = 32719)
> ) WITHOUT OIDS;
>
>
> Finally, the output for the query in postgresql 8.0.3 is
>
> SRID=-1;POINT(355514.59375 6290622) (the intersection point between
> "LOS
> RECUERDOS" and "LOS NOGALES" streets)
>
> and the output in postgresql 8.1.7 is
>
> ERROR: GEOS Intersection() threw an error! (i don't know why....)
>
> Can any body help me with this problem ?
>
> Best Regards
>
>
> --
> René F. Viáncos S.
> Director de Geomática y TIC
> Vicerrectoría de Investigación y Desarrollo
> Universidad de Chile
> Tel (56-2) 632 62 09
> Cel (56 9) 933 72 66
> rviancos@uchile.cl
> rviancos@gmail.com
> www.investigacion.uchile.cl
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/0af80a78/attachment-0001.html
>
> ------------------------------
>
> Message: 9
> Date: Fri, 17 Feb 2006 04:53:18 +0200
> From: alex bodnaru <alexbodn@012.net.il>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F53A9E.9070209@alex3>
> Content-Type: text/plain; charset=us-ascii
>
>
> hi,
>
> i'm not sure, but i think it's part of the main postgis doc.
>
> hope this helps,
>
> alex
>
> Laugier Vincent wrote:
> > hello,
> >
> > I am working on postgis and udig for my final educational project
> >
> > I have been looking all around the mailing list and internet for a link
> > to the org.postgis javadoc. I have not found anything.
> >
> > I have seen that someone that was looking for it too was adviced to read
> > the readme file but this is a little bit light to make some development
> >
> > does anyone knows the path to the javadoc ?
> >
> > cheers
> >
> > vincent
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 10
> Date: Thu, 16 Feb 2006 21:41:48 -0800 (PST)
> From: First Last <y2kdis@atenista.net>
> Subject: [postgis-users] Intersection and Geometrytype
> To: postgis-users@postgis.refractions.net
> Message-ID: <20060216214148.23A9E0F3@dm22.mta.everyone.net>
> Content-Type: text/plain
>
>
> I ran the query statement below to create an intersection of the road
> network layer and country layer (scotland in particular). After
> unsuccessfully converting the resulting table into a shapefile, I found out
> that the table contains heterogenous geometry types. It consists of several
> linestrings and multilinestrings as well as a geometry collection. The
> latter contains one point and one linestring. I was curious as to why the
> query would return a geometry collection so I went on to plot the point and
> linestring that made up the geometry collection. The point turned out to be
> one of the vertices of a linestring. The linestring, on the other hand, is
> valid but I wonder why it has to be included in a geometrycollection and not
> as a separate linestring entry. Does anybody have explanation for this?
>
> Corollary to this, is there a way to force the resulting table to return
> only a specific geometrytype (e.g., linestring only) so I could skip the
> extra step in exporting it to shapefile? Right now, since I'm aware that the
> output table does not have a homogenous geometry type, I do a filter on it
> whenever I export it using pgsql2shp.
>
> I have uploaded a captured image of the country layer with the queried
> road layer in http://gislnxserver.irri.org/intx.gif . The valid
> linestrings/multilinestrings are in red while the linestring included in the
> geometrycollection is in blue. The point included in the geometrycollection
> is shown in black.
>
> This is the query statement I used to generate the table:
>
> "CREATE TABLE sc_road AS SELECT b.name, a.*, intersection(a.the_geom,
> b.memgeomunion) FROM road AS a, (SELECT name, memgeomunion(the_geom) FROM
> polbndry WHERE name='SCOTLAND' GROUP BY name) AS b WHERE a.the_geom &&
> b.memgeomunion AND intersects(a.the_geom, b.memgeomunion);"
>
> I used memgeomunion primarily because a country may be composed of several
> polygons and it should be grouped into one prior to intersecting it with the
> road layer.
>
>
> _____________________________________________________________
> Check out Atenista.Net (www.atenista.net)- new design, regular content and
> additional services!
>
>
> ------------------------------
>
> Message: 11
> Date: Fri, 17 Feb 2006 12:02:08 +0100
> From: Markus Schaber <schabi@logix-tt.com>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5AD30.9020003@logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi, Vincent,
>
> Laugier Vincent schrieb:
>
> > I am working on postgis and udig for my final educational project
> >
> > I have been looking all around the mailing list and internet for a link
> > to the org.postgis javadoc. I have not found anything.
> >
> > I have seen that someone that was looking for it too was adviced to read
> > the readme file but this is a little bit light to make some development
> >
> > does anyone knows the path to the javadoc ?
>
> Currently, there's no explicit javadoc html distributed, but you can
> easily generate it yourself from the source, and have a look at the
> example packages contained therein.
>
> It seems that I should have a look at the Java Documentation...
>
> Markus
>
>
> ------------------------------
>
> Message: 12
> Date: Fri, 17 Feb 2006 12:34:41 +0100
> From: Markus Schaber <schabi@logix-tt.com>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5B4D1.8080201@logix-tt.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hi, Vincent,
>
> The attached Makefile Patch generates basic JavaDoc documentation. It
> still spits out some warnings, and does not work with GNU jdoc, but it
> works with sun and ibm javadoc implementation.
>
> I did not commit it yet as we still need to have some thoughts:
>
> - Should we put the generated docs under doc/java instead of jdbc2/docu?
>
> - Can we cross-link it with the generated PostGIS html docs?
>
> Markus
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Makefile.patch
> Type: text/x-patch
> Size: 1543 bytes
> Desc: not available
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060217/143916fb/Makefile-0001.bin
>
> ------------------------------
>
> Message: 13
> Date: Fri, 17 Feb 2006 15:05:50 +0100
> From: Arnaud Lesauvage <thewild@freesurf.fr>
> Subject: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5D83E.1090604@freesurf.fr>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi List !
>
> I have to build a database to hold a very large spatial dataset.
> (all the roads of a country, ~5.000.000 MULTILINESTRINGS).
> I think this would make queries too slow, so I would like to
> implement partitioning.
> The document
> http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html
> talks about Constraint Exclusion, and it sounds like a very good
> idea to me.
>
> I have two options right now :
> -build one child table for every road-class in the master table
> (~10 different classes). Many queries are based on this road-class
> field.
> -build one child table for every administrative area in the master
> table (~100 administrative areas).
>
> The first case would be easy to implement (just a check constraint
> on the road-class), but there would still be some big tables (some
> road-classes have a lot more element than others).
> The second case seems better (more partitioning, smaller
> partitions, roughlu the same number of element per partition), but
> I believe it would be quite hard to implement.
> I could use a constraint like CHECK ( Within(this_geometry,
> AdministrativeAreaGeometry) ), but then querying country-wide
> would be quite difficult...
>
> At the present time, I have just one huge table with a BTree index
> on the road-class and a gist index on the spatial column, but
> simple queries like
> SELECT my_geom FROM theTable WHERE roadclass=2 AND (somebox_geom
> && my_geom)
> take ages to run...
>
> Do you have any better idea on how to implement this ?
>
> Thanks a lot for your help !
>
> Regards
> --
> Arnaud
>
>
>
> ------------------------------
>
> Message: 14
> Date: Fri, 17 Feb 2006 15:20:44 +0100
> From: Markus Schaber <schabi@logix-tt.com>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5DBBC.7030603@logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi, Arnaud,
>
> Arnaud Lesauvage schrieb:
>
> > I have to build a database to hold a very large spatial dataset.
> > (all the roads of a country, ~5.000.000 MULTILINESTRINGS).
> > I think this would make queries too slow, so I would like to implement
> > partitioning.
>
> >From my personal experience, 5 million rows do not yet justify
> partitioning, especially on road data, which usually is write once and
> then read-only.
>
> > -build one child table for every road-class in the master table (~10
> > different classes). Many queries are based on this road-class field.
>
> This partitioning will give you benefits if you often fetch only a
> single road class.
>
> > -build one child table for every administrative area in the master table
> > (~100 administrative areas).
>
> This partitioning will give you benefits if you often fetch roads from a
> single (or only a few) administrative area.
>
> > I could use a constraint like CHECK ( Within(this_geometry,
> > AdministrativeAreaGeometry) ), but then querying country-wide would be
> > quite difficult...
>
> It's difficult to find constraints that the planner can automatically
> map to your queries. If it can't, then it will still query all the
> partitions, and you don't benefit.
>
> > At the present time, I have just one huge table with a BTree index on
> > the road-class and a gist index on the spatial column, but simple
> > queries like
> > SELECT my_geom FROM theTable WHERE roadclass=2 AND (somebox_geom &&
> > my_geom)
> > take ages to run...
>
> Which indices did you put on the table? (send us the output of psql
> command "\d tablename").
>
> Have you recently VACUUMed and ANALYZed the table?
>
> Could you send us the output of "EXPLAIN ANALYZE <your query>;"? If
> estimations and reality are very different, increasing the statistics
> target will help.
>
> In case you're using older PostgreSQL versions, recreating the indices
> migth also boost performance (see the postgresql list archives under
> "index bloat").
>
> > Do you have any better idea on how to implement this ?
>
> If you don't have NULL geometries, add a NOT NULL constraint to your
> geometry column and CLUSTER your table on the geometry index.
>
> You may also want to use partial indices in addition to the full one, e.
> G.
>
> CREATE INDEX roadclass_2_ids ON theTable USING GIST (my_geom) WHERE
> roadclass=2;
>
> Having lots of indices slows down insertion and updates, and reduces
> cache efficiency, but can drastically speed up some queries, especially
> for road classes that are a few roads only.
>
> HTH,
> Markus
>
>
> ------------------------------
>
> Message: 15
> Date: Fri, 17 Feb 2006 15:46:26 +0100
> From: Arnaud Lesauvage <thewild@freesurf.fr>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5E1C2.7060805@freesurf.fr>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Markus, thanks for your answer !
>
> Markus Schaber a écrit :
> >>From my personal experience, 5 million rows do not yet justify
> > partitioning, especially on road data, which usually is write once and
> > then read-only.
>
> Actually, it was just for the benefit of the constraint exclusion
> that I wanted to try this.
>
> > Which indices did you put on the table? (send us the output of psql
> > command "\d tablename").
>
> The table has many columns, so I spare them (frc, i.e. the
> road-class, is a smallint).
>
> Index :
> «nw_pkey» PRIMARY KEY, btree (gid)
> «nw_frc_btree» btree (frc)
> «nw_nw_geometry_gist» gist (nw_geometry)
>
>
> > Have you recently VACUUMed and ANALYZed the table?
>
> Yes, and the data never changes (has you said in your post, it is
> a write once - read many dataset).
>
> > Could you send us the output of "EXPLAIN ANALYZE <your query>;"? If
> > estimations and reality are very different, increasing the statistics
> > target will help.
>
> Sure !
> "Index Scan using nw_nw_geometry_gist on nw (cost=0.00..6.02
> rows=1 width=141)"
> " Index Cond: ('<the box>'::geometry && nw_geometry)"
> " Filter: ((frc = 2) AND ('<the box>'::geometry && nw_geometry))"
>
> Reality is ~5 minutes.
> But I don't know what you mean by increasing the statistics (sorry
> for my lack of knowledge, I am new to postgresql, I am moving from
> mysql).
>
>
> > If you don't have NULL geometries, add a NOT NULL constraint to your
> > geometry column and CLUSTER your table on the geometry index.
>
> I'll check the nullity of geometries, I am not sure of this.
> How do you cluster a table on an index (again, sorry but these
> concepts are unkown in the mysql world...)
>
>
> > You may also want to use partial indices in addition to the full one, e.
> G.
>
> That sound very good to me !
> I usually filter data on both the road class AND the geometry
> location, so it definitively makes sense to filter on both !
> I'll try this ASAP !
>
> Thanks again Markus !
>
> Regards
> --
> Arnaud
>
>
>
> ------------------------------
>
> Message: 16
> Date: Fri, 17 Feb 2006 13:36:36 -0300
> From: Ezequias Rodrigues da Rocha <ezequias@recife.pe.gov.br>
> Subject: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F5FB94.30103@recife.pe.gov.br>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi list,
>
> There is any function on PostGIS that restrict the updates of some point
> layer inside another layer (polygon)?
>
>
> Sincerely...
>
> --
> Ezequias Rodrigues da Rocha
> http://ezequiasrocha.blogspot.com
> msn:ezequias@hotmail.com
> "the worst of democracies is still better than the best of dictatorship"
>
>
>
> ------------------------------
>
> Message: 17
> Date: Fri, 17 Feb 2006 09:34:18 -0800
> From: Paul Ramsey <pramsey@refractions.net>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <E07C74DE-AAF8-4C50-AE11-6A6B41AA0C34@refractions.net>
> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
>
> You could write a contraint...
> P
>
> On Feb 17, 2006, at 8:36 AM, Ezequias Rodrigues da Rocha wrote:
>
> > Hi list,
> >
> > There is any function on PostGIS that restrict the updates of some
> > point layer inside another layer (polygon)?
> >
> >
> > Sincerely...
> >
> > --
> > Ezequias Rodrigues da Rocha
> > http://ezequiasrocha.blogspot.com
> > msn:ezequias@hotmail.com
> > "the worst of democracies is still better than the best of
> > dictatorship"
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 18
> Date: Fri, 17 Feb 2006 18:41:31 +0100
> From: Markus Schaber <schabi@logix-tt.com>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F60ACB.4030905@logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi, Arnaud,
>
> Arnaud Lesauvage schrieb:
>
> >> partitioning, especially on road data, which usually is write once and
> >> then read-only.
> > Actually, it was just for the benefit of the constraint exclusion that I
> > wanted to try this.
>
> Constraint Exclusion itsself has its overhead, that's why there's a
> configuration option to enable it.
>
> >> Could you send us the output of "EXPLAIN ANALYZE <your query>;"? If
> >> estimations and reality are very different, increasing the statistics
> >> target will help.
> >
> > Sure !
> > "Index Scan using nw_nw_geometry_gist on nw (cost=0.00..6.02 rows=1
> > width=141)"
> > " Index Cond: ('<the box>'::geometry && nw_geometry)"
> > " Filter: ((frc = 2) AND ('<the box>'::geometry && nw_geometry))"
> >
> > Reality is ~5 minutes.
>
> It looks like that is the output of "EXPLAIN <query>" instead of
> "EXPLAIN ANALYZE <query>". The difference is that the latter one
> actually performs the query with some profiling, and provides the real
> times as well as the estimations.
>
> > But I don't know what you mean by increasing the statistics (sorry for
> > my lack of knowledge, I am new to postgresql, I am moving from mysql).
>
> Please see the PostgreSQL docs on
> http://www.postgresql.org/docs/8.1/static/planner-stats.html
>
> >> If you don't have NULL geometries, add a NOT NULL constraint to your
> >> geometry column and CLUSTER your table on the geometry index.
> > I'll check the nullity of geometries, I am not sure of this.
>
> The problem is that AFAIR PostGIS geometry indices (and all other GIST
> type indices) have a problem with NULL values that prevent them from
> being CLUSTERed on.
>
> > How do you cluster a table on an index (again, sorry but these concepts
> > are unkown in the mysql world...)
>
> It's all explained here:
> http://www.postgresql.org/docs/8.1/static/sql-cluster.html
>
> Btw, generally, you should look into the PostgreSQL manuals, especially
> http://www.postgresql.org/docs/8.1/static/maintenance.html - it is
> always a good adivise to read the manuals when changing to a new
> product, there are subtle differences between PostgreSQL and MySQL. (The
> same is true for DB2, Oracle, MS Sequel server etc., of course.) It will
> need some weeks or month until you can "think in PostgreS way". :-)
>
> Also, have a look at
> http://www.postgresql.org/docs/faqs.FAQ.html#item3.3 - there are some
> useful links.
>
> You can also ask the pgsql-performance@postgresql.org mailing list if
> you have specific questions that are not answered in the docs.
>
> >> You may also want to use partial indices in addition to the full one,
> >> e. G.
> > That sound very good to me !
> > I usually filter data on both the road class AND the geometry location,
> > so it definitively makes sense to filter on both !
>
> Yes, and partial indices basically do the same than constraint
> exclusion, but are more lightweight.
>
> HTH,
> Markus
>
>
> ------------------------------
>
> Message: 19
> Date: Fri, 17 Feb 2006 14:44:54 -0300
> From: Ezequias Rodrigues da Rocha <ezequias@recife.pe.gov.br>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F60B96.6060207@recife.pe.gov.br>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Sure, but could someone tell me how it works ? I am relatively newbe in
> PostgreSQL.
>
> Ezequias
> Paul Ramsey escreveu:
> > You could write a contraint...
> > P
> >
> > On Feb 17, 2006, at 8:36 AM, Ezequias Rodrigues da Rocha wrote:
> >
> >> Hi list,
> >>
> >> There is any function on PostGIS that restrict the updates of some
> >> point layer inside another layer (polygon)?
> >>
> >>
> >> Sincerely...
> >>
> >> --Ezequias Rodrigues da Rocha
> >> http://ezequiasrocha.blogspot.com
> >> msn:ezequias@hotmail.com
> >> "the worst of democracies is still better than the best of
> dictatorship"
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users@postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Ezequias Rodrigues da Rocha
> http://ezequiasrocha.blogspot.com
> msn:ezequias@hotmail.com
> "the worst of democracies is still better than the best of dictatorship"
>
>
>
> ------------------------------
>
> Message: 20
> Date: Fri, 17 Feb 2006 09:52:00 -0800
> From: Paul Ramsey <pramsey@refractions.net>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <D679C041-D59D-4EE5-917F-266E2EF358C1@refractions.net>
> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
>
> http://www.postgresql.org/docs/current/static/ddl-constraints.html
>
> On Feb 17, 2006, at 9:44 AM, Ezequias Rodrigues da Rocha wrote:
>
> > Sure, but could someone tell me how it works ? I am relatively
> > newbe in PostgreSQL.
> >
> > Ezequias
> > Paul Ramsey escreveu:
> >> You could write a contraint...
> >> P
> >>
> >> On Feb 17, 2006, at 8:36 AM, Ezequias Rodrigues da Rocha wrote:
> >>
> >>> Hi list,
> >>>
> >>> There is any function on PostGIS that restrict the updates of
> >>> some point layer inside another layer (polygon)?
> >>>
> >>>
> >>> Sincerely...
> >>>
> >>> --Ezequias Rodrigues da Rocha
> >>> http://ezequiasrocha.blogspot.com
> >>> msn:ezequias@hotmail.com
> >>> "the worst of democracies is still better than the best of
> >>> dictatorship"
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users@postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users@postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> > --
> > Ezequias Rodrigues da Rocha
> > http://ezequiasrocha.blogspot.com
> > msn:ezequias@hotmail.com
> > "the worst of democracies is still better than the best of
> > dictatorship"
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 21
> Date: Fri, 17 Feb 2006 19:11:49 +0100
> From: Arnaud Lesauvage <thewild@freesurf.fr>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F611E5.8040408@freesurf.fr>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Markus Schaber a écrit :
> > It looks like that is the output of "EXPLAIN <query>" instead of
> > "EXPLAIN ANALYZE <query>". The difference is that the latter one
> > actually performs the query with some profiling, and provides the real
> > times as well as the estimations.
>
> Yes, your are absolutely right ! Sorry for that. I am not at work
> right now, but I'll recheck this query on monday !
>
> >> How do you cluster a table on an index (again, sorry but these concepts
> >> are unkown in the mysql world...)
> >
> > It's all explained here:
> > http://www.postgresql.org/docs/8.1/static/sql-cluster.html
> >
> > Btw, generally, you should look into the PostgreSQL manuals, especially
> > http://www.postgresql.org/docs/8.1/static/maintenance.html - it is
> > always a good adivise to read the manuals when changing to a new
> > product, there are subtle differences between PostgreSQL and MySQL. (The
> > same is true for DB2, Oracle, MS Sequel server etc., of course.) It will
> > need some weeks or month until you can "think in PostgreS way". :-)
>
> Well, I have read the doc (a large part of it at least), but I
> believe I won't be able to "think in PostgreS way" until I've read
> it 2 or 3 more times. ;-)
>
> >>> You may also want to use partial indices in addition to the full one,
> >>> e. G.
> >> That sound very good to me !
> >> I usually filter data on both the road class AND the geometry location,
> >> so it definitively makes sense to filter on both !
> >
> > Yes, and partial indices basically do the same than constraint
> > exclusion, but are more lightweight.
>
> My server is reloading the data during this week-end (it needs
> some hours to do that).
> I think I will first try to cluster on the Gist index.
> If I create a multicolumn index (with the geometry column first),
> would it be a better idea to cluster the table on this index instead ?
>
>
>
> Many thanks again ! I'll have another look at the docs this
> week-end ;-)
>
> Regards
> --
> Arnaud
>
>
> ------------------------------
>
> Message: 22
> Date: Fri, 17 Feb 2006 14:26:29 -0500
> From: Bill Binko <bill@binko.net>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Message-ID: <43F62365.8050308@binko.net>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Arnaud Lesauvage wrote:
>
> > ....
>
> >
> >
> > Do you have any better idea on how to implement this ?
> >
> I think I do! I know there is already another response, and his advice
> is good... but, I have found that the partitioning solution you
> mentioned really solves other problems such as data storage, archival
> support, etc, and not necessarily query performance.
>
> There are many reasons why your query might be slow, but a few simple
> things I'd do are:
> 1) Make sure it's using the GiST index (as mentioned post a EXPLAIN
> ANALYZE and we'll help). There are many reasons including some simple
> ones like the sort_mem and shared_buffers parameters in postgresql.conf
> (which by default are just plain wrong)
> 2) Create functional indexes on any spatial functions you run (such as
> centroid() or area()) and expect to join to
> 3) Create Conditional indexes on the fields you're considering
> partitioning on. This is a Biggie, so let me explain:
>
> Lets say you have a field like admin_area_id that you were going to
> partition on. You usually add a clause like 'WHERE admin_area_id = 4"
> which will limit the rest of the query to 1/100 of the data.
>
> Rather than partitioning the table, just create partitioned
> (conditional) indexes:
>
> First create an index on the field you're going to split on
> CREATE INDEX theTable_admin_id on theTable (admin_id);
>
> Cluster it so that the rows are sorted on-disk by admin_id:
> CLUSTER theTable on theTable_admin_id;
>
> And create one GiST Index for each value:
> CREATE INDEX theTable_admin_gist_1 on theTable USING GIST (theShape
> GIST_GEOMETRY_OPS) WHERE admin_area_id=1;
> CREATE INDEX theTable_admin_gist_2 on theTable USING GIST (theShape
> GIST_GEOMETRY_OPS) WHERE admin_area_id=2;
> ...
> CREATE INDEX theTable_admin_gist_100 on theTable USING GIST (theShape
> GIST_GEOMETRY_OPS) WHERE admin_area_id=100;
>
> Now, whenever you apply the 'WHERE admin_area_id = 4' clause, the
> 'theTable_admin_gist_4' index will be used. This index will have a
> spatial index that only includes the shapes where the admin_area_id =
> 4. It will find the entries very quickly...because it only searches 1%
> of the shapes. It will load them quickly due to the CLUSTER command.
>
> One of the nicest things about this is that there is monthly
> administration of this or other cruft necessary with partitioning: just
> run VACUUM FULL ANALYZE regularly, and you're done.
>
> Hope this helps.
>
> Bill
>
>
>
> this will keep the items close together on disk, so that you get most of
> the benefits of the
>
>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 40, Issue 16
> *********************************************
>
[Attachment #3 (text/html)]
unsuscribe<br><br><div><span class="gmail_quote">2006/2/17, <a \
href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net</a> \
<<a href="mailto:postgis-users-request@postgis.refractions.net"> \
postgis-users-request@postgis.refractions.net</a>>:</span><blockquote \
class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt \
0pt 0.8ex; padding-left: 1ex;">Send postgis-users mailing list submissions to \
<br> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><br>To \
subscribe or unsubscribe via the World Wide Web, \
visit<br> <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>or, via email, \
send a message with subject or body 'help' \
to<br> <a \
href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net
</a><br><br>You can reach the person managing the list \
at<br> <a \
href="mailto:postgis-users-owner@postgis.refractions.net">postgis-users-owner@postgis.refractions.net</a><br><br>When \
replying, please edit your Subject line so it is more specific <br>than "Re: \
Contents of postgis-users digest..."<br><br><br>Today's \
Topics:<br><br> 1. intersection direction (David Bitner)<br> \
2. Re: intersection direction (Stephen Woodbridge)<br> 3. Problem with \
intersection from postgresql 8.0.7 \
to 8.1.3<br> win32 \
(Ren? F. Viancos S.)<br> 4. RE: Problem with intersection from postgresql \
8.0.7 \
to8.1.3<br> win32 (Bruce Rindahl)<br> \
5. Re: Problem with intersection from postgresql 8.0.7 \
\
to8.1.3<br> win32 (Ren? F. Viancos \
S.)<br> 6. postgis javadoc (Laugier Vincent)<br> 7. Re: \
Problems with restore dump datafile (Markus Schaber)<br> 8. Query Problem \
pg 8.0.3 and 8.1.7 (Ren? F. Viancos S.) <br> 9. Re: postgis javadoc (alex \
bodnaru)<br> 10. Intersection and Geometrytype (First \
Last)<br> 11. Re: postgis javadoc (Markus Schaber)<br> 12. Re: \
postgis javadoc (Markus Schaber)<br> 13. Partitioning spatial table \
(Arnaud Lesauvage) <br> 14. Re: Partitioning spatial table (Markus \
Schaber)<br> 15. Re: Partitioning spatial table (Arnaud \
Lesauvage)<br> 16. Point within Polygon (Ezequias Rodrigues da \
Rocha)<br> 17. Re: Point within Polygon (Paul Ramsey) <br> 18. \
Re: Partitioning spatial table (Markus Schaber)<br> 19. Re: Point within \
Polygon (Ezequias Rodrigues da Rocha)<br> 20. Re: Point within Polygon \
(Paul Ramsey)<br> 21. Re: Partitioning spatial table (Arnaud Lesauvage) \
<br> 22. Re: Partitioning spatial table (Bill \
Binko)<br><br><br>----------------------------------------------------------------------<br><br>Message: \
1<br>Date: Thu, 16 Feb 2006 14:51:13 -0600<br>From: David Bitner < <a \
href="mailto:osgis.lists@gmail.com">osgis.lists@gmail.com</a>><br>Subject: \
[postgis-users] intersection direction<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a>><br>Message-ID:<br> <<a \
href="mailto:71c3c6c50602161251u60420c6ev19be384e58a4a7f@mail.gmail.com">71c3c6c50602161251u60420c6ev19be384e58a4a7f@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1 <br><br>Is there a way to get at directionality in an \
intersection result?<br><br>Internally, intersection must be finding the from/to pair \
of each line<br>segment that are crossing before it interpolates, if I could \
calculate <br>the azimuth of each of those pairs, I could compare those and \
then<br>(assuming from/to equates to left/right) determine if the \
intersecting<br>line came from above or below. Any other ideas of how to \
get at this<br> information?<br><br>Basically, I have a line that I need to test \
other lines against and<br>if they come through from one side I don't care, but from \
the other<br>side I need to know about it.<br><br><br>------------------------------ \
<br><br>Message: 2<br>Date: Thu, 16 Feb 2006 16:41:31 -0500<br>From: Stephen \
Woodbridge <<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>><br>Subject: \
Re: [postgis-users] intersection direction<br>
To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F4F18B.4060307@swoodbridge.com">43F4F18B.4060307@swoodbridge.com
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; \
format=flowed<br><br>David,<br><br>I think you can determine that by looking at the z \
value of a cross<br>product of the lone segments that intersect. It will \
be negative or <br>positive based on the direction of \
travel.<br><br>-Steve<br><br>David Bitner wrote:<br>> Is there a way to get at \
directionality in an intersection result?<br>><br>> Internally, intersection \
must be finding the from/to pair of each line <br>> segment that are crossing \
before it interpolates, if I could calculate<br>> the azimuth of each of those \
pairs, I could compare those and then<br>> (assuming from/to equates to \
left/right) determine if the intersecting <br>> line came from above or \
below. Any other ideas of how to get at this<br>> \
information?<br>><br>> Basically, I have a line that I need to test other lines \
against and<br>> if they come through from one side I don't care, but from the \
other <br>> side I need to know about it.<br>> \
_______________________________________________<br>> postgis-users mailing \
list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br><br><br>------------------------------<br><br>Message: \
3 <br>Date: Thu, 16 Feb 2006 19:32:48 -0300<br>From: Ren? F. Viancos S. <<a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a>><br>Subject: \
[postgis-users] Problem with intersection from \
postgresql<br> 8.0.7 \
to 8.1.3 win32<br>To: PostGIS Users \
Discussion <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:842c1e660602161432u418a6efco@mail.gmail.com"> \
842c1e660602161432u418a6efco@mail.gmail.com</a>><br>Content-Type: text/plain; \
charset="iso-8859-1"<br><br>Dear users<br><br>i have the following SQL \
sentence that work's fine with postgreSQL 8.0.7,<br>win32, and PostGIS <br><br>SELECT \
DISTINCT(intersection((SELECT collect(the_geom) FROM ejes_13<br>WHERE nombre = \
'BALMACEDA' AND nom_com1 = 'PUENTE ALTO'),(SELECT<br>collect(the_geom) FROM ejes_13 \
WHERE nombre = 'EYZAGUIRRE' AND<br>nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE \
(nombre = 'BALMACEDA' <br>OR nombre = 'EYZAGUIRRE') AND nom_com1 = 'PUENTE \
ALTO';<br><br>this sql sentence give the point for the intersection of two streets, \
where<br>"ejes_13" is the table with the street geometry, \
"nombre" is the field with <br>the name of the street, and \
"nom_com1" is the field with the town name.<br><br>when i run this sentence \
in postgreSQL 8.1.3, win32, and PostGIS, the data<br>engine gives me the following \
error.<br><br>ERROR: GEOS Intersection() threw an error! <br><br>does somebody any \
ideas about this.....<br><br>Regards<br><br><br>--<br>René F. Viáncos S.<br>Director \
de Geomática y TIC<br>Vicerrectoría de Investigación y Desarrollo<br>Universidad de \
Chile<br>Tel (56-2) 632 62 09 <br>Cel (56 9) 933 72 66<br><a \
href="mailto:rviancos@uchile.cl">rviancos@uchile.cl</a><br><a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a><br><a \
href="http://www.investigacion.uchile.cl">www.investigacion.uchile.cl \
</a><br>-------------- next part --------------<br>An HTML attachment was \
scrubbed...<br>URL: <a \
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/de6fef \
95/attachment-0001.html">http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/de6fef95/attachment-0001.html
</a><br><br>------------------------------<br><br>Message: 4<br>Date: Thu, 16 Feb \
2006 15:36:12 -0700<br>From: "Bruce Rindahl" <<a \
href="mailto:rindahl@lrcwe.com">rindahl@lrcwe.com</a>><br>Subject: RE: \
[postgis-users] Problem with intersection from postgresql \
<br> 8.0.7 to8.1.3 \
win32<br>To: "'PostGIS Users \
Discussion'"<br> <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<001f01c63349$64842990$2500a8c0@BRUCE ><br>Content-Type: text/plain; \
charset="iso-8859-1"<br><br>Has the srid been set for both geometries and \
are they the same?<br><br><br><br>-----Original Message-----<br>From: <a \
href="mailto:postgis-users-bounces@postgis.refractions.net"> \
postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a \
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] \
On Behalf Of René<br>F. Viancos S.<br>Sent: Thursday, February 16, 2006 3:33 PM \
<br>To: PostGIS Users Discussion<br>Subject: [postgis-users] Problem with \
intersection from postgresql 8.0.7<br>to8.1.3 win32<br><br><br><br>Dear \
users<br><br>i have the following SQL sentence that work's fine with postgreSQL \
<br>8.0.7, win32, and PostGIS<br><br>SELECT DISTINCT(intersection((SELECT \
collect(the_geom) FROM ejes_13<br>WHERE nombre = 'BALMACEDA' AND nom_com1 = 'PUENTE \
ALTO'),(SELECT<br>collect(the_geom) FROM ejes_13 WHERE nombre = 'EYZAGUIRRE' AND \
<br>nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE (nombre = 'BALMACEDA'<br>OR nombre \
= 'EYZAGUIRRE') AND nom_com1 = 'PUENTE ALTO';<br><br>this sql sentence give the point \
for the intersection of two streets,<br>where "ejes_13" is the table with \
the street geometry, "nombre" is the <br>field with the name of the street, \
and "nom_com1" is the field with the<br>town name.<br><br>when i run this \
sentence in postgreSQL 8.1.3, win32, and PostGIS, the<br>data engine gives me the \
following error. <br><br>ERROR: GEOS Intersection() threw an error!<br><br>does \
somebody any ideas about this.....<br><br>Regards<br><br><br>--<br>René F. Viáncos \
S.<br>Director de Geomática y TIC<br>Vicerrectoría de Investigación y Desarrollo \
<br>Universidad de Chile<br>Tel (56-2) 632 62 09<br>Cel (56 9) 933 72 66<br><a \
href="mailto:rviancos@uchile.cl">rviancos@uchile.cl</a><br><a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a><br><a \
href="http://www.investigacion.uchile.cl"> \
www.investigacion.uchile.cl</a><br><br>-------------- next part --------------<br>An \
HTML attachment was scrubbed...<br>URL: <a \
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/d41728e5/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/d41728e5/attachment-0001.html</a><br><br>------------------------------<br><br>Message: \
5<br>Date: Thu, 16 Feb 2006 19:39:07 -0300<br>From: Ren? F. Viancos S. < <a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a>><br>Subject: Re: \
[postgis-users] Problem with intersection from \
postgresql<br> 8.0.7 \
to8.1.3 win32<br>To: <a href="mailto:rindahl@lrcwe.com">rindahl@lrcwe.com \
</a>, PostGIS Users \
Discussion<br> <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:842c1e660602161439i5021021bi@mail.gmail.com"> \
842c1e660602161439i5021021bi@mail.gmail.com</a>><br>Content-Type: text/plain; \
charset="iso-8859-1"<br><br>yes, the srid's are the same, and the geometry \
is the same.<br><br>2006/2/16, Bruce Rindahl <<a href="mailto:rindahl@lrcwe.com"> \
rindahl@lrcwe.com</a>>:<br>><br>> Has the srid been set for both \
geometries and are they the same?<br>><br>><br>><br>> -----Original \
Message-----<br>> *From:* <a \
href="mailto:postgis-users-bounces@postgis.refractions.net"> \
postgis-users-bounces@postgis.refractions.net</a> [mailto:<br>> <a \
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] \
*On Behalf Of *René F.<br>> Viancos S. <br>> *Sent:* Thursday, February 16, \
2006 3:33 PM<br>> *To:* PostGIS Users Discussion<br>> *Subject:* \
[postgis-users] Problem with intersection from postgresql 8.0.7<br>> to8.1.3 \
win32<br>><br>><br>><br> > Dear users<br>><br>> i have the \
following SQL sentence that work's fine with postgreSQL 8.0.7,<br>> win32, and \
PostGIS<br>><br>> SELECT DISTINCT(intersection((SELECT collect(the_geom) FROM \
ejes_13<br>> WHERE nombre = 'BALMACEDA' AND nom_com1 = 'PUENTE ALTO'),(SELECT \
<br>> collect(the_geom) FROM ejes_13 WHERE nombre = 'EYZAGUIRRE' AND<br>> \
nom_com1 = 'PUENTE ALTO'))) FROM ejes_13 WHERE (nombre = 'BALMACEDA'<br>> OR \
nombre = 'EYZAGUIRRE') AND nom_com1 = 'PUENTE ALTO';<br>><br> > this sql \
sentence give the point for the intersection of two streets,<br>> where \
"ejes_13" is the table with the street geometry, "nombre" is the \
field<br>> with the name of the street, and "nom_com1" is the field with \
the town name. <br>><br>><br>> when i run this sentence in postgreSQL 8.1.3, \
win32, and PostGIS, the data<br>> engine gives me the following \
error.<br>><br>> ERROR: GEOS Intersection() threw an error!<br>><br>> \
does somebody any ideas about this..... <br>><br>> \
Regards<br>><br>><br>> --<br>> René F. Viáncos S.<br>> Director de \
Geomática y TIC<br>> Vicerrectoría de Investigación y Desarrollo<br>> \
Universidad de Chile<br>> Tel (56-2) 632 62 09 <br>> Cel (56 9) 933 72 \
66<br>> <a href="mailto:rviancos@uchile.cl">rviancos@uchile.cl</a><br>> <a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a><br>> <a \
href="http://www.investigacion.uchile.cl">www.investigacion.uchile.cl \
</a><br>><br>> _______________________________________________<br>> \
postgis-users mailing list<br>> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> \
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>><br><br><br>--<br>René \
F. Viáncos S.<br>Director de Geomática y TIC<br>Vicerrectoría de Investigación y \
Desarrollo<br>Universidad de Chile <br>Tel (56-2) 632 62 09<br>Cel (56 9) 933 72 \
66<br><a href="mailto:rviancos@uchile.cl">rviancos@uchile.cl</a><br><a \
href="mailto:rviancos@gmail.com">rviancos@gmail.com</a><br><a \
href="http://www.investigacion.uchile.cl"> \
www.investigacion.uchile.cl</a><br>-------------- next part --------------<br>An HTML \
attachment was scrubbed...<br>URL: <a \
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/16d4c48a/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/16d4c48a/attachment-0001.html</a><br><br>------------------------------<br><br>Message: \
6<br>Date: Fri, 17 Feb 2006 00:53:20 +0100<br>From: Laugier Vincent < <a \
href="mailto:vincent.laugier@enst-bretagne.fr">vincent.laugier@enst-bretagne.fr</a>><br>Subject: \
[postgis-users] postgis javadoc<br>To: <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>Message-ID: <<a \
href="mailto:43F51070.9030002@enst-bretagne.fr">43F51070.9030002@enst-bretagne.fr</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1; format=flowed<br><br>hello,<br><br>I am working on \
postgis and udig for my final educational project <br><br>I have been looking all \
around the mailing list and internet for a link<br>to the org.postgis javadoc. I have \
not found anything.<br><br>I have seen that someone that was looking for it too was \
adviced to read<br> the readme file but this is a little bit light to make some \
development<br><br>does anyone knows the path to the javadoc \
?<br><br>cheers<br><br>vincent<br><br><br><br><br>------------------------------<br><br>Message: \
7<br>
Date: Fri, 17 Feb 2006 01:21:54 +0100<br>From: Markus Schaber <<a \
href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>><br>Subject: Re: \
[postgis-users] Problems with restore dump datafile<br>To: PostGIS Users Discussion \
< <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F51722.4070001@logix-tt.com">43F51722.4070001@logix-tt.com</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1 <br><br>Hi, Pablo,<br><br>Pablo Silva \
schrieb:<br><br>> could not access file \
"$libdir/libpostgis.so.0.8":<br>> No such File or directory<br><br>This \
is because the dump contains the old function definitions that <br>reference the 0.8 \
backend library.<br><br>> So, what's the next?, the solution for this \
problem<br>> could be read the chapter 2 upgrade and just to do it?<br>> or... \
I need some magics steps for this?<br><br> AFAIR, the postgis upgrade script only \
works with binary dumps.<br><br>However, you could try to edit your dump and remove \
all PostGIS function<br>/ type / table definitions, as well as the spatial_ref_sys \
table. You<br>might also have to tweak the geometry_columns table. Then install plain \
<br>PostGIS into a fresh database, and insert the dump \
there.<br><br>HTH,<br>Markus<br><br><br>------------------------------<br><br>Message: \
8<br>Date: Thu, 16 Feb 2006 22:19:25 -0300<br>From: Ren? F. Viancos S. <<a \
href="mailto:rviancos@gmail.com"> rviancos@gmail.com</a>><br>Subject: \
[postgis-users] Query Problem pg 8.0.3 and 8.1.7<br>To: PostGIS Users Discussion \
<<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>>
<br>Cc: jaime villanueva <<a \
href="mailto:jaimehvillanueva@gmail.com">jaimehvillanueva@gmail.com</a>>, Orion \
Aramayo<br> <<a \
href="mailto:orion.aramayo@gmail.com">orion.aramayo@gmail.com</a>>, Ori?n Aramayo \
B. < <a href="mailto:orion.aramayo@unarte.cl">orion.aramayo@unarte.cl</a>>,<br> Alejandro \
Silva <<a href="mailto:alejandro.silva.a@gmail.com">alejandro.silva.a@gmail.com</a>>, Sebastian \
Barahona<br> < <a \
href="mailto:seba.barahona@gmail.com">seba.barahona@gmail.com</a>><br>Message-ID: \
<<a href="mailto:842c1e660602161719j7fb3ddafv@mail.gmail.com">842c1e660602161719j7fb3ddafv@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="iso-8859-1" <br><br>Dear users, i have a problem with \
the following query.<br><br>SELECT DISTINCT(intersection((SELECT collect(the_geom) \
FROM r13_ejes_32719<br>WHERE nombre = 'LOS RECUERDOS'),(SELECT collect(the_geom) \
FROM<br>r13_ejes_32719 WHERE nombre = 'LOS NOGALES'))) FROM r13_ejes_32719 WHERE \
<br>(nombre = 'LOS RECUERDOS' OR nombre = 'LOS NOGALES');<br><br>where \
'r13_ejes_32719' is the table with the street data, 'nombre' the flied<br>with the \
street name.<br><br>In postgresql 8.0.3 works fine, but doesn't in postgresql 8.1.7 \
and i have<br>executed the postgis_full_version() in both versions;<br><br>postgresql \
8.0.3, win32 binary package, has the folowing:<br>POSTGIS="0.9.1" \
GEOS="2.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS \
<br>DBPROC="0.0.1" RELPROC="0.0.1"<br><br>postgresql 8.1.7, win32 \
binary package, has the folowing;<br>POSTGIS="1.0.4" GEOS="2.1.4" \
PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS <br>DBPROC="0.3.0" \
RELPROC="0.3.0"<br><br><br><br>In postgresql 8.0.3 the shape dumper creates \
this DDL<br><br>CREATE TABLE "public"."r13_ejes_32719" \
(<br> "gid" SERIAL,<br> "fnode_" \
BIGINT,<br> "tnode_" BIGINT,<br> "lpoly_" \
BIGINT,<br> "rpoly_" BIGINT,<br> "length" \
NUMERIC,<br> "svial05_" \
BIGINT,<br> "svial05_id" BIGINT, \
<br> "iniizq" NUMERIC(20,0),<br> "terizq" \
NUMERIC(20,0),<br> "inider" \
NUMERIC(20,0),<br> "terder" \
NUMERIC(20,0),<br> "nombre" \
VARCHAR,<br> "clase" VARCHAR, \
<br> "prefijo" VARCHAR,<br> "observ" \
VARCHAR,<br> "transito" \
NUMERIC(20,0),<br> "id_saf" \
NUMERIC(20,0),<br> "the_geom" \
"public"."geometry",<br> CONSTRAINT \
"r13_ejes_32719_pkey" PRIMARY KEY("gid"), \
<br> CONSTRAINT "enforce_geotype_the_geom" CHECK \
((geometrytype(the_geom) =<br>'MULTILINESTRING'::text) OR (the_geom IS \
NULL)),<br> CONSTRAINT "enforce_srid_the_geom" CHECK \
(srid(the_geom) = 32719)<br> ) WITH OIDS;<br><br><br>In postgresql 8.1.7 the shape \
dumper creates this DDL<br><br>CREATE TABLE \
"public"."r13_ejes_32719" (<br> "gid" \
SERIAL,<br> "fnode_" BIGINT,<br> "tnode_" \
BIGINT, <br> "lpoly_" BIGINT,<br> "rpoly_" \
BIGINT,<br> "length" \
NUMERIC,<br> "svial05_" \
BIGINT,<br> "svial05_id" \
BIGINT,<br> "iniizq" \
NUMERIC(20,0),<br> "terizq" NUMERIC(20,0), \
<br> "inider" NUMERIC(20,0),<br> "terder" \
NUMERIC(20,0),<br> "nombre" \
VARCHAR,<br> "clase" VARCHAR,<br> "prefijo" \
VARCHAR,<br> "observ" \
VARCHAR,<br> "transito" NUMERIC(20,0), \
<br> "id_saf" NUMERIC(20,0),<br> "the_geom" \
"public"."geometry",<br> CONSTRAINT \
"r13_ejes_32719_pkey" PRIMARY \
KEY("gid"),<br> CONSTRAINT "enforce_dims_the_geom" \
CHECK (ndims(the_geom) = 2), <br> CONSTRAINT \
"enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) \
=<br>'MULTILINESTRING'::text) OR (the_geom IS NULL)),<br> CONSTRAINT \
"enforce_srid_<br>the_geom" CHECK (srid(the_geom) = 32719) <br>) WITHOUT \
OIDS;<br><br><br>Finally, the output for the query in postgresql 8.0.3 \
is<br><br>SRID=-1;POINT(355514.59375 6290622) (the \
intersection point between "LOS<br>RECUERDOS" and "LOS NOGALES" \
streets) <br><br>and the output in postgresql 8.1.7 is<br><br>ERROR: GEOS \
Intersection() threw an error! (i don't know why....)<br><br>Can any body help me \
with this problem ?<br><br>Best Regards<br><br><br>--<br>René F. Viáncos S. \
<br>Director de Geomática y TIC<br>Vicerrectoría de Investigación y \
Desarrollo<br>Universidad de Chile<br>Tel (56-2) 632 62 09<br>Cel (56 9) 933 72 \
66<br><a href="mailto:rviancos@uchile.cl">rviancos@uchile.cl</a><br><a \
href="mailto:rviancos@gmail.com"> rviancos@gmail.com</a><br><a \
href="http://www.investigacion.uchile.cl">www.investigacion.uchile.cl</a><br>-------------- \
next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a \
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/0af80a78/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20060216/0af80a78/attachment-0001.html</a><br><br>------------------------------<br><br>Message: \
9<br>Date: Fri, 17 Feb 2006 04:53:18 +0200<br>From: alex bodnaru < <a \
href="mailto:alexbodn@012.net.il">alexbodn@012.net.il</a>><br>Subject: Re: \
[postgis-users] postgis javadoc<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a>><br>Message-ID: <43F53A9E.9070209@alex3><br>Content-Type: text/plain; \
charset=us-ascii<br><br><br>hi,<br><br>i'm not sure, but i think it's part of the \
main postgis doc.<br><br>hope this helps,<br><br>alex<br> <br>Laugier Vincent \
wrote:<br>> hello,<br>><br>> I am working on postgis and udig for my final \
educational project<br>><br>> I have been looking all around the mailing list \
and internet for a link<br>> to the org.postgis javadoc. I have not found \
anything.<br>><br>> I have seen that someone that was looking for it too was \
adviced to read<br>> the readme file but this is a little bit light to make some \
development<br>> <br>> does anyone knows the path to the javadoc \
?<br>><br>> cheers<br>><br>> vincent<br>><br>><br>> \
_______________________________________________<br>> postgis-users mailing \
list<br>> <a href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a><br>> <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.re \
fractions.net/mailman/listinfo/postgis-users</a><br>><br><br><br>------------------------------
<br><br>Message: 10<br>Date: Thu, 16 Feb 2006 21:41:48 -0800 (PST)<br>From: First \
Last <<a href="mailto:y2kdis@atenista.net">y2kdis@atenista.net</a>><br>Subject: \
[postgis-users] Intersection and Geometrytype<br>To: <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Message-ID: \
<<a href="mailto:20060216214148.23A9E0F3@dm22.mta.everyone.net">20060216214148.23A9E0F3@dm22.mta.everyone.net
</a>><br>Content-Type: text/plain<br><br><br>I ran the query statement below to \
create an intersection of the road network layer and country layer (scotland in \
particular). After unsuccessfully converting the resulting table into a shapefile, I \
found out that the table contains heterogenous geometry types. It consists of several \
linestrings and multilinestrings as well as a geometry collection. The latter \
contains one point and one linestring. I was curious as to why the query would return \
a geometry collection so I went on to plot the point and linestring that made up the \
geometry collection. The point turned out to be one of the vertices of a linestring. \
The linestring, on the other hand, is valid but I wonder why it has to be included in \
a geometrycollection and not as a separate linestring entry. Does anybody have \
explanation for this? <br><br>Corollary to this, is there a way to force the \
resulting table to return only a specific geometrytype (e.g., linestring only) so I \
could skip the extra step in exporting it to shapefile? Right now, since I'm aware \
that the output table does not have a homogenous geometry type, I do a filter on it \
whenever I export it using pgsql2shp. <br><br>I have uploaded a captured image of the \
country layer with the queried road layer in <a \
href="http://gislnxserver.irri.org/intx.gif">http://gislnxserver.irri.org/intx.gif</a> \
. The valid linestrings/multilinestrings are in red while the linestring included in \
the geometrycollection is in blue. The point included in the geometrycollection is \
shown in black. <br><br>This is the query statement I used to generate the \
table:<br><br>"CREATE TABLE sc_road AS SELECT <a \
href="http://b.name">b.name</a>, a.*, intersection(a.the_geom, b.memgeomunion) FROM \
road AS a, (SELECT name, memgeomunion(the_geom) FROM polbndry WHERE name='SCOTLAND' \
GROUP BY name) AS b WHERE a.the_geom && b.memgeomunion AND \
intersects(a.the_geom, b.memgeomunion);"<br><br>I used memgeomunion primarily \
because a country may be composed of several polygons and it should be grouped into \
one prior to intersecting it with the road layer. \
<br><br><br>_____________________________________________________________<br>Check \
out Atenista.Net (<a href="http://www.atenista.net">www.atenista.net</a>)- new \
design, regular content and additional services!<br><br><br>
------------------------------<br><br>Message: 11<br>Date: Fri, 17 Feb 2006 12:02:08 \
+0100<br>From: Markus Schaber <<a \
href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>><br>Subject: Re: \
[postgis-users] postgis javadoc <br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F5AD30.9020003@logix-tt.com">43F5AD30.9020003@logix-tt.com \
</a>><br>Content-Type: text/plain; charset=ISO-8859-1<br><br>Hi, \
Vincent,<br><br>Laugier Vincent schrieb:<br><br>> I am working on postgis and udig \
for my final educational project<br>><br>> I have been looking all around the \
mailing list and internet for a link <br>> to the org.postgis javadoc. I have not \
found anything.<br>><br>> I have seen that someone that was looking for it too \
was adviced to read<br>> the readme file but this is a little bit light to make \
some development <br>><br>> does anyone knows the path to the javadoc \
?<br><br>Currently, there's no explicit javadoc html distributed, but you \
can<br>easily generate it yourself from the source, and have a look at the<br>example \
packages contained therein. <br><br>It seems that I should have a look at the Java \
Documentation...<br><br>Markus<br><br><br>------------------------------<br><br>Message: \
12<br>Date: Fri, 17 Feb 2006 12:34:41 +0100<br>From: Markus Schaber <<a \
href="mailto:schabi@logix-tt.com"> schabi@logix-tt.com</a>><br>Subject: Re: \
[postgis-users] postgis javadoc<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
< <a href="mailto:43F5B4D1.8080201@logix-tt.com">43F5B4D1.8080201@logix-tt.com</a>><br>Content-Type: \
text/plain; charset="iso-8859-1"<br><br>Hi, Vincent,<br><br>The attached \
Makefile Patch generates basic JavaDoc documentation. It <br>still spits out some \
warnings, and does not work with GNU jdoc, but it<br>works with sun and ibm javadoc \
implementation.<br><br>I did not commit it yet as we still need to have some \
thoughts:<br><br>- Should we put the generated docs under doc/java instead of \
jdbc2/docu? <br><br>- Can we cross-link it with the generated PostGIS html \
docs?<br><br>Markus<br>-------------- next part --------------<br>A non-text \
attachment was scrubbed...<br>Name: Makefile.patch<br>Type: text/x-patch<br>Size: \
1543 bytes <br>Desc: not available<br>Url : <a \
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20060217/143916 \
fb/Makefile-0001.bin">http://lists.refractions.net/pipermail/postgis-users/attachments/20060217/143916fb/Makefile-0001.bin
</a><br><br>------------------------------<br><br>Message: 13<br>Date: Fri, 17 Feb \
2006 15:05:50 +0100<br>From: Arnaud Lesauvage <<a \
href="mailto:thewild@freesurf.fr">thewild@freesurf.fr</a>><br>Subject: \
[postgis-users] Partitioning spatial table <br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F5D83E.1090604@freesurf.fr">43F5D83E.1090604@freesurf.fr \
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi \
List !<br><br>I have to build a database to hold a very large spatial \
dataset.<br>(all the roads of a country, ~5.000.000 MULTILINESTRINGS). <br>I think \
this would make queries too slow, so I would like to<br>implement \
partitioning.<br>The document<br><a \
href="http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html">http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html
</a><br>talks about Constraint Exclusion, and it sounds like a very good<br>idea to \
me.<br><br>I have two options right now :<br>-build one child table for every \
road-class in the master table<br>(~10 different classes). Many queries are based on \
this road-class <br>field.<br>-build one child table for every administrative area in \
the master<br>table (~100 administrative areas).<br><br>The first case would be easy \
to implement (just a check constraint<br>on the road-class), but there would still be \
some big tables (some <br>road-classes have a lot more element than others).<br>The \
second case seems better (more partitioning, smaller<br>partitions, roughlu the same \
number of element per partition), but<br>I believe it would be quite hard to \
implement. <br>I could use a constraint like CHECK ( \
Within(this_geometry,<br>AdministrativeAreaGeometry) ), but then querying \
country-wide<br>would be quite difficult...<br><br>At the present time, I have just \
one huge table with a BTree index <br>on the road-class and a gist index on the \
spatial column, but<br>simple queries like<br>SELECT my_geom FROM theTable WHERE \
roadclass=2 AND (somebox_geom<br>&& my_geom)<br>take ages to run...<br><br>Do \
you have any better idea on how to implement this ? <br><br>Thanks a lot for your \
help !<br><br>Regards<br>--<br>Arnaud<br><br><br><br>------------------------------<br><br>Message: \
14<br>Date: Fri, 17 Feb 2006 15:20:44 +0100<br>From: Markus Schaber <<a \
href="mailto:schabi@logix-tt.com"> schabi@logix-tt.com</a>><br>Subject: Re: \
[postgis-users] Partitioning spatial table<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>>
<br>Message-ID: <<a \
href="mailto:43F5DBBC.7030603@logix-tt.com">43F5DBBC.7030603@logix-tt.com</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1<br><br>Hi, Arnaud,<br><br>Arnaud Lesauvage \
schrieb:<br><br>> I have to build a database to hold a very large spatial dataset. \
<br>> (all the roads of a country, ~5.000.000 MULTILINESTRINGS).<br>> I think \
this would make queries too slow, so I would like to implement<br>> \
partitioning.<br><br>>From my personal experience, 5 million rows do not yet \
justify <br>partitioning, especially on road data, which usually is write once \
and<br>then read-only.<br><br>> -build one child table for every road-class in the \
master table (~10<br>> different classes). Many queries are based on this \
road-class field. <br><br>This partitioning will give you benefits if you often fetch \
only a<br>single road class.<br><br>> -build one child table for every \
administrative area in the master table<br>> (~100 administrative areas).<br> \
<br>This partitioning will give you benefits if you often fetch roads from \
a<br>single (or only a few) administrative area.<br><br>> I could use a constraint \
like CHECK ( Within(this_geometry,<br>> AdministrativeAreaGeometry) ), but then \
querying country-wide would be <br>> quite difficult...<br><br>It's difficult to \
find constraints that the planner can automatically<br>map to your queries. If it \
can't, then it will still query all the<br>partitions, and you don't \
benefit.<br><br>> At the present time, I have just one huge table with a BTree \
index on <br>> the road-class and a gist index on the spatial column, but \
simple<br>> queries like<br>> SELECT my_geom FROM theTable WHERE roadclass=2 \
AND (somebox_geom &&<br>> my_geom)<br>> take ages to run... \
<br><br>Which indices did you put on the table? (send us the output of \
psql<br>command "\d tablename").<br><br>Have you recently VACUUMed and \
ANALYZed the table?<br><br>Could you send us the output of "EXPLAIN ANALYZE \
<your query>;"? If <br>estimations and reality are very different, \
increasing the statistics<br>target will help.<br><br>In case you're using older \
PostgreSQL versions, recreating the indices<br>migth also boost performance (see the \
postgresql list archives under <br>"index bloat").<br><br>> Do you have \
any better idea on how to implement this ?<br><br>If you don't have NULL geometries, \
add a NOT NULL constraint to your<br>geometry column and CLUSTER your table on the \
geometry index. <br><br>You may also want to use partial indices in addition to the \
full one, e. G.<br><br>CREATE INDEX roadclass_2_ids ON theTable USING GIST (my_geom) \
WHERE<br>roadclass=2;<br><br>Having lots of indices slows down insertion and updates, \
and reduces <br>cache efficiency, but can drastically speed up some queries, \
especially<br>for road classes that are a few roads \
only.<br><br>HTH,<br>Markus<br><br><br>------------------------------<br><br>Message: \
15<br>Date: Fri, 17 Feb 2006 15:46:26 +0100 <br>From: Arnaud Lesauvage <<a \
href="mailto:thewild@freesurf.fr">thewild@freesurf.fr</a>><br>Subject: Re: \
[postgis-users] Partitioning spatial table<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a \
href="mailto:43F5E1C2.7060805@freesurf.fr">43F5E1C2.7060805@freesurf.fr</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi Markus, thanks for your \
answer ! <br><br>Markus Schaber a écrit :<br>>>From my personal experience, 5 \
million rows do not yet justify<br>> partitioning, especially on road data, which \
usually is write once and<br>> then read-only.<br><br>Actually, it was just for \
the benefit of the constraint exclusion <br>that I wanted to try this.<br><br>> \
Which indices did you put on the table? (send us the output of psql<br>> command \
"\d tablename").<br><br>The table has many columns, so I spare them (frc, \
i.e. the<br> road-class, is a smallint).<br><br>Index :<br> \
«nw_pkey» PRIMARY KEY, btree (gid)<br> «nw_frc_btree» btree \
(frc)<br> «nw_nw_geometry_gist» gist \
(nw_geometry)<br><br><br>> Have you recently VACUUMed and ANALYZed the table? \
<br><br>Yes, and the data never changes (has you said in your post, it is<br>a write \
once - read many dataset).<br><br>> Could you send us the output of "EXPLAIN \
ANALYZE <your query>;"? If<br>> estimations and reality are very \
different, increasing the statistics <br>> target will help.<br><br>Sure \
!<br>"Index Scan using nw_nw_geometry_gist on \
nw (cost=0.00..6.02<br>rows=1 width=141)"<br>" Index \
Cond: ('<the box>'::geometry && nw_geometry)"<br> \
" Filter: ((frc = 2) AND ('<the box>'::geometry && \
nw_geometry))"<br><br>Reality is ~5 minutes.<br>But I don't know what you mean \
by increasing the statistics (sorry<br>for my lack of knowledge, I am new to \
postgresql, I am moving from <br>mysql).<br><br><br>> If you don't have NULL \
geometries, add a NOT NULL constraint to your<br>> geometry column and CLUSTER \
your table on the geometry index.<br><br>I'll check the nullity of geometries, I am \
not sure of this. <br>How do you cluster a table on an index (again, sorry but \
these<br>concepts are unkown in the mysql world...)<br><br><br>> You may also want \
to use partial indices in addition to the full one, e. G.<br><br>That sound very good \
to me ! <br>I usually filter data on both the road class AND the \
geometry<br>location, so it definitively makes sense to filter on both !<br>I'll try \
this ASAP !<br><br>Thanks again Markus !<br><br>Regards<br>--<br>Arnaud<br><br> \
<br><br>------------------------------<br><br>Message: 16<br>Date: Fri, 17 Feb 2006 \
13:36:36 -0300<br>From: Ezequias Rodrigues da Rocha <<a \
href="mailto:ezequias@recife.pe.gov.br">ezequias@recife.pe.gov.br</a>><br>Subject: \
[postgis-users] Point within Polygon <br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F5FB94.30103@recife.pe.gov.br">43F5FB94.30103@recife.pe.gov.br \
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi \
list,<br><br>There is any function on PostGIS that restrict the updates of some \
point<br>layer inside another layer (polygon)?<br><br><br>Sincerely... \
<br><br>--<br>Ezequias Rodrigues da Rocha<br><a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a><br><a \
href="mailto:msn:ezequias@hotmail.com">msn:ezequias@hotmail.com</a><br>"the \
worst of democracies is still better than the best of dictatorship" \
<br><br><br><br>------------------------------<br><br>Message: 17<br>Date: Fri, 17 \
Feb 2006 09:34:18 -0800<br>From: Paul Ramsey <<a \
href="mailto:pramsey@refractions.net">pramsey@refractions.net</a>><br>Subject: Re: \
[postgis-users] Point within Polygon <br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:E07C74DE-AAF8-4C50-AE11-6A6B41AA0C34@refractions.net"> \
E07C74DE-AAF8-4C50-AE11-6A6B41AA0C34@refractions.net</a>><br>Content-Type: \
text/plain; charset=US-ASCII; delsp=yes; format=flowed<br><br>You could write a \
contraint...<br>P<br><br>On Feb 17, 2006, at 8:36 AM, Ezequias Rodrigues da Rocha \
wrote: <br><br>> Hi list,<br>><br>> There is any function on PostGIS that \
restrict the updates of some<br>> point layer inside another layer \
(polygon)?<br>><br>><br>> Sincerely...<br>><br>> --<br>> Ezequias \
Rodrigues da Rocha <br>> <a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a><br>> \
<a href="mailto:msn:ezequias@hotmail.com">msn:ezequias@hotmail.com</a><br>> \
"the worst of democracies is still better than the best of <br>> \
dictatorship"<br>><br>> \
_______________________________________________<br>> postgis-users mailing \
list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br>------------------------------<br><br>Message: \
18<br>Date: Fri, 17 Feb 2006 18:41:31 +0100 <br>From: Markus Schaber <<a \
href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>><br>Subject: Re: \
[postgis-users] Partitioning spatial table<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a \
href="mailto:43F60ACB.4030905@logix-tt.com">43F60ACB.4030905@logix-tt.com</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1<br><br>Hi, Arnaud,<br><br> Arnaud Lesauvage \
schrieb:<br><br>>> partitioning, especially on road data, which usually is \
write once and<br>>> then read-only.<br>> Actually, it was just for the \
benefit of the constraint exclusion that I <br>> wanted to try \
this.<br><br>Constraint Exclusion itsself has its overhead, that's why there's \
a<br>configuration option to enable it.<br><br>>> Could you send us the output \
of "EXPLAIN ANALYZE <your query>;"? If <br>>> estimations and \
reality are very different, increasing the statistics<br>>> target will \
help.<br>><br>> Sure !<br>> "Index Scan using nw_nw_geometry_gist on \
nw (cost=0.00..6.02 rows=1<br> > width=141)"<br>> \
" Index Cond: ('<the box>'::geometry && \
nw_geometry)"<br>> " Filter: ((frc = 2) AND ('<the \
box>'::geometry && nw_geometry))"<br>><br>> Reality is ~5 \
minutes. <br><br>It looks like that is the output of "EXPLAIN \
<query>" instead of<br>"EXPLAIN ANALYZE <query>". The \
difference is that the latter one<br>actually performs the query with some profiling, \
and provides the real <br>times as well as the estimations.<br><br>> But I don't \
know what you mean by increasing the statistics (sorry for<br>> my lack of \
knowledge, I am new to postgresql, I am moving from mysql).<br><br>Please see the \
PostgreSQL docs on <br><a \
href="http://www.postgresql.org/docs/8.1/static/planner-stats.html">http://www.postgresql.org/docs/8.1/static/planner-stats.html</a><br><br>>> \
If you don't have NULL geometries, add a NOT NULL constraint to your <br>>> \
<br>type indices) have a problem with NULL values that prevent them from<br>being \
CLUSTERed on.<br><br>> How do you cluster a table on an index (again, sorry but \
these concepts<br>> are unkown in the mysql world...) <br><br>It's all explained \
here:<br><a href="http://www.postgresql.org/docs/8.1/static/sql-cluster.html">http://www.postgresql.org/docs/8.1/static/sql-cluster.html</a><br><br>Btw, \
generally, you should look into the PostgreSQL manuals, especially <br><a \
href="http://www.postgresql.org/docs/8.1/static/maintenance.html">http://www.postgresql.org/docs/8.1/static/maintenance.html</a> \
- it is<br>always a good adivise to read the manuals when changing to a \
new<br>product, there are subtle differences between PostgreSQL and MySQL. (The \
<br>same is true for DB2, Oracle, MS Sequel server etc., of course.) It will<br>need \
some weeks or month until you can "think in PostgreS way". :-)<br><br>Also, \
have a look at<br><a href="http://www.postgresql.org/docs/faqs.FAQ.html#item3.3"> \
http://www.postgresql.org/docs/faqs.FAQ.html#item3.3</a> - there are some<br>useful \
links.<br><br>You can also ask the <a \
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a> \
mailing list if <br>you have specific questions that are not answered in the \
docs.<br><br>>> You may also want to use partial indices in addition to the \
full one,<br>>> e. G.<br>> That sound very good to me !<br>> I usually \
filter data on both the road class AND the geometry location, <br>> so it \
definitively makes sense to filter on both !<br><br>Yes, and partial indices \
basically do the same than constraint<br>exclusion, but are more \
lightweight.<br><br>HTH,<br>Markus<br><br><br>------------------------------ \
<br><br>Message: 19<br>Date: Fri, 17 Feb 2006 14:44:54 -0300<br>From: Ezequias \
Rodrigues da Rocha <<a \
href="mailto:ezequias@recife.pe.gov.br">ezequias@recife.pe.gov.br</a>><br>Subject: \
Re: [postgis-users] Point within Polygon <br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F60B96.6060207@recife.pe.gov.br">43F60B96.6060207@recife.pe.gov.br
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Sure, \
but could someone tell me how it works ? I am relatively newbe \
in<br>PostgreSQL.<br><br>Ezequias<br>Paul Ramsey escreveu:<br>> You could write a \
contraint... <br>> P<br>><br>> On Feb 17, 2006, at 8:36 AM, Ezequias \
Rodrigues da Rocha wrote:<br>><br>>> Hi list,<br>>><br>>> There \
is any function on PostGIS that restrict the updates of some<br>>> point layer \
inside another layer (polygon)? <br>>><br>>><br>>> \
Sincerely...<br>>><br>>> --Ezequias Rodrigues da Rocha<br>>> <a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a><br>>> \
<a href="mailto:msn:ezequias@hotmail.com"> msn:ezequias@hotmail.com</a><br>>> \
"the worst of democracies is still better than the best of \
dictatorship"<br>>><br>>> \
_______________________________________________<br>>> postgis-users mailing \
list <br>>> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> \
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>><br>> _______________________________________________<br>> \
postgis-users mailing list<br>> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> \
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br>--<br>Ezequias \
Rodrigues da Rocha<br><a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a><br><a \
href="mailto:msn:ezequias@hotmail.com"> msn:ezequias@hotmail.com</a><br>"the \
worst of democracies is still better than the best of \
dictatorship"<br><br><br><br>------------------------------<br><br>Message: \
20<br>Date: Fri, 17 Feb 2006 09:52:00 -0800<br>
From: Paul Ramsey <<a \
href="mailto:pramsey@refractions.net">pramsey@refractions.net</a>><br>Subject: Re: \
[postgis-users] Point within Polygon<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a \
href="mailto:D679C041-D59D-4EE5-917F-266E2EF358C1@refractions.net">D679C041-D59D-4EE5-917F-266E2EF358C1@refractions.net</a>><br>Content-Type: \
text/plain; charset=US-ASCII; delsp=yes; format=flowed <br><br><a \
href="http://www.postgresql.org/docs/current/static/ddl-constraints.html">http://www.postgresql.org/docs/current/static/ddl-constraints.html</a><br><br>On \
Feb 17, 2006, at 9:44 AM, Ezequias Rodrigues da Rocha wrote: <br><br>> Sure, but \
could someone tell me how it works ? I am relatively<br>> newbe in \
PostgreSQL.<br>><br>> Ezequias<br>> Paul Ramsey escreveu:<br>>> You \
could write a contraint...<br>>> P<br>>> <br>>> On Feb 17, 2006, at \
8:36 AM, Ezequias Rodrigues da Rocha wrote:<br>>><br>>>> Hi \
list,<br>>>><br>>>> There is any function on PostGIS that restrict \
the updates of<br>>>> some point layer inside another layer (polygon)? \
<br>>>><br>>>><br>>>> \
Sincerely...<br>>>><br>>>> --Ezequias Rodrigues da \
Rocha<br>>>> <a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a> \
<br>>>> <a href="mailto:msn:ezequias@hotmail.com">msn:ezequias@hotmail.com</a><br>>>> \
"the worst of democracies is still better than the best of<br>>>> \
dictatorship"<br>>>><br> >>> \
_______________________________________________<br>>>> postgis-users mailing \
list<br>>>> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>>> \
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>>><br>>> \
_______________________________________________<br>>> postgis-users mailing \
list <br>>> <a \
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> \
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>>><br>><br>> --<br>> Ezequias Rodrigues da Rocha<br>> <a \
href="http://ezequiasrocha.blogspot.com">http://ezequiasrocha.blogspot.com</a><br>> \
<a href="mailto:msn:ezequias@hotmail.com">msn:ezequias@hotmail.com </a><br>> \
"the worst of democracies is still better than the best of<br>> \
dictatorship"<br>><br>> \
_______________________________________________<br>> postgis-users mailing \
list<br>> <a href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a><br>> <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.re \
fractions.net/mailman/listinfo/postgis-users</a><br><br><br><br>------------------------------
<br><br>Message: 21<br>Date: Fri, 17 Feb 2006 19:11:49 +0100<br>From: Arnaud \
Lesauvage <<a href="mailto:thewild@freesurf.fr">thewild@freesurf.fr</a>><br>Subject: \
Re: [postgis-users] Partitioning spatial table<br>To: PostGIS Users Discussion < \
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: \
<<a href="mailto:43F611E5.8040408@freesurf.fr">43F611E5.8040408@freesurf.fr</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1; format=flowed <br><br>Markus Schaber a écrit \
:<br>> It looks like that is the output of "EXPLAIN <query>" \
instead of<br>> "EXPLAIN ANALYZE <query>". The difference is that \
the latter one<br>> actually performs the query with some profiling, and provides \
the real <br>> times as well as the estimations.<br><br>Yes, your are absolutely \
right ! Sorry for that. I am not at work<br>right now, but I'll recheck this query on \
monday !<br><br>>> How do you cluster a table on an index (again, sorry but \
these concepts <br>>> are unkown in the mysql world...)<br>><br>> It's \
all explained here:<br>> <a \
href="http://www.postgresql.org/docs/8.1/static/sql-cluster.html">http://www.postgresql.org/docs/8.1/static/sql-cluster.html
</a><br>><br>> Btw, generally, you should look into the PostgreSQL manuals, \
especially<br>> <a \
href="http://www.postgresql.org/docs/8.1/static/maintenance.html">http://www.postgresql.org/docs/8.1/static/maintenance.html
</a> - it is<br>> always a good adivise to read the manuals when changing to a \
new<br>> product, there are subtle differences between PostgreSQL and MySQL. \
(The<br>> same is true for DB2, Oracle, MS Sequel server etc., of course.) It will \
<br>> need some weeks or month until you can "think in PostgreS way". \
:-)<br><br>Well, I have read the doc (a large part of it at least), but I<br>believe \
I won't be able to "think in PostgreS way" until I've read <br>it 2 or 3 \
more times. ;-)<br><br>>>> You may also want to use partial indices in \
addition to the full one,<br>>>> e. G.<br>>> That sound very good to \
me !<br>>> I usually filter data on both the road class AND the geometry \
location, <br>>> so it definitively makes sense to filter on both \
!<br>><br>> Yes, and partial indices basically do the same than \
constraint<br>> exclusion, but are more lightweight.<br><br>My server is reloading \
the data during this week-end (it needs <br>some hours to do that).<br>I think I will \
first try to cluster on the Gist index.<br>If I create a multicolumn index (with the \
geometry column first),<br>would it be a better idea to cluster the table on this \
index instead ? <br><br><br><br>Many thanks again ! I'll have another look at the \
docs this<br>week-end \
;-)<br><br>Regards<br>--<br>Arnaud<br><br><br>------------------------------<br><br>Message: \
22<br>Date: Fri, 17 Feb 2006 14:26:29 -0500 <br>From: Bill Binko <<a \
href="mailto:bill@binko.net">bill@binko.net</a>><br>Subject: Re: [postgis-users] \
Partitioning spatial table<br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@postgis.refractions.net"> \
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a \
href="mailto:43F62365.8050308@binko.net">43F62365.8050308@binko.net</a>><br>Content-Type: \
text/plain; charset=ISO-8859-1; format=flowed<br><br>Arnaud Lesauvage wrote: \
<br><br>> ....<br><br>><br>><br>> Do you have any better idea on how to \
implement this ?<br>><br>I think I do! I know there is already another \
response, and his advice<br>is good... but, I have found that the partitioning \
solution you <br>mentioned really solves other problems such as data storage, \
archival<br>support, etc, and not necessarily query performance.<br><br>There are \
many reasons why your query might be slow, but a few simple<br>things I'd do are: \
<br>1) Make sure it's using the GiST index (as mentioned post a EXPLAIN<br>ANALYZE \
and we'll help). There are many reasons including some simple<br>ones like \
the sort_mem and shared_buffers parameters in postgresql.conf<br> (which by default \
are just plain wrong)<br>2) Create functional indexes on any spatial functions you \
run (such as<br>centroid() or area()) and expect to join to<br>3) Create Conditional \
indexes on the fields you're considering <br>partitioning on. This is a \
Biggie, so let me explain:<br><br>Lets say you have a field like admin_area_id that \
you were going to<br>partition on. You usually add a clause like 'WHERE \
admin_area_id = 4"<br>which will limit the rest of the query to 1/100 of the \
data. <br><br>Rather than partitioning the table, just create \
partitioned<br>(conditional) indexes:<br><br>First create an index on the field \
you're going to split on<br>CREATE INDEX theTable_admin_id on theTable \
(admin_id);<br> <br>Cluster it so that the rows are sorted on-disk by \
admin_id:<br>CLUSTER theTable on theTable_admin_id;<br><br>And create one GiST Index \
for each value:<br>CREATE INDEX theTable_admin_gist_1 on theTable USING GIST \
(theShape <br>GIST_GEOMETRY_OPS) WHERE admin_area_id=1;<br>CREATE INDEX \
theTable_admin_gist_2 on theTable USING GIST (theShape<br>GIST_GEOMETRY_OPS) WHERE \
admin_area_id=2;<br>...<br>CREATE INDEX theTable_admin_gist_100 on theTable USING \
GIST (theShape <br>GIST_GEOMETRY_OPS) WHERE admin_area_id=100;<br><br>Now, whenever \
you apply the 'WHERE admin_area_id = 4' clause, the<br>'theTable_admin_gist_4' index \
will be used. This index will have a<br>spatial index that only includes \
the shapes where the admin_area_id = <br>4. It will find the entries very \
quickly...because it only searches 1%<br>of the shapes. It will load them \
quickly due to the CLUSTER command.<br><br>One of the nicest things about this is \
that there is monthly<br>administration of this or other cruft necessary with \
partitioning: just <br>run VACUUM FULL ANALYZE regularly, and you're \
done.<br><br>Hope this helps.<br><br>Bill<br><br><br><br>this will keep the items \
close together on disk, so that you get most of<br>the benefits of \
the<br><br><br><br>------------------------------ \
<br><br>_______________________________________________<br>postgis-users mailing \
list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> \
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br>End of \
postgis-users Digest, Vol 40, Issue \
16<br>*********************************************<br></blockquote></div><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic