Got it working<br><br>SELECT gen_id, <br> ST_X((dp).geom),<br> ST_Y((dp).geom),<br> ST_Z((dp).geom) <br>FROM (<br> SELECT gen_id, ST_dumpPoints(geomtext) as dp<br> from vfaces <br> ) As foo;<br>
<br><br><div class="gmail_quote">On Tue, Oct 30, 2012 at 11:19 AM, Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<div>
<div dir="ltr" align="left"><span><font>Maybe ST_X,
ST_Y and ST_Z ?</font></span></div>
<div dir="ltr" align="left"><span><font></font></span> </div>
<div dir="ltr" align="left"><span><font>Hugues.</font></span></div>
<div> </div><br>
<div dir="ltr" align="left" lang="fr">
<hr>
<font face="Tahoma"><b>De :</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>De la part de</b> Ed
Linde<br><b>Envoyé :</b> mardi 30 octobre 2012 11:14<br><b>À :</b>
PostGIS Users Discussion<br><b>Objet :</b> Re: [postgis-users] Problem with
3D polygon intersection test<br></font><br></div><div><div class="h5">
<div></div>Hi Nicolas,<br>Not quite sure what to make of the output from
ST_DumpPoints, how do I transform it to just raw x,y,z
coordinates?<br><br> select st_astext(geomtext) from small_tris where
id=0;<br>
st_astext
<br>-------------------------------------------------------<br> POLYGON Z
((10 10 805,20 10 809,10 20 814,10 10 805))<br>(1 row)<br><br>bounds=#
select id, st_dumpPoints( geomtext ) from small_tris where id=0;<br> id
|
st_dumppoints
<br>----+----------------------------------------------------------------------<br>
0 |
("{1,1}",0101000080000000000000244000000000000024400000000000288940)<br> 0
| ("{1,2}",0101000080000000000000344000000000000024400000000000488940)<br>
0 |
("{1,3}",0101000080000000000000244000000000000034400000000000708940)<br> 0
| ("{1,4}",0101000080000000000000244000000000000024400000000000288940)<br>(4
rows)<br><br><br>Thanks,Ed<br><br><br>
<div class="gmail_quote">On Tue, Oct 30, 2012 at 11:05 AM, Ed Linde <span dir="ltr"><<a href="mailto:edolinde@gmail.com" target="_blank">edolinde@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Ok thanks!
<div>
<div><br><br>
<div class="gmail_quote">On Tue, Oct 30, 2012 at 11:04 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">st_dumpRings will return the rings (closed linestrings)
composing the polygon (exterior first, then interiors)
<div>and st_dumpPoints will return all the points forming the rings, in the
right order.</div><span><font color="#888888">
<div><br></div></font></span>
<div><span><font color="#888888">Nicolas</font></span>
<div>
<div><br><br>
<div class="gmail_quote">On 30 October 2012 10:56, Ed Linde <span dir="ltr"><<a href="mailto:edolinde@gmail.com" target="_blank">edolinde@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Ok, for a polygon, is there a good postgis function that
can return the edges to me in the <br>right sequence? Or at least the
points that constitute the polygon in the right order, so I
can<br>generate the edges in a script?<br><br>Thanks,<br>Ed
<div>
<div><br><br>
<div class="gmail_quote">On Tue, Oct 30, 2012 at 10:46 AM, Ed Linde <span dir="ltr"><<a href="mailto:edolinde@gmail.com" target="_blank">edolinde@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Ah drat! Ok then I have to basically do edge
intersection tests by myself.
<div>Ah well. bad news! :(
<div>
<div><br><br>
<div class="gmail_quote">On Tue, Oct 30, 2012 at 10:44 AM, Nicolas Ribot
<span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Hi,
<div><br></div>
<div>st_intersection is only suitable for 2D operations. Third
dimension is not taken into account.</div>
<div><br></div>
<div>Nicolas </div>
<div><br>
<div class="gmail_quote">
<div>
<div>On 30 October 2012 09:44, Ed Linde <span dir="ltr"><<a href="mailto:edolinde@gmail.com" target="_blank">edolinde@gmail.com</a>></span> wrote:<br></div></div>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">
<div>
<div>Hi All,<br>Is there a simpler test I can do? I am quite sure
that I am probably not using the right <br>function in my SQL, or
not doing the intersection test correctly. Is there any simpler
<br>way to test this 3D intersection? Am waiting on the postgis
gurus to come along and<br>give me some ideas, coz I am at my wits
ends on this one! :) The polygons look good<br>to me when I
visualize them, but don't get how all triangles are found as
intersecting?<br>Anyway will wait.<br><br>Cheers,<br>Ed<br><br>
<div class="gmail_quote">On Tue, Oct 30, 2012 at 9:00 AM, Ed Linde
<span dir="ltr"><<a href="mailto:edolinde@gmail.com" target="_blank">edolinde@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Hi All,<br>I am having some troubles getting the
<b>ST_Intersect</b> function to work with 3D polygons. <br>I have
a table to hold triangles as polygons in
<b>"small_tris"</b><br>Another table for holding the polygonal
faces of a 3D voronoi diagram in <b>"vfaces"</b><br>So now I am
trying to do an intersection between the triangles and the voronoi
faces<br>to get the triangles that intersect with the faces. And
it seems that *ALL* triangles <br>are intersecting. I have
attached a PDF where I have plotted the triangles and the
<br>voronoi cells. so the box around in red and blue with a major
face going through is <br>the voronoi diagram. The Voronoi
partition in 3D is shown at the bottom too for clarity<br>Can
someone please help and let me know what am I doing wrong
here?<br>Is the select assuming something? So the polygons are
flat and the intersections <br>should just be lines or points. I
am under some pressure to get this to work, so if<br>anyone needs
more information to help, the please let me know! :)
<br><br>Cheers,<br>Ed<br><br><b>-- TABLE SCHEMAS</b><br>bounds=#
\d vfaces;<br> Table
"public.vfaces"<br> Column |
Type | Modifiers
<br>----------+----------+-----------<br> gen_id
| integer | <br> geomtext | geometry | <br><br>bounds=#
\d small_tris;<br> Table
"public.small_tris"<br> Column |
Type | Modifiers
<br>----------+----------+-----------<br> id
| integer | not
null<br> v0 |
integer | <br> v1 |
integer | <br> v2 |
integer | <br> geomtext | geometry |
<br>Indexes:<br> "small_tris_pkey" PRIMARY KEY,
btree (id)<br> "small_idx" gist
(geomtext)<br><br><br><b><br>-- TABLE CONTENT</b><br>bounds=#
select id, st_astext( geomtext ) from small_tris;<br> id
|
st_astext
<br>----+-------------------------------------------------------<br>
0 | POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10
805))<br> 1 | POLYGON Z ((10 20 814,20 10 809,20 20 819,10
20 814))<br> 2 | POLYGON Z ((20 20 819,30 10 809,30 20
812,20 20 819))<br> 3 | POLYGON Z ((30 10 809,20 20 819,20
10 809,30 10 809))<br> 4 | POLYGON Z ((30 20 812,30 10
809,40 10 826,30 20 812))<br> 5 | POLYGON Z ((20 20 819,30
20 812,20 30 827,20 20 819))<br> 6 | POLYGON Z ((10 20
814,20 20 819,20 30 827,10 20 814))<br> 7 | POLYGON Z ((10
40 805,10 30 814,20 30 827,10 40 805))<br> 8 | POLYGON Z
((10 50 804,10 40 805,20 40 807,10 50 804))<br> 9 | POLYGON
Z ((20 30 827,20 40 807,10 40 805,20 30 827))<br> 10 |
POLYGON Z ((30 30 817,20 40 807,20 30 827,30 30 817))<br> 11
| POLYGON Z ((30 40 816,20 50 809,20 40 807,30 40
816))<br> 12 | POLYGON Z ((20 40 807,20 50 809,10 50 804,20
40 807))<br> 13 | POLYGON Z ((10 30 814,10 20 814,20 30
827,10 30 814))<br> 14 | POLYGON Z ((30 30 817,20 30 827,30
20 812,30 30 817))<br> 15 | POLYGON Z ((30 20 812,40 20
823,30 30 817,30 20 812))<br> 16 | POLYGON Z ((40 20 823,30
20 812,40 10 826,40 20 823))<br> 17 | POLYGON Z ((40 20
823,40 10 826,50 10 803,40 20 823))<br> 18 | POLYGON Z ((30
30 817,40 20 823,40 30 814,30 30 817))<br> 19 | POLYGON Z
((40 20 823,50 20 808,40 30 814,40 20 823))<br> 20 | POLYGON
Z ((50 20 808,40 20 823,50 10 803,50 20 808))<br> 21 |
POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814))<br> 22
| POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30
817))<br> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30
40 816))<br> 24 | POLYGON Z ((30 40 816,30 30 817,40 40
800,30 40 816))<br> 25 | POLYGON Z ((30 50 813,40 40 800,40
50 817,30 50 813))<br> 26 | POLYGON Z ((30 40 816,30 50
813,20 50 809,30 40 816))<br> 27 | POLYGON Z ((50 40 821,40
50 817,40 40 800,50 40 821))<br> 28 | POLYGON Z ((50 50
809,40 50 817,50 40 821,50 50 809))<br> 29 | POLYGON Z ((50
40 821,40 40 800,50 30 816,50 40 821))<br> 30 | POLYGON Z
((40 30 814,50 30 816,40 40 800,40 30 814))<br> 31 | POLYGON
Z ((30 30 817,30 40 816,20 40 807,30 30 817))<br>(32
rows)<br><br> select st_astext(geomtext) from
vfaces;<br>
st_astext
<br>-----------------------------------------------------------------------------------------------<br> POLYGON
Z ((51 17.8 799,9 38.8 799,9 51 819.33333333333,19.4 51 828,51
35.2 828,51 17.8 799))<br> POLYGON Z ((51 17.8 799,9 38.8
799,9 51 799,51 51 799,51 17.8 799))<br> POLYGON Z ((51 35.2
828,19.4 51 828,51 51 828,51 35.2 828))<br> POLYGON Z ((9 9
799,9 38.8 799,51 17.8 799,51 9 799,9 9 799))<br> POLYGON Z
((51 17.8 799,9 38.8 799,9 51 819.33333333333,19.4 51 828,51 35.2
828,51 17.8 799))<br> POLYGON Z ((9 9 828,9 51 828,19.4 51
828,51 35.2 828,51 9 828,9 9 828))<br>(6 rows)<br><br><b style="COLOR:rgb(51,102,255)">select distinct <a href="http://a.id" target="_blank">a.id</a><br>from small_tris a,
vfaces b<br>where st_intersects (a.geomtext, b.geomtext)<br>order
by <a href="http://a.id" target="_blank">a.id</a>;</b><br> id
<br>----<br> 0<br> 1<br> 2<br> 3<br>
4<br> 5<br> 6<br> 7<br> 8<br>
9<br> 10<br> 11<br> 12<br> 13<br> 14<br> 15<br> 16<br> 17<br> 18<br> 19<br> 20<br> 21<br> 22<br> 23<br> 24<br> 25<br> 26<br> 27<br> 28<br> 29<br> 30<br> 31<br>(32
rows)<br><br><img src="" alt=""><br></blockquote></div><br><br></div></div>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div></blockquote></div><br></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></blockquote></div><br></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>