3

I have a problem with ST_UNION

So, I have a several polygons

enter image description here

DB looks like this

enter image description here

I need UNION all off those polygons to one multipolygon and get result that looks like this

enter image description here

I tried use ST_UNION but result was like that

SELECT 
   ST_ASTEXT(ST_UNION(coord)::geography)
from  sel_pai_coord

enter image description here

My tables :

-- Table: public.sel_pai_coord

-- DROP TABLE public.sel_pai_coord;

CREATE TABLE public.sel_pai_coord
(
    _id bigint NOT NULL DEFAULT nextval('pai_list__id_seq'::regclass),
    pai_id integer,
    coord geometry,
    CONSTRAINT pai_list_pkey PRIMARY KEY (_id)
)
WITH (
    OIDS = FALSE,
    FILLFACTOR = 60
)
TABLESPACE pg_default;

ALTER TABLE public.sel_pai_coord
    OWNER to postgres;

GRANT ALL ON TABLE public.sel_pai_coord TO postgres;

-- Index: pai_coord_g_gpx

-- DROP INDEX public.pai_coord_g_gpx;

CREATE INDEX pai_coord_g_gpx
    ON public.sel_pai_coord USING gist
    (geography(coord))
    TABLESPACE pg_default;

-- Index: pai_list__ID_idx

-- DROP INDEX public."pai_list__ID_idx";

CREATE INDEX "pai_list__ID_idx"
    ON public.sel_pai_coord USING btree
    (_id)
    TABLESPACE pg_default;

-- Index: pai_pai_id_idx

-- DROP INDEX public.pai_pai_id_idx;

CREATE INDEX pai_pai_id_idx
    ON public.sel_pai_coord USING btree
    (pai_id)
    TABLESPACE pg_default;

INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (1,ST_GeometryFromText('POLYGON((32.915735224676 48.667318607159,32.915891361936 48.666724803316,32.914539289227 48.666574277365,32.911074894272 48.66615153185,32.910527024907 48.666443043425,32.91019769701 48.666664976548,32.915735224676 48.667318607159))'),1);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (2,ST_GeometryFromText('POLYGON((32.915493660079 48.668237787695,32.915735224676 48.667318607159,32.91019769701 48.666664976548,32.910122085301 48.666715959724,32.909659470992 48.667091603507,32.909242852252 48.667499993439,32.915493660079 48.668237787695))'),2);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (3,ST_GeometryFromText('POLYGON((32.915271957921 48.669080920698,32.915493660079 48.668237787695,32.909242852252 48.667499993439,32.909219009909 48.667523283735,32.908866986452 48.667961386873,32.908650866381 48.66829937205,32.915271957921 48.669080920698))'),3);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (4,ST_GeometryFromText('POLYGON((32.913126672039 48.669810358406,32.915023521616 48.670025666711,32.915271957921 48.669080920698,32.908650866381 48.66829937205,32.908579542278 48.668410858988,32.908518148319 48.66857554911,32.908584967335 48.668679454027,32.908828336937 48.66875448492,32.909034640224 48.668830904837,32.909105913012 48.668914378955,32.90909436983 48.668981411408,32.908731907312 48.669137010962,32.908723403544 48.669142797361,32.913165551105 48.669667237818,32.913126672039 48.669810358406))'),4);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (5,ST_GeometryFromText('POLYGON((32.920582872417 48.672076683794,32.920827389679 48.671146327546,32.914979272018 48.670456119152,32.914734772246 48.671386462768,32.920582872417 48.672076683794))'),5);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (6,ST_GeometryFromText('POLYGON((32.920827389679 48.671146327546,32.921077308006 48.670195442151,32.915229148016 48.669505246862,32.915090390777 48.670033247048,32.916268615163 48.670167037968,32.916243830023 48.670273780126,32.915064850186 48.670130642581,32.914979272018 48.670456119152,32.920827389679 48.671146327546))'),6);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (7,ST_GeometryFromText('POLYGON((32.921077308006 48.670195442151,32.921321807536 48.669265084665,32.91547375223 48.668574901037,32.915229148016 48.669505246862,32.921077308006 48.670195442151))'),7);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (8,ST_GeometryFromText('POLYGON((32.921566311873 48.668334726458,32.921813556528 48.667393563435,32.920349812344 48.667220860867,32.915958210013 48.66673220346,32.915718361245 48.66764455449,32.921566311873 48.668334726458))'),8);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (9,ST_GeometryFromText('POLYGON((32.927321705618 48.669028539037,32.927572799247 48.668072692532,32.921880414509 48.667401409699,32.921629216189 48.668357244553,32.927321705618 48.669028539037))'),9);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (10,ST_GeometryFromText('POLYGON((32.927321705618 48.669028539037,32.927572799247 48.668072692532,32.921880414509 48.667401409699,32.921629216189 48.668357244553,32.927321705618 48.669028539037))'),10);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (11,ST_GeometryFromText('POLYGON((32.92681949061 48.670940230111,32.926956163263 48.670419867204,32.927070602739 48.669984384897,32.921384145047 48.66931380306,32.921132914754 48.670269636742,32.92681949061 48.670940230111))'),11);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (12,ST_GeometryFromText('POLYGON((32.926568491411 48.671896073698,32.926675570784 48.671488321518,32.92681949061 48.670940230111,32.921126791783 48.670268912321,32.920875701452 48.671224744151,32.926568491411 48.671896073698))'),12);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (13,ST_GeometryFromText('POLYGON((32.926317360781 48.672851917621,32.926394844402 48.672556767017,32.926568491411 48.671896073698,32.920875701452 48.671224744151,32.92062446611 48.67218057642,32.926317360781 48.672851917621))'),13);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (14,ST_GeometryFromText('POLYGON((32.931207152705 48.674225601767,32.926114242385 48.673625219609,32.92606635666 48.673807759808,32.925821250407 48.674740649585,32.926368150929 48.674800677445,32.928662152468 48.675051784759,32.929982429368 48.675121512835,32.930807088239 48.675191813291,32.930920191503 48.675158292191,32.930989766653 48.675053541108,32.931207152705 48.674225601767))'),14);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (15,ST_GeometryFromText('POLYGON((32.931207152705 48.674225601767,32.931487799334 48.673157136326,32.926394844402 48.672556767017,32.926317360781 48.672851917621,32.926114242385 48.673625219609,32.931207152705 48.674225601767))'),15);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (16,ST_GeometryFromText('POLYGON((32.931487799334 48.673157136326,32.931768285075 48.672088671287,32.926675570784 48.671488321518,32.926568491411 48.671896073698,32.926394844402 48.672556767017,32.931487799334 48.673157136326))'),16);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (17,ST_GeometryFromText('POLYGON((32.931768285075 48.672088671287,32.932009325403 48.671170890793,32.932050800889 48.671020476693,32.926956163263 48.670419867204,32.92681949061 48.670940230111,32.926675570784 48.671488321518,32.931768285075 48.672088671287))'),17);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (18,ST_GeometryFromText('POLYGON((32.932050800889 48.671020476693,32.932344605673 48.669955463176,32.931495735526 48.669855414974,32.927236344423 48.669353303761,32.927070602739 48.669984384897,32.926956163263 48.670419867204,32.932050800889 48.671020476693))'),18);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (19,ST_GeometryFromText('POLYGON((32.931495735526 48.669855414974,32.931823300432 48.668608630593,32.931763713537 48.668594733187,32.931146099629 48.668497946921,32.930090652539 48.668369522092,32.927572799247 48.668072692532,32.927321705618 48.669028539037,32.927236344423 48.669353303761,32.931495735526 48.669855414974))'),19);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (20,ST_GeometryFromText('POLYGON((32.91482510697 48.670780494547,32.914930963048 48.670378059765,32.908356605342 48.66960199519,32.908314555227 48.669657802406,32.908179716846 48.669689305416,32.908041528378 48.669793567311,32.90799954542 48.669974830487,32.91482510697 48.670780494547))'),20);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (21,ST_GeometryFromText('POLYGON((32.914930963048 48.670378059765,32.914998120299 48.67012251257,32.913093526415 48.669891321224,32.913126672039 48.669810358406,32.913165551105 48.669667237818,32.908723403544 48.669142797361,32.908608226902 48.669221168442,32.908518465302 48.66938712538,32.908356605342 48.66960199519,32.914930963048 48.670378059765))'),21);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (22,ST_GeometryFromText('POLYGON((32.932344605673 48.669955463176,32.932637484196 48.6688938824,32.932227998806 48.668702882526,32.931823300432 48.668608630593,32.931495735526 48.669855414974,32.932344605673 48.669955463176))'),22);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (23,ST_GeometryFromText('POLYGON((32.927070602739 48.669984384897,32.927196114527 48.66950645788,32.92150361364 48.668835166233,32.921378008614 48.66931307876,32.927070602739 48.669984384897))'),23);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (24,ST_GeometryFromText('POLYGON((32.927196114527 48.66950645788,32.927236344423 48.669353303761,32.927321705618 48.669028539037,32.921629216189 48.668357244553,32.92150361364 48.668835166233,32.927196114527 48.66950645788))'),24);
INSERT INTO "public"."pai_coord"("_id", "coord", "pai_id") VALUES (25,ST_GeometryFromText('POLYGON((32.911969252628 48.665848346027,32.912425604043 48.665889255856,32.912697717596 48.665946101607,32.914067703079 48.666132729519,32.915978609457 48.666406695549,32.916014641101 48.666247121965,32.915293649559 48.665772400265,32.914869229855 48.665237973359,32.913749591631 48.665282729695,32.913283709712 48.665337007128,32.913031635189 48.665391324223,32.912473914445 48.665604552088,32.911975635553 48.665814599979,32.911969252628 48.665848346027))'),25);
Improve this question
8
  • 1
    what does SELECT ST_IsValidReason(coord) FROM sel_pai_coord; say? my guess is some are invalid? if so, try with ST_MakeValid in the union, or update the column with it.
    – geozelot
    Commented Mar 20, 2019 at 10:25
  • said that all fields have Valid Geometry
    – Dsqwared
    Commented Mar 20, 2019 at 11:41
  • I tried to use ` SELECT (st_dump(st_union(a.coord))).geom as coord From sel_pai_coord;` and got little bit better result. st_dump union polygons to 5 different polygons [![enter image description here][1]][1] [1]: i.sstatic.net/dfFxL.png
    – Dsqwared
    Commented Mar 20, 2019 at 11:52
  • just tried your exact setup; no problems here. the unioned multipolygon is somewhat off of what you expect (some gaps etc.), but is produced correctly. what PG version are you on?
    – geozelot
    Commented Mar 20, 2019 at 11:53
  • psql (PostgreSQL) 9.2.24
    – Dsqwared
    Commented Mar 20, 2019 at 11:56

1 Answer 1

Reset to default
3

Playing with buffers will make your work with a small inaccuracy :-),

namely run the script,

create table pai_coord_un as SELECT ST_Multi(ST_Buffer( (ST_Dump( (ST_Union( ST_Buffer( (coord),0.0001125, 'join=mitre mitre_limit=5.0'))))).geom, -0.000112, 'join=mitre mitre_limit=5.0')) as geom from pai_coord see the result in the figure

enter image description here

and don't forget to bring your data to your SRID, I played with yours in geographic SC 4326......

Improve this answer
11
  • WOOOHOO I love you man.. THANK YOU VERY VERY MUCH!!!! Will try )
    – Dsqwared
    Commented Mar 20, 2019 at 18:51
  • all good, but inside should be empty space like in your screenshot, but i getting a little bit different result prntscr.com/n0n5lj
    – Dsqwared
    Commented Mar 20, 2019 at 19:07
  • I get it as in the my picture :-),
    – Cyril Mikhalchenko
    Commented Mar 20, 2019 at 19:09
  • Yeah, i don't have duplicate, sorry it was my mistake when I copied Inserts queries
    – Dsqwared
    Commented Mar 20, 2019 at 19:24
  • I a bit don't understand where i need to use SRID.. maybe that's why i have different result
    – Dsqwared
    Commented Mar 20, 2019 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.