[postgis-users] unsuscribe
sebastian barahona
sebastian.barahona at gmail.com
Fri Feb 17 14:58:31 PST 2006
unsuscribe
2006/2/17, postgis-users-request at postgis.refractions.net <
postgis-users-request at postgis.refractions.net>:
>
> Send postgis-users mailing list submissions to
> postgis-users at postgis.refractions.net
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request at postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner at postgis.refractions.net
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. 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 at gmail.com>
> Subject: [postgis-users] intersection direction
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID:
> <71c3c6c50602161251u60420c6ev19be384e58a4a7f at 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 at swoodbridge.com>
> Subject: Re: [postgis-users] intersection direction
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F4F18B.4060307 at 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 at 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 at gmail.com>
> Subject: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to 8.1.3 win32
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <842c1e660602161432u418a6efco at 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 at uchile.cl
> rviancos at 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 at lrcwe.com>
> Subject: RE: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to8.1.3 win32
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001f01c63349$64842990$2500a8c0 at 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 at postgis.refractions.net
> [mailto:postgis-users-bounces at 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 at uchile.cl
> rviancos at 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 at gmail.com>
> Subject: Re: [postgis-users] Problem with intersection from postgresql
> 8.0.7 to8.1.3 win32
> To: rindahl at lrcwe.com, PostGIS Users Discussion
> <postgis-users at postgis.refractions.net>
> Message-ID: <842c1e660602161439i5021021bi at 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 at lrcwe.com>:
> >
> > Has the srid been set for both geometries and are they the same?
> >
> >
> >
> > -----Original Message-----
> > *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> > postgis-users-bounces at 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 at uchile.cl
> > rviancos at gmail.com
> > www.investigacion.uchile.cl
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at 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 at uchile.cl
> rviancos at 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 at enst-bretagne.fr>
> Subject: [postgis-users] postgis javadoc
> To: postgis-users at postgis.refractions.net
> Message-ID: <43F51070.9030002 at 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 at logix-tt.com>
> Subject: Re: [postgis-users] Problems with restore dump datafile
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F51722.4070001 at 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 at gmail.com>
> Subject: [postgis-users] Query Problem pg 8.0.3 and 8.1.7
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Cc: jaime villanueva <jaimehvillanueva at gmail.com>, Orion Aramayo
> <orion.aramayo at gmail.com>, Ori?n Aramayo B. <
> orion.aramayo at unarte.cl>,
> Alejandro Silva <alejandro.silva.a at gmail.com>, Sebastian Barahona
> <seba.barahona at gmail.com>
> Message-ID: <842c1e660602161719j7fb3ddafv at 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 at uchile.cl
> rviancos at 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 at 012.net.il>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F53A9E.9070209 at 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 at 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 at atenista.net>
> Subject: [postgis-users] Intersection and Geometrytype
> To: postgis-users at postgis.refractions.net
> Message-ID: <20060216214148.23A9E0F3 at 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 at logix-tt.com>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5AD30.9020003 at 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 at logix-tt.com>
> Subject: Re: [postgis-users] postgis javadoc
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5B4D1.8080201 at 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 at freesurf.fr>
> Subject: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5D83E.1090604 at 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 at logix-tt.com>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5DBBC.7030603 at 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 at freesurf.fr>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5E1C2.7060805 at 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 at recife.pe.gov.br>
> Subject: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F5FB94.30103 at 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 at 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 at refractions.net>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <E07C74DE-AAF8-4C50-AE11-6A6B41AA0C34 at 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 at hotmail.com
> > "the worst of democracies is still better than the best of
> > dictatorship"
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at 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 at logix-tt.com>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F60ACB.4030905 at 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 at 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 at recife.pe.gov.br>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F60B96.6060207 at 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 at hotmail.com
> >> "the worst of democracies is still better than the best of
> dictatorship"
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Ezequias Rodrigues da Rocha
> http://ezequiasrocha.blogspot.com
> msn:ezequias at 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 at refractions.net>
> Subject: Re: [postgis-users] Point within Polygon
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <D679C041-D59D-4EE5-917F-266E2EF358C1 at 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 at hotmail.com
> >>> "the worst of democracies is still better than the best of
> >>> dictatorship"
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> > --
> > Ezequias Rodrigues da Rocha
> > http://ezequiasrocha.blogspot.com
> > msn:ezequias at hotmail.com
> > "the worst of democracies is still better than the best of
> > dictatorship"
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at 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 at freesurf.fr>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F611E5.8040408 at 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 at binko.net>
> Subject: Re: [postgis-users] Partitioning spatial table
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43F62365.8050308 at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 40, Issue 16
> *********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060217/76446a05/attachment.html>
More information about the postgis-users
mailing list