[postgis-users] st_union

[paul.malm at lfv.se]

Hi again!

I tried this first:
CREATE TABLE public."Areas_union" AS select  (st_dump(st_union("the_geom"))).geom from "Areas";
It took 168 sec

Then I tried this:
CREATE TABLE public." Areas_union " AS select  (ST_dump(ST_Buffer(St_Collect(the_geom), 0))).geom as "the_geom"  from " Areas";
And it took 46 sec.
Is there any problems doing so, the result looked ok?
Kind regards,
Paul


Från: Malm, Paul (Operations AIM)
Skickat: den 3 mars 2020 07:29
Till: PostGIS Users Discussion
Ämne: SV: [postgis-users] st_union

Thanks Paul and Alexande!
/Paul

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Alexandre Neto
Skickat: den 3 mars 2020 01:53
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] st_union

Uhmmm... So, if there's no join or where clause, the index is not used at all?

Somehow, I thought that internally ST_union would still use it to decide which geometries need be merged together in the same part.

Thanks for clarifying.

Alexandre Neto

A segunda, 2/03/2020, 22:02, Paul Ramsey <pramsey at cleverelephant.ca<mailto:pramsey at cleverelephant.ca>> escreveu:
On Mon, Mar 2, 2020 at 1:16 PM Alexandre Neto <senhor.neto at gmail.com<mailto:senhor.neto at gmail.com>> wrote:
>
> Stating the obvious,
>
> Do you have a spatial index on your geom column?

That will make no difference, the query is reading the whole contents
of the column, a spatial index will do nothing.

P


>
> Alexandre Neto
>
> A segunda, 2/03/2020, 17:04, Paul Ramsey <pramsey at cleverelephant.ca<mailto:pramsey at cleverelephant.ca>> escreveu:
>>
>> No, if it’s returning the answer you want, that’s the way to do it, and there’s no way around it.
>>
>> > On Mar 2, 2020, at 6:25 AM, <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> wrote:
>> >
>> > Hi list,
>> > I would like to do this:
>> > CREATE TABLE public."Areas_union" AS select  (st_dump(st_union("the_geom"))).geom from "Areas";
>> >
>> > But it takes such a long time to execute, is there a smarter way to do it?
>> >
>> > Kind regards,
>> > Paul
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
>> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200303/78a5f3dc/attachment.html>