# [postgis-devel] Is there a reason we don't have an ST_Intersection aggregate function

=?UTF-8?Q?Nicklas_Av=E9n?= nicklas.aven at jordogskog.no
Mon Sep 1 02:46:51 PDT 2014

```Yes, I guess it is what you call layer union layer case I am talking about.

But the point is that it is not unly the intersection between layers that needs to be calculated but also between the polygons in the same layer.

To solve that in PostGIS today is quite dirty:
http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables2

and more discussions :
http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables

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.

Yes, I guess you are right that it would be possible to write a function that does the job from a collection

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.

>ARRAY[geom1,geom2,geom3...] for the relevant functions.
>
Ok ?

Why not just use:
geometry ST_Collect(geometry[] g1_array);

What would an array of geometries be that a collection isn't?

I think that aggregates is very interesting. It opens a lot of possibilities to to do something with a bunch of geoemtries, and combinate them in different ways.
Like the ST_AsTWKBagg function. There I pack the geoemtries as twkb in an aggregate function. But instead of using ST_Collect I wrote a new aggregate. In that way I could pack also the individual id:s of the geometris in the twkb. Another thing that I gained from that was that I could continue the delta encoding over many rows.
When aggregating points that can make a huge difference. ST_AsTWKBagg can make interesting vector tiles for instance.

>The essential difference that is often blocking is that aggregates can't be used in a lot of places
>(FROM, WHERE, in ordering, in windows ...), and you can't stake theim (aggregate(aggregate()) is forbiden).
>
>So often going the array way is the only short solution(you have always the possibility ot use lot's of CTE but with big data this has a cost).
>

Well, this is the way I find my self go a lot. Using aggregates in sub queries or CTE.

Are you sure that the planner and indexes will manage arrays better?

/Nicklas

>Cheers,
>Rémi-C
>>
>
>
>
>
>
>
>
>

>>

>
>>
2014-09-01 10:37 GMT+02:00 Nicklas Avén <nicklas.aven at jordogskog.no>:
>>
In general I think we could do a lot of cool things with aggregates.>
I have had a thought in my head for a very long time that I haven't had time to investigate.>

>>
Quite often people is asking for a function like union in the esri world.>
resources.arcgis.com/en/help/main/10.1/index.html#//000800000010000000>

>>
In PostGIS we have a problem to do calculations that involves more than 2 geoemtries.>
But as an aggregate function it vould be possible to do.>

>>
/Nicklas>
>

>2014-09-01 Paul Ramsey  wrote:
>
>Only reason is nobody ever asked for it, it's hard to figure great use
>>cases for it.
>>
>>P
>>
>>
>On Sun, Aug 31, 2014 at 6:16 PM, Paragon Corporationwrote:
>>> I wasn't sure if we just thought there wasn't much utility in it or if there
>>> was a technical obstacle.
>>>
>>> I would think that would be easier to build than the other aggregates since
>>> as you add more geometries it shrinks and the current aggregate state is
>>> always the result of the previous state plus new geometry (so no need for an
>>> accumulation function or a final state function) and if you ever end up with
>>> a  geometry collection, you short-circuit out.
>>>
>>> Thanks,
>>> Regina
>>>
>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at lists.osgeo.org
>>> lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>>_______________________________________________
>>postgis-devel mailing list
>>postgis-devel at lists.osgeo.org
>>lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>>
>>
>_______________________________________________
>postgis-devel mailing list
>postgis-devel at lists.osgeo.org
>lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20140901/286b53e0/attachment.html>
```