[postgis-users] Difficult Problem with Polygons
Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. 2. Then I have a line that cuts through a triangle's face and a point Q, away from this line and triangle. I would like to know amongst the neighbours of the triangle (calculated in 1), which ones fall *below* the line and closer to the point Q (where Q is the generator of a Voronoi cell). And also the set of triangles that are *above* the line and further from Q. Any suggestions as to how I can go about achieving this in postgis 2.0? Cheers, Ed ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi, 1. Does st_touches not work for that ? 2. What do you mean by below ? south from the line ? St_distance should help you to find the closer one from Q. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date: lun. 29/10/2012 09:33 À: PostGIS Users Discussion Objet : [postgis-users] Difficult Problem with Polygons Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. 2. Then I have a line that cuts through a triangle's face and a point Q, away from this line and triangle. I would like to know amongst the neighbours of the triangle (calculated in 1), which ones fall *below* the line and closer to the point Q (where Q is the generator of a Voronoi cell). And also the set of triangles that are *above* the line and further from Q. Any suggestions as to how I can go about achieving this in postgis 2.0? Cheers, Ed winmail.dat___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
On 29 October 2012 21:33, Ed Linde edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ 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
Re: [postgis-users] Difficult Problem with Polygons
Hi All, Wondering if that diagram made things any simpler or is it still not clear what the problem is? Thanks, Ed On Mon, Oct 29, 2012 at 11:57 AM, Ed Linde edoli...@gmail.com wrote: Attached is a figure. Where the dotted line is the boundary of the voronoi cell whose generator is point P1. So triangle 4 intersects with the voronoi boundary, but we are interested in the adjacent triangles of triangle 4, which are closer to point P1. For example, triangle 5. Hope this helps. Cheers, Ed On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot nicolas.ri...@gmail.comwrote: Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde edoli...@gmail.com wrote: Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ 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 ___ 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
[postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
Did you specify the path to pg_config ?? eg: ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config *Jeff Lake* MichiganWxSystem http://www.michiganwxsystem.com AllisonHouse http://www.allisonhouse.com GRLevelXStuff http://www.grlevelxstuff.com On 10/29/2012 9:49, Ilie, Radu wrote: Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ 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
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
Yes, I did. The error is this: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/usr/include/libxml2 -I../liblwgeom -I. -I. -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/server -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o lwgeom_estimate.c lwgeom_estimate.c: In function 'LWGEOM_gist_joinsel': lwgeom_estimate.c:318: error: 'Form_pg_class' undeclared (first use in this function) lwgeom_estimate.c:318: error: (Each undeclared identifier is reported only once lwgeom_estimate.c:318: error: for each function it appears in.) lwgeom_estimate.c:318: error: expected ';' before 'reltup' lwgeom_estimate.c:319: error: 'reltup' undeclared (first use in this function) lwgeom_estimate.c:330: error: expected ';' before 'reltup' make[1]: *** [lwgeom_estimate.o] Error 1 Radu From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Jeff Lake Sent: Monday, October 29, 2012 9:59 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 Did you specify the path to pg_config ?? eg: ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config Jeff Lake MichiganWxSystemhttp://www.michiganwxsystem.com AllisonHousehttp://www.allisonhouse.com GRLevelXStuffhttp://www.grlevelxstuff.com On 10/29/2012 9:49, Ilie, Radu wrote: Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ postgis-users mailing list postgis-users@postgis.refractions.netmailto: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
Re: [postgis-users] Difficult Problem with Polygons
Yes indeed it's clearer. You could compute the distance between each adjacent triangle and the voronoi generator and order by shortest distance. (taking the triangle's centroid for the distance) Also, is the voronoi boundary forming a polygon or just a line ? in the former, you could find all the triangles inside the voronoi cell. In the latter, you will have to determine a signed area or a cross product to know if points lie in the same side of the boundary. Nicolas On 29 October 2012 13:45, Ed Linde edoli...@gmail.com wrote: Hi All, Wondering if that diagram made things any simpler or is it still not clear what the problem is? Thanks, Ed On Mon, Oct 29, 2012 at 11:57 AM, Ed Linde edoli...@gmail.com wrote: Attached is a figure. Where the dotted line is the boundary of the voronoi cell whose generator is point P1. So triangle 4 intersects with the voronoi boundary, but we are interested in the adjacent triangles of triangle 4, which are closer to point P1. For example, triangle 5. Hope this helps. Cheers, Ed On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde edoli...@gmail.com wrote: Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ 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 ___ 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi Ed, Well if clarifies one thing at least, you can ignore Mike's st_Relate because 5 is not adjacent to a linear edge, it is only adjacent to a vertex, so st_touches should work fine. so you are looking for: 1. a specific triangle by id 2. that intersects triangle VC 3. and the triangles adjacent to triangle by id 4. and those that are contained in VC 5. sorted by distance to P1 limit 1 so something like: select id, the_geom from (select id, the_geom as adjacent from triangles where st_touches(the_geom, (select the_geom from triangles a, (select the_geom as vc from vc_table where id='p1') b where a.id=4)) c order by st_distance(c.the_geom, (select the_geom from points where id='p1')) asc limit 1; Untested, but should give you a model to work with. -Steve W On 10/29/2012 6:57 AM, Ed Linde wrote: Attached is a figure. Where the dotted line is the boundary of the voronoi cell whose generator is point P1. So triangle 4 intersects with the voronoi boundary, but we are interested in the adjacent triangles of triangle 4, which are closer to point P1. For example, triangle 5. Hope this helps. Cheers, Ed On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot nicolas.ri...@gmail.com mailto:nicolas.ri...@gmail.com wrote: Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com mailto:mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id http://e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net
Re: [postgis-users] Difficult Problem with Polygons
Hi All, I was trying to do this intersection between two tables, I have inserted the selects for the two tables and the error. Also the postgis full version information. Is there a way to get around this problem? Is this a bug? The polygons in both tables contain 3D points. Could this be a problem? Cheers, Ed select a.id, ST_AsText(st_intersection(a.geomtext, b.geomtext)) the_tris from small_tris a, vfaces b where st_intersects (a.geomtext, b.geomtext); ERROR: Error performing intersection: TopologyException: found non-noded intersection between LINESTRING (593907 5.2196e+06, 593915 5.21961e+06) and LINESTRING (593911 5.21961e+06, 593908 5.2196e+06) at 593908.598 5219602 809.899918 ** Error ** ERROR: Error performing intersection: TopologyException: found non-noded intersection between LINESTRING (593907 5.2196e+06, 593915 5.21961e+06) and LINESTRING (593911 5.21961e+06, 593908 5.2196e+06) at 593908.598 5219602 809.899918 SQL state: XX000 SELECT PostGIS_full_version(); postgis_full_version --- POSTGIS=2.0.0alpha7SVN GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.7.1, 23 September 2009 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.7.8 USE_STATS (1 row) bounds=# select gen_id, ST_AsText(geomtext) from vfaces; gen_id | st_astext +- 7 | POLYGON Z ((593908 5219600 803,593907 5219600 818,593915 5219610 818,593911 5219610 803,593908 5219600 803)) 7 | POLYGON Z ((593908 5219600 803,593899 5219600 803,593899 5219600 818,593907 5219600 818,593908 5219600 803)) 7 | POLYGON Z ((593908 5219600 803,593911 5219610 803,593903 5219620 803,593899 5219620 803,593899 5219600 803,593908 5219600 803)) 7 | POLYGON Z ((593899 5219620 803,593899 5219620 818,593899 5219600 818,593899 5219600 803,593899 5219620 803)) 7 | POLYGON Z ((593899 5219620 803,593903 5219620 803,593912 5219620 818,593899 5219620 818,593899 5219620 803)) 7 | POLYGON Z ((593912 5219620 818,593903 5219620 803,593911 5219610 803,593915 5219610 818,593912 5219620 818)) 7 | POLYGON Z ((593912 5219620 818,593915 5219610 818,593907 5219600 818,593899 5219600 818,593899 5219620 818,593912 5219620 818)) 16 | POLYGON Z ((593921 5219600 803,593921 5219610 803,593911 5219610 803,593908 5219600 803,593921 5219600 803)) 16 | POLYGON Z ((593921 5219600 803,593921 5219600 818,593921 5219610 818,593921 5219610 803,593921 5219600 803)) 16 | POLYGON Z ((593921 5219600 803,593908 5219600 803,593907 5219600 818,593921 5219600 818,593921 5219600 803)) 16 | POLYGON Z ((593911 5219610 803,593921 5219610 803,593921 5219610 818,593915 5219610 818,593911 5219610 803)) 16 | POLYGON Z ((593911 5219610 803,593915 5219610 818,593907 5219600 818,593908 5219600 803,593911 5219610 803)) 16 | POLYGON Z ((593907 5219600 818,593915 5219610 818,593921 5219610 818,593921 5219600 818,593907 5219600 818)) 18 | POLYGON Z ((593921 5219610 803,593911 5219610 803,593915 5219610 818,593921 5219610 818,593921 5219610 803)) 18 | POLYGON Z ((593921 5219610 803,593921 5219620 803,593903 5219620 803,593911 5219610 803,593921 5219610 803)) 18 | POLYGON Z ((593921 5219610 803,593921 5219610 818,593921 5219620 818,593921 5219620 803,593921 5219610 803)) 18 | POLYGON Z ((593912 5219620 818,593915 5219610 818,593911 5219610 803,593903 5219620 803,593912 5219620 818)) 18 | POLYGON Z ((593912 5219620 818,593921 5219620 818,593921 5219610 818,593915 5219610 818,593912 5219620 818)) 18 | POLYGON Z ((593912 5219620 818,593903 5219620 803,593921 5219620 803,593921 5219620 818,593912 5219620 818)) (19 rows) bounds=# select id, st_astext(geomtext) from small_tris; id | st_astext +--- 0 | POLYGON Z ((593890 5219590 840,593900 5219590 827,593890 5219600 817,593890 5219590 840)) 1 | POLYGON Z ((593890 5219600 817,593900 5219590 827,593900 5219600 815,593890 5219600 817)) 2 | POLYGON Z ((593900 5219600 815,593910 5219590 824,593910 5219600 815,593900 5219600 815)) 3 | POLYGON Z ((593910 5219590 824,593900 5219600 815,593900 5219590 827,593910 5219590 824)) 4 | POLYGON Z ((593910 5219600 815,593910 5219590 824,593920 5219590 842,593910 5219600 815)) 5 | POLYGON Z ((593900 5219600 815,593910 5219600 815,593900 5219610 815,593900 5219600 815)) 6 | POLYGON Z ((593890 5219600 817,593900 5219600 815,593900 5219610 815,593890 5219600 817)) 7 | POLYGON Z ((593890 5219620 812,593890 5219610 825,593900 5219610 815,593890 5219620 812)) 8 | POLYGON Z ((593890 5219630 827,593890 5219620 812,593900 5219620 841,593890 5219630 827)) 9 | POLYGON Z ((593900 5219610 815,593900 5219620 841,593890
Re: [postgis-users] Difficult Problem with Polygons
If the points are computed in the right order, you can store them and pass them to st_makeLine and st_makePolygon. If not, you can form a segment between 2 closest points and connect it to the closest points. On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote: Hi All, Thanks for the tips! Just another thing, when I compute the transition points on each edge (shown as red points in my pdf). I need to join them to make a polygon. Wondering how I can connect them together so that I start with a point and end on it to form a closed polygon? Cheers, Ed On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge wood...@swoodbridge.com wrote: Hi Ed, Well if clarifies one thing at least, you can ignore Mike's st_Relate because 5 is not adjacent to a linear edge, it is only adjacent to a vertex, so st_touches should work fine. so you are looking for: 1. a specific triangle by id 2. that intersects triangle VC 3. and the triangles adjacent to triangle by id 4. and those that are contained in VC 5. sorted by distance to P1 limit 1 so something like: select id, the_geom from (select id, the_geom as adjacent from triangles where st_touches(the_geom, (select the_geom from triangles a, (select the_geom as vc from vc_table where id='p1') b where a.id=4)) c order by st_distance(c.the_geom, (select the_geom from points where id='p1')) asc limit 1; Untested, but should give you a model to work with. -Steve W On 10/29/2012 6:57 AM, Ed Linde wrote: Attached is a figure. Where the dotted line is the boundary of the voronoi cell whose generator is point P1. So triangle 4 intersects with the voronoi boundary, but we are interested in the adjacent triangles of triangle 4, which are closer to point P1. For example, triangle 5. Hope this helps. Cheers, Ed On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot nicolas.ri...@gmail.com mailto:nicolas.ri...@gmail.com wrote: Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com mailto:mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table elements, and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id http://e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___ postgis-users mailing list
Re: [postgis-users] Difficult Problem with Polygons
Thanks Nicolas. Just about the error, is this because the line segments are too close and postgis 2.0 could not handle this? If so is there a workaround, even if it means slightly having to perturb each point's position to not run into this bug. I was really hoping that the intersection of two polygonal shapes in 3D would be fairly simple in postgis 2.0 :( Ed On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote: If the points are computed in the right order, you can store them and pass them to st_makeLine and st_makePolygon. If not, you can form a segment between 2 closest points and connect it to the closest points. On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote: Hi All, Thanks for the tips! Just another thing, when I compute the transition points on each edge (shown as red points in my pdf). I need to join them to make a polygon. Wondering how I can connect them together so that I start with a point and end on it to form a closed polygon? Cheers, Ed On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge wood...@swoodbridge.com wrote: Hi Ed, Well if clarifies one thing at least, you can ignore Mike's st_Relate because 5 is not adjacent to a linear edge, it is only adjacent to a vertex, so st_touches should work fine. so you are looking for: 1. a specific triangle by id 2. that intersects triangle VC 3. and the triangles adjacent to triangle by id 4. and those that are contained in VC 5. sorted by distance to P1 limit 1 so something like: select id, the_geom from (select id, the_geom as adjacent from triangles where st_touches(the_geom, (select the_geom from triangles a, (select the_geom as vc from vc_table where id='p1') b where a.id=4)) c order by st_distance(c.the_geom, (select the_geom from points where id='p1')) asc limit 1; Untested, but should give you a model to work with. -Steve W On 10/29/2012 6:57 AM, Ed Linde wrote: Attached is a figure. Where the dotted line is the boundary of the voronoi cell whose generator is point P1. So triangle 4 intersects with the voronoi boundary, but we are interested in the adjacent triangles of triangle 4, which are closer to point P1. For example, triangle 5. Hope this helps. Cheers, Ed On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot nicolas.ri...@gmail.com mailto:nicolas.ri...@gmail.com wrote: Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by shrinking the voronoi cell (closer to Q) and another property when expanding the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mwto...@gmail.com mailto:mwto...@gmail.com wrote: On 29 October 2012 21:33, Ed Linde edoli...@gmail.com mailto:edoli...@gmail.com wrote: Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements
Re: [postgis-users] Difficult Problem with Polygons
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line segments are
too close
and postgis 2.0 could not handle this? If so is there a workaround, even if
it means
slightly having to perturb each point's position to not run into this bug.
I was really hoping that the intersection of two polygonal shapes in 3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can connect them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of the
voronoi cell whose
generator is point P1. So triangle 4 intersects with the voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which are closer
to
point P1.
For example, triangle 5.
Hope this helps.
Cheers,
Ed
On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot
nicolas.ri...@gmail.com
mailto:nicolas.ri...@gmail.com wrote:
Could you draw a figure ?
Nicolas
On 29 October 2012 11:03, Ed Linde edoli...@gmail.com
mailto:edoli...@gmail.com wrote:
Hi All,
Thanks for the suggestions.
For 1) I will look into how ST_touches works and see if it can
pick up all
the adjacent polygons to
the one I have. And also look into Mike's suggestion on
ST_relate...though I
must admit it looks
more complex.
For 2) I will try to clarify it a bit more... its harder to do
without a
figure :) but here goes.
Lets say we have a point Q which is the generator of a voronoi
cell. Now I
compute the
intersection between the voronoi cell boundaries and my
triangulation (Set
of polygons)
using ST_intersect. Once I have these triangles.. I say pick
one
triangle T
that is
intersecting the voronoi cell boundary of Q.
For all the triangles adjacent to T, I need to know which
triangles are
INSIDE the voronoi
boundary (closer to Q) and which adjacent triangles are just
OUTSIDE the
voronoi
boundary (farther from Q). I am basically testing for a certain
property by
shrinking the
voronoi cell (closer to Q) and another property when
expanding
the voronoi
cell (away from Q).
Just need to make this division of triangles. Haven't thought
of
a nice way
to do this in postgis 2.0
So any suggestions would greatly help.
Thanks,
Ed
On Mon, Oct 29, 2012 at 10:15
Re: [postgis-users] Difficult Problem with Polygons
Thanks Nicolas! Will look at the script that generated the polygon text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line segments
are
too close
and postgis 2.0 could not handle this? If so is there a workaround, even
if
it means
slightly having to perturb each point's position to not run into this
bug.
I was really hoping that the intersection of two polygonal shapes in 3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of the
voronoi cell whose
generator is point P1. So triangle 4 intersects with the voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which are closer
to
point P1.
For example, triangle 5.
Hope this helps.
Cheers,
Ed
On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot
nicolas.ri...@gmail.com
mailto:nicolas.ri...@gmail.com wrote:
Could you draw a figure ?
Nicolas
On 29 October 2012 11:03, Ed Linde edoli...@gmail.com
mailto:edoli...@gmail.com wrote:
Hi All,
Thanks for the suggestions.
For 1) I will look into how ST_touches works and see if it
can
pick up all
the adjacent polygons to
the one I have. And also look into Mike's suggestion on
ST_relate...though I
must admit it looks
more complex.
For 2) I will try to clarify it a bit more... its harder to
do
without a
figure :) but here goes.
Lets say we have a point Q which is the generator of a
voronoi
cell. Now I
compute the
intersection between the voronoi cell boundaries and my
triangulation (Set
of polygons)
using ST_intersect. Once I have these triangles.. I say pick
one
triangle T
that is
intersecting the voronoi cell boundary of Q.
For all the triangles adjacent to T, I need to know which
triangles are
INSIDE the voronoi
boundary (closer to Q) and which adjacent triangles are just
OUTSIDE the
voronoi
boundary (farther from Q). I am basically testing for a
certain
property by
shrinking the
voronoi cell
Re: [postgis-users] Difficult Problem with Polygons
Yes, it looks like some points were not put in the right order before
building a polygon, thus these butterfly polygons you generated.
If the formed polygon are expected to be convex, you could use
st_convexhull on the point cloud to generate the polygons.
On 29 October 2012 16:09, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas! Will look at the script that generated the polygon text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line segments
are
too close
and postgis 2.0 could not handle this? If so is there a workaround, even
if
it means
slightly having to perturb each point's position to not run into this
bug.
I was really hoping that the intersection of two polygonal shapes in 3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's
st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of the
voronoi cell whose
generator is point P1. So triangle 4 intersects with the voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which are
closer
to
point P1.
For example, triangle 5.
Hope this helps.
Cheers,
Ed
On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot
nicolas.ri...@gmail.com
mailto:nicolas.ri...@gmail.com wrote:
Could you draw a figure ?
Nicolas
On 29 October 2012 11:03, Ed Linde edoli...@gmail.com
mailto:edoli...@gmail.com wrote:
Hi All,
Thanks for the suggestions.
For 1) I will look into how ST_touches works and see if it
can
pick up all
the adjacent polygons to
the one I have. And also look into Mike's suggestion on
ST_relate...though I
must admit it looks
more complex.
For 2) I will try to clarify it a bit more... its harder to
do
without a
figure :) but here goes.
Lets say we have a point Q which is the generator of a
voronoi
cell. Now I
compute the
intersection between the voronoi cell boundaries and my
triangulation (Set
of polygons)
using ST_intersect. Once I have these triangles.. I say pick
one
triangle T
that is
intersecting the voronoi cell boundary of Q.
For all the
Re: [postgis-users] Difficult Problem with Polygons
Ok thanks, will look into that function. Because I wonder if the s/w I am
using is
actually outputting the vertices of each face in a cyclical fashion or just
arbitrarily.
Cheers,
Ed
On Mon, Oct 29, 2012 at 4:23 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote:
Yes, it looks like some points were not put in the right order before
building a polygon, thus these butterfly polygons you generated.
If the formed polygon are expected to be convex, you could use
st_convexhull on the point cloud to generate the polygons.
On 29 October 2012 16:09, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas! Will look at the script that generated the polygon text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line
segments
are
too close
and postgis 2.0 could not handle this? If so is there a workaround,
even
if
it means
slightly having to perturb each point's position to not run into
this
bug.
I was really hoping that the intersection of two polygonal shapes in
3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect
it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's
st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent
to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of
the
voronoi cell whose
generator is point P1. So triangle 4 intersects with the
voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which are
closer
to
point P1.
For example, triangle 5.
Hope this helps.
Cheers,
Ed
On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot
nicolas.ri...@gmail.com
mailto:nicolas.ri...@gmail.com wrote:
Could you draw a figure ?
Nicolas
On 29 October 2012 11:03, Ed Linde edoli...@gmail.com
mailto:edoli...@gmail.com wrote:
Hi All,
Thanks for the suggestions.
For 1) I will look into how ST_touches works and see if it
can
pick up all
the adjacent polygons to
the one I have. And also look into Mike's suggestion on
ST_relate...though I
must admit it looks
more complex.
For 2) I will try to clarify it a bit more... its harder
to
do
without a
figure :) but here
Re: [postgis-users] Difficult Problem with Polygons
Hi Nicolas,
It seems like sometimes the points are one and the same and I get
linestrings and not
actual polygons. I pass these points in from a perl script, so is there a
way I can just
give the raw x,y,z coordinates of these points and do a st_convexhull
function on them?
Could you please give me a small example?
I tried something like:
The two coordinates marked in red and marked in orange are the same.
bounds=# insert into vfaces values (1,
bounds(# ST_GeomFromText('POLYGON((593901 5219610 814,593901 5219610 814,593899
5219610 814,593899 5219610 814,593901 5219610 814))') );
INSERT 0 1
bounds=# select st_isvalid(geomtext) from vfaces;
NOTICE: Too few points in geometry component at or near point 593901
5219610 814
st_isvalid
f
(1 row)
Thanks,
Ed
On Mon, Oct 29, 2012 at 4:26 PM, Ed Linde edoli...@gmail.com wrote:
Ok thanks, will look into that function. Because I wonder if the s/w I am
using is
actually outputting the vertices of each face in a cyclical fashion or
just
arbitrarily.
Cheers,
Ed
On Mon, Oct 29, 2012 at 4:23 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote:
Yes, it looks like some points were not put in the right order before
building a polygon, thus these butterfly polygons you generated.
If the formed polygon are expected to be convex, you could use
st_convexhull on the point cloud to generate the polygons.
On 29 October 2012 16:09, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas! Will look at the script that generated the polygon
text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line
segments
are
too close
and postgis 2.0 could not handle this? If so is there a workaround,
even
if
it means
slightly having to perturb each point's position to not run into
this
bug.
I was really hoping that the intersection of two polygonal shapes in
3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them
and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect
it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's
st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent
to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of
the
voronoi cell whose
generator is point P1. So triangle 4 intersects with the
voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which
Re: [postgis-users] Difficult Problem with Polygons
You should filter out the consecutive points. If there are more than 2
points, you can then call st_convexHull() on the point set:
with points as (
select 'POINT (0 0)'::geometry as geom
UNION
select 'POINT (1 0)'::geometry as geom
UNION
select 'POINT (1 1)'::geometry as geom
) select st_convexHull(st_collect(geom))
from points;
If the points are aligned, convexHull will be a linestring, that you
can discard or further process according to your needs.
Nicolas
On 29 October 2012 16:51, Ed Linde edoli...@gmail.com wrote:
Hi Nicolas,
It seems like sometimes the points are one and the same and I get
linestrings and not
actual polygons. I pass these points in from a perl script, so is there a
way I can just
give the raw x,y,z coordinates of these points and do a st_convexhull
function on them?
Could you please give me a small example?
I tried something like:
The two coordinates marked in red and marked in orange are the same.
bounds=# insert into vfaces values (1,
bounds(# ST_GeomFromText('POLYGON((593901 5219610 814,593901 5219610
814,593899 5219610 814,593899 5219610 814,593901 5219610 814))') );
INSERT 0 1
bounds=# select st_isvalid(geomtext) from vfaces;
NOTICE: Too few points in geometry component at or near point 593901
5219610 814
st_isvalid
f
(1 row)
Thanks,
Ed
On Mon, Oct 29, 2012 at 4:26 PM, Ed Linde edoli...@gmail.com wrote:
Ok thanks, will look into that function. Because I wonder if the s/w I am
using is
actually outputting the vertices of each face in a cyclical fashion or
just
arbitrarily.
Cheers,
Ed
On Mon, Oct 29, 2012 at 4:23 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
Yes, it looks like some points were not put in the right order before
building a polygon, thus these butterfly polygons you generated.
If the formed polygon are expected to be convex, you could use
st_convexhull on the point cloud to generate the polygons.
On 29 October 2012 16:09, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas! Will look at the script that generated the polygon
text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point 593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line
segments
are
too close
and postgis 2.0 could not handle this? If so is there a workaround,
even
if
it means
slightly having to perturb each point's position to not run into
this
bug.
I was really hoping that the intersection of two polygonal shapes in
3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them
and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and connect
it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can
connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's
st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent
to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1')
Re: [postgis-users] Difficult Problem with Polygons
I tried something like the following without removing the same points ad it
seems to work:
SELECT st_astext(ST_ConvexHull(ST_GeomFromText('MULTIPOINT(593901 5219610
814,593901 5219610 814,593899 5219610 814,593899 5219610 814,593901 5219610
814)')));
LINESTRING Z (593901 5219610 814,593899 5219610 814)
Ed
On Mon, Oct 29, 2012 at 5:23 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote:
You should filter out the consecutive points. If there are more than 2
points, you can then call st_convexHull() on the point set:
with points as (
select 'POINT (0 0)'::geometry as geom
UNION
select 'POINT (1 0)'::geometry as geom
UNION
select 'POINT (1 1)'::geometry as geom
) select st_convexHull(st_collect(geom))
from points;
If the points are aligned, convexHull will be a linestring, that you
can discard or further process according to your needs.
Nicolas
On 29 October 2012 16:51, Ed Linde edoli...@gmail.com wrote:
Hi Nicolas,
It seems like sometimes the points are one and the same and I get
linestrings and not
actual polygons. I pass these points in from a perl script, so is there a
way I can just
give the raw x,y,z coordinates of these points and do a st_convexhull
function on them?
Could you please give me a small example?
I tried something like:
The two coordinates marked in red and marked in orange are the same.
bounds=# insert into vfaces values (1,
bounds(# ST_GeomFromText('POLYGON((593901 5219610 814,593901 5219610
814,593899 5219610 814,593899 5219610 814,593901 5219610 814))') );
INSERT 0 1
bounds=# select st_isvalid(geomtext) from vfaces;
NOTICE: Too few points in geometry component at or near point 593901
5219610 814
st_isvalid
f
(1 row)
Thanks,
Ed
On Mon, Oct 29, 2012 at 4:26 PM, Ed Linde edoli...@gmail.com wrote:
Ok thanks, will look into that function. Because I wonder if the s/w I
am
using is
actually outputting the vertices of each face in a cyclical fashion or
just
arbitrarily.
Cheers,
Ed
On Mon, Oct 29, 2012 at 4:23 PM, Nicolas Ribot nicolas.ri...@gmail.com
wrote:
Yes, it looks like some points were not put in the right order before
building a polygon, thus these butterfly polygons you generated.
If the formed polygon are expected to be convex, you could use
st_convexhull on the point cloud to generate the polygons.
On 29 October 2012 16:09, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas! Will look at the script that generated the polygon
text..
must have goofed something up there.
On Mon, Oct 29, 2012 at 4:05 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
Hi,
No. this is because some of your polygons are not valid:
for instance:
select st_isvalid('POLYGON ((593921 5219610 803,593921 5219610
818,593921 5219620 818,593921 5219620 803,593921 5219610
803))'::geometry);
NOTICE: Too few points in geometry component at or near point
593921
5219610 803
st_isvalid
f
(1 row)
you can control this with st_isvalid, st_isvalidReason and correct
them with st_makeValid.
Use only valid objects before processing them with Postgis
functions.
Nicolas
On 29 October 2012 16:03, Ed Linde edoli...@gmail.com wrote:
Thanks Nicolas. Just about the error, is this because the line
segments
are
too close
and postgis 2.0 could not handle this? If so is there a
workaround,
even
if
it means
slightly having to perturb each point's position to not run into
this
bug.
I was really hoping that the intersection of two polygonal shapes
in
3D
would be fairly
simple in postgis 2.0 :(
Ed
On Mon, Oct 29, 2012 at 4:00 PM, Nicolas Ribot
nicolas.ri...@gmail.com
wrote:
If the points are computed in the right order, you can store them
and
pass them to st_makeLine and st_makePolygon.
If not, you can form a segment between 2 closest points and
connect
it
to the closest points.
On 29 October 2012 15:37, Ed Linde edoli...@gmail.com wrote:
Hi All,
Thanks for the tips! Just another thing, when I compute the
transition
points on each edge (shown as red points in my pdf).
I need to join them to make a polygon. Wondering how I can
connect
them
together so that I start with a point and end on it
to form a closed polygon?
Cheers,
Ed
On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's
st_Relate
because 5 is not adjacent to a linear edge, it is only
adjacent
to a
vertex,
so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the
Re: [postgis-users] out of memory
The image output looks correct to me. -bborie On 10/25/2012 10:43 PM, Mahavir Trivedi wrote: hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB input ) but problem occurred when i export it then output size increase. (image input size = 1591 X 1446) (image output size = 1600 X 1450) can i change blocksize of server ? if yes then how ? my system XP Windows 64-bit 4 GB RAM thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
Yes it can... someday. -bborie On 10/26/2012 06:55 AM, Pierre Racine wrote: This could be solved by ticket #826 http://trac.osgeo.org/postgis/ticket/826 Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Friday, October 26, 2012 8:26 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] out of memory The output size makes sense since the loader split the input raster into 100x100 tiles. 1591 / 100 = 15.91 ... 16 x 100 1446 / 100 = 14.46 ... 15 x 100 So, when unioning the tiles back together, the unioned raster should be 1600 x 1500 (don't know where you got 1450 though). -bborie On Thu, Oct 25, 2012 at 10:43 PM, Mahavir Trivedi mahavir.triv...@gmail.com wrote: hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB input ) but problem occurred when i export it then output size increase. (image input size = 1591 X 1446) (image output size = 1600 X 1450) can i change blocksize of server ? if yes then how ? my system XP Windows 64-bit 4 GB RAM thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ 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 -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
No, it was not supported, it is in svn now, and will be at next patch release http://trac.osgeo.org/postgis/ticket/2071 P. On Mon, Oct 29, 2012 at 7:04 AM, Ilie, Radu ri...@wsi.com wrote: Yes, I did. The error is this: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/usr/include/libxml2 -I../liblwgeom -I. -I. -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/server -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o lwgeom_estimate.c lwgeom_estimate.c: In function ‘LWGEOM_gist_joinsel’: lwgeom_estimate.c:318: error: ‘Form_pg_class’ undeclared (first use in this function) lwgeom_estimate.c:318: error: (Each undeclared identifier is reported only once lwgeom_estimate.c:318: error: for each function it appears in.) lwgeom_estimate.c:318: error: expected ‘;’ before ‘reltup’ lwgeom_estimate.c:319: error: ‘reltup’ undeclared (first use in this function) lwgeom_estimate.c:330: error: expected ‘;’ before ‘reltup’ make[1]: *** [lwgeom_estimate.o] Error 1 Radu From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Jeff Lake Sent: Monday, October 29, 2012 9:59 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 Did you specify the path to pg_config ?? eg: ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config Jeff Lake MichiganWxSystem AllisonHouse GRLevelXStuff On 10/29/2012 9:49, Ilie, Radu wrote: Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Is this a perverse use-case for raster types?
Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band for each timestep. There could be upwards of 36k to 72k timesteps for a typical model run. We have hundreds of model runs. So my question is, is it insane to be thinking of storing that many bands in a PostGIS raster? Or more specifically, is this _not_ a use case for which PostGIS rasters were designed? I notice that most of the examples in the docs and in PostGIS In Action focus only on images and I can imagine that handling multispectral satellite images as being more of the intended use case. I did a little benchmarking of a typical use case of ours (What's the average temperature inside a some polygon, e.g. a river basin?). I noticed that the run time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster, band, geometry) appears to be super-linear even when doing it on just a single band. I ran the following query: SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = rid (where basins is table of river basins with one single polygon and bcsd is a table with a raster column rast). for a set of rasters with increasing number of bands, and the time to run the query is shown in the attached plot. Since the raster properties are presumably shared across all the bands, it seems odd to me that run time would increase. I would expect it to be _contant_ (with constant number of pixels), but I suppose that that's my own ignorance as to how the PG type extensions work? Comments or explanations are welcome. ~James -- James Hiebert Lead, Computational Support Pacific Climate Impacts Consortium http://www.pacificclimate.org Room 112, University House 1, University of Victoria PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2 E-mail: hieb...@uvic.ca Tel: (250) 472-4521 Fax: (250) 472-4830 attachment: clip_time.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). If you're in the evaluation stage and you're compiling PostGIS yourself, I'd recommend trying SVN -trunk (will become 2.1) as it has additional capabilities and performance improvements. I'm already using -trunk in production as I needed the new features (full disclosure: I wrote almost the new features in -trunk). -bborie On 10/29/2012 03:32 PM, James Hiebert wrote: Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band for each timestep. There could be upwards of 36k to 72k timesteps for a typical model run. We have hundreds of model runs. So my question is, is it insane to be thinking of storing that many bands in a PostGIS raster? Or more specifically, is this _not_ a use case for which PostGIS rasters were designed? I notice that most of the examples in the docs and in PostGIS In Action focus only on images and I can imagine that handling multispectral satellite images as being more of the intended use case. I did a little benchmarking of a typical use case of ours (What's the average temperature inside a some polygon, e.g. a river basin?). I noticed that the run time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster, band, geometry) appears to be super-linear even when doing it on just a single band. I ran the following query: SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = rid (where basins is table of river basins with one single polygon and bcsd is a table with a raster column rast). for a set of rasters with increasing number of bands, and the time to run the query is shown in the attached plot. Since the raster properties are presumably shared across all the bands, it seems odd to me that run time would increase. I would expect it to be _contant_ (with constant number of pixels), but I suppose that that's my own ignorance as to how the PG type extensions work? Comments or explanations are welcome. ~James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. I had considered that (better, actually, than duplicating our data, right?), but was finding that st_intersects wasn't yet implemented for out of db storage. Looking through the trunk code, though, it appears that maybe you've gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints() is another good reason for me to seriously consider working out of trunk... Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Thanks; good to know. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). Ah, that changes the picture pretty dramatically (see attached plot). Since it improves by a lot more than a factor of two, I suspect maybe I'm having some desktop scaling issues or something. I'll go ahead and actually put this on our database server, try the trunk version, and go from there. This is at least somewhat encouraging :) Thanks for the suggestions. ~James On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). If you're in the evaluation stage and you're compiling PostGIS yourself, I'd recommend trying SVN -trunk (will become 2.1) as it has additional capabilities and performance improvements. I'm already using -trunk in production as I needed the new features (full disclosure: I wrote almost the new features in -trunk). -bborie On 10/29/2012 03:32 PM, James Hiebert wrote: Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band for each timestep. There could be upwards of 36k to 72k timesteps for a typical model run. We have hundreds of model runs. So my question is, is it insane to be thinking of storing that many bands in a PostGIS raster? Or more specifically, is this _not_ a use case for which PostGIS rasters were designed? I notice that most of the examples in the docs and in PostGIS In Action focus only on images and I can imagine that handling multispectral satellite images as being more of the intended use case. I did a little benchmarking of a typical use case of ours (What's the average temperature inside a some polygon, e.g. a river basin?). I noticed that the run time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster, band, geometry) appears to be super-linear even when doing it on just a single band. I ran the following query: SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = rid (where basins is table of river basins with one single polygon and bcsd is a table with a raster column rast). for a set of rasters with increasing number of bands, and the time to run the query is shown in the attached plot. Since the raster properties are presumably shared across all the bands, it seems odd to me that run time would increase. I would expect it to be _contant_ (with constant number of pixels), but I suppose that that's my own ignorance as to how the PG type extensions work? Comments or explanations are welcome. ~James -- James Hiebert Lead, Computational Support Pacific Climate Impacts Consortium http://www.pacificclimate.org Room 112, University House 1, University of Victoria PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2 E-mail: hieb...@uvic.ca Tel: (250) 472-4521 Fax: (250) 472-4830 attachment: clip_time.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. As for performance of in-db vs out-db, in-db is slightly faster but my benchmarks are rather old. I hope to do some testing soon to see if I can improve out-db performance. Tile size is critical regardless of whether or not you're going to store your rasters in-db or out-db. Generally, tiles should be 100x100 or smaller. Ideal tile size depends upon the input raster's dimensions and what tile dimension is cleanly divisible from the raster's dimension. I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. -bborie On 10/29/2012 04:23 PM, James Hiebert wrote: If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. I had considered that (better, actually, than duplicating our data, right?), but was finding that st_intersects wasn't yet implemented for out of db storage. Looking through the trunk code, though, it appears that maybe you've gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints() is another good reason for me to seriously consider working out of trunk... Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Thanks; good to know. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). Ah, that changes the picture pretty dramatically (see attached plot). Since it improves by a lot more than a factor of two, I suspect maybe I'm having some desktop scaling issues or something. I'll go ahead and actually put this on our database server, try the trunk version, and go from there. This is at least somewhat encouraging :) Thanks for the suggestions. ~James On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). If you're in the evaluation stage and you're compiling PostGIS yourself, I'd recommend trying SVN -trunk (will become 2.1) as it has additional capabilities and performance improvements. I'm already using -trunk in production as I needed the new features (full disclosure: I wrote almost the new features in -trunk). -bborie On 10/29/2012 03:32 PM, James Hiebert wrote: Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band for each timestep. There could be upwards of 36k to 72k timesteps for a typical model run. We have hundreds of model runs. So my question is, is it insane to be thinking of storing that many bands in a PostGIS raster? Or more specifically, is this _not_ a use case for which PostGIS rasters were designed? I notice that most of the examples in the docs and in PostGIS In Action focus only on images and I can imagine that handling multispectral satellite images as being more of the intended use case. I did a little benchmarking of a typical use case of ours (What's the average temperature inside a some polygon, e.g. a river basin?). I noticed that the run time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster, band, geometry) appears to be super-linear even when doing it on just a single band. I ran the following query: SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = rid (where basins is table of river basins with one single polygon and bcsd is a table with a raster column rast). for a set of rasters with increasing number of bands, and the time to run the query is shown in the attached plot. Since the raster properties are presumably shared across all the bands, it seems odd to me that run time would increase. I would expect it
Re: [postgis-users] Is this a perverse use-case for raster types?
I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. Hmmm, for me it it fails for the (raster, integer, geometry) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = 39; ERROR: rt_raster_intersects not implemented yet for OFFDB bands CONTEXT: PL/pgSQL function _st_intersects line 20 at RETURN but it appears that you're right for the (geometry, raster, integer) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(the_geom, rast, 1) WHERE rid = 39; rid - 39 (1 row) I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. Ah ha! Yes, that's definitely the case. With out of db storage, each of intersects/clip queries comes back in 200ms, regardless of num bands. That's more of the behaviour that I was expecting, too. Thanks for helping me put a finger on it! ~James On Mon, Oct 29, 2012 at 04:33:36PM -0700, Bborie Park wrote: I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. As for performance of in-db vs out-db, in-db is slightly faster but my benchmarks are rather old. I hope to do some testing soon to see if I can improve out-db performance. Tile size is critical regardless of whether or not you're going to store your rasters in-db or out-db. Generally, tiles should be 100x100 or smaller. Ideal tile size depends upon the input raster's dimensions and what tile dimension is cleanly divisible from the raster's dimension. I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. -bborie On 10/29/2012 04:23 PM, James Hiebert wrote: If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. I had considered that (better, actually, than duplicating our data, right?), but was finding that st_intersects wasn't yet implemented for out of db storage. Looking through the trunk code, though, it appears that maybe you've gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints() is another good reason for me to seriously consider working out of trunk... Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Thanks; good to know. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). Ah, that changes the picture pretty dramatically (see attached plot). Since it improves by a lot more than a factor of two, I suspect maybe I'm having some desktop scaling issues or something. I'll go ahead and actually put this on our database server, try the trunk version, and go from there. This is at least somewhat encouraging :) Thanks for the suggestions. ~James On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). If you're in the evaluation stage and you're compiling PostGIS yourself, I'd recommend trying SVN -trunk (will become 2.1) as it has additional capabilities and performance improvements. I'm already using -trunk in production as I needed the new features (full disclosure: I wrote almost the new features in -trunk). -bborie On 10/29/2012 03:32 PM, James Hiebert wrote: Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band for each timestep. There could be upwards of 36k to 72k timesteps for a typical model run. We have hundreds of model runs. So my question is, is it insane to be thinking of storing that many bands in a PostGIS raster? Or more specifically, is
Re: [postgis-users] Is this a perverse use-case for raster types?
Wow. What version of PostGIS are you running? Great to hear that the out-db works for you. I always expected that out-db would work better for rasters with large numbers of bands. out-db rasters does have the limitation that they are read-only. -bborie On 10/29/2012 05:02 PM, James Hiebert wrote: I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. Hmmm, for me it it fails for the (raster, integer, geometry) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = 39; ERROR: rt_raster_intersects not implemented yet for OFFDB bands CONTEXT: PL/pgSQL function _st_intersects line 20 at RETURN but it appears that you're right for the (geometry, raster, integer) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(the_geom, rast, 1) WHERE rid = 39; rid - 39 (1 row) I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. Ah ha! Yes, that's definitely the case. With out of db storage, each of intersects/clip queries comes back in 200ms, regardless of num bands. That's more of the behaviour that I was expecting, too. Thanks for helping me put a finger on it! ~James On Mon, Oct 29, 2012 at 04:33:36PM -0700, Bborie Park wrote: I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. As for performance of in-db vs out-db, in-db is slightly faster but my benchmarks are rather old. I hope to do some testing soon to see if I can improve out-db performance. Tile size is critical regardless of whether or not you're going to store your rasters in-db or out-db. Generally, tiles should be 100x100 or smaller. Ideal tile size depends upon the input raster's dimensions and what tile dimension is cleanly divisible from the raster's dimension. I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. -bborie On 10/29/2012 04:23 PM, James Hiebert wrote: If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. I had considered that (better, actually, than duplicating our data, right?), but was finding that st_intersects wasn't yet implemented for out of db storage. Looking through the trunk code, though, it appears that maybe you've gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints() is another good reason for me to seriously consider working out of trunk... Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Thanks; good to know. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). Ah, that changes the picture pretty dramatically (see attached plot). Since it improves by a lot more than a factor of two, I suspect maybe I'm having some desktop scaling issues or something. I'll go ahead and actually put this on our database server, try the trunk version, and go from there. This is at least somewhat encouraging :) Thanks for the suggestions. ~James On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). If you're in the evaluation stage and you're compiling PostGIS yourself, I'd recommend trying SVN -trunk (will become 2.1) as it has additional capabilities and performance improvements. I'm already using -trunk in production as I needed the new features (full disclosure: I wrote almost the new features in -trunk). -bborie On 10/29/2012 03:32 PM, James Hiebert wrote: Hi All, I'm considering using PostGIS rasters for storage of raster data at my organization and I'm looking for some advice (or perhaps a reality check). I work for a region climate services provider and the vast majority of our data (by volume, not necessarily complexity) are output from climate models. These are generally a n-by-m raster with one band
Re: [postgis-users] Is this a perverse use-case for raster types?
= select version(), postgis_full_version(), postgis_raster_lib_version(); PostgreSQL 9.1.5 on x86_64-pc-linux-gnu, compiled by x86_64-linux-gnu-gcc (Gentoo 4.4.6-r1 p1.0, pie-0.4.5) 4.4.6, 64-bit | POSTGIS=2.0.1 r9979 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.8.0 LIBJSON=UNKNOWN (core procs from 2.0.0 r9605 need upgrade) RASTER (raster procs from 2.0.0 r9605 need upgrade) | 2.0.1 r9979 out-db rasters does have the limitation that they are read-only. Good to know; shouldn't be a problem for us as model output is fundamentally immutable. Any other limitations that I should be aware of? ~James On Mon, Oct 29, 2012 at 05:05:03PM -0700, Bborie Park wrote: Wow. What version of PostGIS are you running? Great to hear that the out-db works for you. I always expected that out-db would work better for rasters with large numbers of bands. out-db rasters does have the limitation that they are read-only. -bborie On 10/29/2012 05:02 PM, James Hiebert wrote: I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. Hmmm, for me it it fails for the (raster, integer, geometry) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = 39; ERROR: rt_raster_intersects not implemented yet for OFFDB bands CONTEXT: PL/pgSQL function _st_intersects line 20 at RETURN but it appears that you're right for the (geometry, raster, integer) signature: raster_test= SELECT rid FROM basins INNER JOIN bcsd ON ST_Intersects(the_geom, rast, 1) WHERE rid = 39; rid - 39 (1 row) I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. Ah ha! Yes, that's definitely the case. With out of db storage, each of intersects/clip queries comes back in 200ms, regardless of num bands. That's more of the behaviour that I was expecting, too. Thanks for helping me put a finger on it! ~James On Mon, Oct 29, 2012 at 04:33:36PM -0700, Bborie Park wrote: I believe ST_Intersects() works on out-of-db rasters in the 2.0 series, possibly 2.0.1. As for performance of in-db vs out-db, in-db is slightly faster but my benchmarks are rather old. I hope to do some testing soon to see if I can improve out-db performance. Tile size is critical regardless of whether or not you're going to store your rasters in-db or out-db. Generally, tiles should be 100x100 or smaller. Ideal tile size depends upon the input raster's dimensions and what tile dimension is cleanly divisible from the raster's dimension. I wonder what your benchmark's performance would be like if the raster is out-db. I'd expect a flat line with little change regardless the # of bands. -bborie On 10/29/2012 04:23 PM, James Hiebert wrote: If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. I had considered that (better, actually, than duplicating our data, right?), but was finding that st_intersects wasn't yet implemented for out of db storage. Looking through the trunk code, though, it appears that maybe you've gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints() is another good reason for me to seriously consider working out of trunk... Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Thanks; good to know. Another problem with your benchmark query is that the ST_Clip() is running twice (for height and width). Ah, that changes the picture pretty dramatically (see attached plot). Since it improves by a lot more than a factor of two, I suspect maybe I'm having some desktop scaling issues or something. I'll go ahead and actually put this on our database server, try the trunk version, and go from there. This is at least somewhat encouraging :) Thanks for the suggestions. ~James On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: James, I use PostGIS raster for a similar purpose (model outputs) though my model outputs are for a specific day (average temperature for a specific date). So, one raster with one band per day per variable. I could combine a year's worth of bands into one raster but I decided against that. If you've got a large number of bands (100s or more), you may want to consider having the rasters be out-of-db. Part of the problem is that anything stored in PostgreSQL (in-db) is TOASTed so needs to be deserialized (and probably decompressed). So, if the serialized raster is big (more bands), the deTOASTing will take longer. Another problem with
[postgis-users] Optimizing nearest neighbor search with conditions
I recently updated a postgresql database to use postgis 2.0.1 - I am trying to improve the performance of our nearest neighbor queries using the new - operator, but ran into an issue. Our nearest neighbor query also has a handful of other conditions beyond just distance. For the majority of queries using - speeds up the queries by an order of magnitude, unfortunately in the cases where the other conditions can't be met for the query, it performance a sequential scan of the entire index, taking a query that normally takes 20-30ms take 7 seconds. I've tried several things to work around this: * adding a bounding box similar to what we used with postgis 1.x - it always uses the index with the bounding box, even when using the index with the order by would be 10x faster * increasing statistics value for the geometry column in case that was causing the poor choice of index usage, this had no effect * adding a distance constraint that can't be used for the index (st_distance_sphere) - this reverts back to the full index scan, but doesn't halt the full index scan in the worst case. It doesn't seem able to recognize the fact that logically no further items in the ordered index could match Is this a known limitation for nearest neighbor queries with postgis or is there a known workaround for this kind of situation? Thanks in advance for your help, Doug ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Optimizing nearest neighbor search with conditions
Unless you can draw a set from the ordering that is large enough to always contain a result that passes your filters, I think you're out of luck. The reality is that the ordering operators are quite limited, there's all sorts of useful things one would like to do with them (table join on nearest, for example) that one cannot. P. On Mon, Oct 29, 2012 at 8:11 PM, Doug Cole dougc...@gmail.com wrote: I recently updated a postgresql database to use postgis 2.0.1 - I am trying to improve the performance of our nearest neighbor queries using the new - operator, but ran into an issue. Our nearest neighbor query also has a handful of other conditions beyond just distance. For the majority of queries using - speeds up the queries by an order of magnitude, unfortunately in the cases where the other conditions can't be met for the query, it performance a sequential scan of the entire index, taking a query that normally takes 20-30ms take 7 seconds. I've tried several things to work around this: * adding a bounding box similar to what we used with postgis 1.x - it always uses the index with the bounding box, even when using the index with the order by would be 10x faster * increasing statistics value for the geometry column in case that was causing the poor choice of index usage, this had no effect * adding a distance constraint that can't be used for the index (st_distance_sphere) - this reverts back to the full index scan, but doesn't halt the full index scan in the worst case. It doesn't seem able to recognize the fact that logically no further items in the ordered index could match Is this a known limitation for nearest neighbor queries with postgis or is there a known workaround for this kind of situation? Thanks in advance for your help, Doug ___ 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
