<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.17054"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=275162213-01092014><FONT color=#0000ff
size=2 face=Arial>Okay forgot about that use case.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=275162213-01092014><FONT color=#0000ff
size=2 face=Arial>So we have a need for all I guess --> ST_Intersection
(both array and aggregate) similar to what we have for others and
ST_UnaryIntersection</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=275162213-01092014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=275162213-01092014><FONT color=#0000ff
size=2 face=Arial>Just questionable (which I 'm sure Paul is
thinking)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=275162213-01092014><FONT color=#0000ff
size=2 face=Arial>If any of these are useful enough to pollute the code base
with more functions.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=275162213-01092014></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>T<SPAN
class=275162213-01092014>hanks,</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=275162213-01092014></SPAN><SPAN
class=275162213-01092014></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN
class=275162213-01092014>egina</SPAN></FONT></FONT></FONT><BR></DIV>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Rémi
Cura<BR><B>Sent:</B> Monday, September 01, 2014 9:19 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Cc:</B> PostGIS Development Discussion<BR><B>Subject:</B>
Re: [postgis-users] [postgis-devel] Is there a reason we don't have an
ST_Intersection aggregate function<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>sorry was sent too early<BR>
<DIV class=gmail_extra><BR><BR>
<DIV class=gmail_quote>2014-09-01 15:18 GMT+02:00 Rémi Cura <SPAN dir=ltr><<A
href="mailto:remi.cura@gmail.com"
target=_blank>remi.cura@gmail.com</A>></SPAN>:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV dir=ltr>
<DIV>
<DIV>
<DIV>
<DIV>Hey,<BR></DIV>I think you misunderstood me,<BR></DIV>of course array of
geom are not a replacement of proper use of aggregate.<BR></DIV>It's just that
in some case you cannot do with aggregate and array function are very usefull,
or the only solution.<BR></DIV>
<DIV>(example :<BR></DIV>
<DIV>WITH A AS (<BR></DIV>
<DIV>SELECT geom1,geom2,geom3,geom4 ..<BR></DIV>
<DIV>)<BR></DIV></DIV></BLOCKQUOTE>
<DIV>SELECT ST_UNion (ARRAY[geom1,geom2,..]) <BR></DIV>
<DIV><BR></DIV>
<DIV>Cheers,<BR>Rémi-c <BR></DIV>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV dir=ltr><BR><BR>
<DIV class=gmail_quote>2014-09-01 14:39 GMT+02:00 Paragon Corporation <SPAN
dir=ltr><<A href="mailto:lr@pcorp.us"
target=_blank>lr@pcorp.us</A>></SPAN>:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV>
<DIV class=h5><U></U>
<DIV>
<DIV dir=ltr align=left><FONT color=#0000ff face=Arial></FONT> </DIV>
<DIV>
<DIV>
<DIV>
<DIV><FONT color=#0000ff face=Arial></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial></FONT><BR></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial> >
</FONT></SPAN>What I target is not the unioning between the layers but
inside the same layer. The problem is the same -> to do something with
more than two geometries involved.</DIV>
<DIV><FONT color=#0000ff face=Arial></FONT> </DIV></DIV>
<DIV>
<DIV>
<DIV>
<DIV>
<DIV>
<DIV>
<DIV><BR>
<DIV><SPAN><FONT color=#0000ff face=Arial> >
</FONT></SPAN>Adding aggregates is really less a priority than adding
support for <BR>></DIV></DIV>
<DIV>
<DIV>ARRAY[geom1,geom2,geom3...] for the relevant
functions.<BR>><SPAN><FONT color=#0000ff
face=Arial> </FONT></SPAN></DIV></DIV>
<DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>> <FONT color=#000000 size=3
face="Times New Roman">Are you sure that the planner and indexes will manage
arrays better?</FONT></FONT></SPAN></DIV>
<DIV><SPAN></SPAN> </DIV></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>The reason I was thinking an array
of aggregates is not as useful as an ST_Intersection aggregate is that as
you intersect geometries you are using less memory rather than
more</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>with array you'd have to
accumulate them first.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>It really is a sequential
thing.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>That is not to say the ARRAY
version isn't useful, but if you have a bunch of geometries and then have to
array them to get an intersection, then it would be
slower.</FONT></SPAN></DIV>
<DIV><SPAN><SPAN><FONT color=#0000ff face=Arial>I could be wrong on that of
course. Paul and Nicklas can correct me on that since they are more familiar
with the innards.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>I have on occasion come into the
situation where the things I want to get intersection of are in separate
rows rather than separate layers and it is a tad bit annoying to work around
it. Doesn't come up often though.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><SPAN><FONT color=#0000ff face=Arial>Now with 9.4 coming with the
FILTER syntax now makes the across rows much easier if I actually had an
ST_Intersection aggregate that could take advantage of
it.</FONT></SPAN></SPAN></DIV>
<DIV>
<DIV><SPAN><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN><SPAN>
<DIV><SPAN><FONT color=#0000ff face=Arial> >
</FONT></SPAN>Yes, I guess you are right that it would be possible to
write a function that does the job from a collection</DIV>
<DIV><BR></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial> > </FONT></SPAN>But
the function will have to do the same thing. To calculate a result from 2
polygons, then use the resulting polygon for calculation against the third
polygon and so on. Just like an aggregate function
works.</DIV></SPAN></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>I actually hadn't thought of our
ST_UnaryUnion <A
href="http://postgis.net/docs/manual-2.1/ST_UnaryUnion.html"
target=_blank>http://postgis.net/docs/manual-2.1/ST_UnaryUnion.html</A>
equivalent of ST_Intersection which is what I think Nicklas is
talking about here. That would be useful as well.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>That introduces another question
though?</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>Would an ST_Intersection aggregate
double as an ST_UnaryIntersection?</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>or we just keep them
separate.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>I would say keep them separate so
that we have a parallel with ST_UnaryUnion and also you can use it NOT as an
aggregate to satisfy the array like need.</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial>Regina</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV><BR></DIV></DIV>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV></BODY></HTML>