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

Paul Ramsey pramsey at cleverelephant.ca
Mon Sep 1 11:18:01 PDT 2014

I do think that all the time, however the array[] stuff is quite
intriguing, since it's such a powerful general purpose piece of PgSQL
functionality. We get a lot of leverage from other bits of array[]
support in the main code base, so making more use of it ourselves
could have some serious benefit.

Of course, in the GIS case, the odds of an array[] function running to
memory exhaustion are an order of magnitude higher that in the boring
old data case...


On Mon, Sep 1, 2014 at 6:25 AM, Paragon Corporation <lr at pcorp.us> wrote:
> Okay forgot about that use case.
> 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
> Just questionable (which I 'm sure Paul is thinking)
> If any of these are useful enough to pollute the code base with more
> functions.
> Thanks,
> Regina
> ________________________________
> From: postgis-users-bounces at lists.osgeo.org
> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
> Sent: Monday, September 01, 2014 9:19 AM
> To: PostGIS Users Discussion
> Cc: PostGIS Development Discussion
> Subject: Re: [postgis-users] [postgis-devel] Is there a reason we don't have
> an ST_Intersection aggregate function
> sorry was sent too early
> 2014-09-01 15:18 GMT+02:00 Rémi Cura <remi.cura at gmail.com>:
>> Hey,
>> I think you misunderstood me,
>> of course array of geom are not a replacement of proper use of aggregate.
>> It's just that in some case you cannot do with aggregate and array
>> function are very usefull, or the only solution.
>> (example :
>> WITH A AS (
>> SELECT geom1,geom2,geom3,geom4 ..
>> )
> SELECT ST_UNion (ARRAY[geom1,geom2,..])
> Cheers,
> Rémi-c
>> 2014-09-01 14:39 GMT+02:00 Paragon Corporation <lr at pcorp.us>:
>>>  >  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.
>>>  >  Adding aggregates is really less a priority than adding support for
>>> >
>>> ARRAY[geom1,geom2,geom3...] for the relevant functions.
>>> >
>>> > Are you sure that the planner and indexes will manage arrays better?
>>> 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
>>> with array you'd have to accumulate them first.
>>> It really is a sequential thing.
>>> 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.
>>> I could be wrong on that of course. Paul and Nicklas can correct me on
>>> that since they are more familiar with the innards.
>>> 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.
>>> 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.
>>>  >  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.
>>> I actually hadn't thought of our ST_UnaryUnion
>>> http://postgis.net/docs/manual-2.1/ST_UnaryUnion.html  equivalent of
>>> ST_Intersection  which is what I think Nicklas is talking about here.  That
>>> would be useful as well.
>>> That introduces another question though?
>>> Would an ST_Intersection aggregate double as an ST_UnaryIntersection?
>>> or we just keep them separate.
>>> 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.
>>> Thanks,
>>> Regina
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

More information about the postgis-devel mailing list