ST_Union - CockroachDB

Given a set of shapes (e.g., from a ), ST_Union combines that set of shapes into a single shape. The resulting shape can then be passed to functions that operate on a single shape, such as . ST_Union works on the following data types:

The non-aggregate version of ST_Union is not yet implemented.

Unlike ST_Collect, which does not change the shapes it operates on and merely gathers them into a collection, ST_Union modifies the shapes it operates on, merging them together.

Examples

In this example, we will generate a single geometry from many individual points using ST_Union.

Create a temporary table to hold all the points, which will be in format:

CREATE TABLE tmp (ID UUID DEFAULT gen_random_uuid(), geom_text STRING);

Insert the points with the following statement:

INSERT INTO tmp (geom_text) VALUES
('POINT (-73.962090000000003 40.609226)'),
('POINT (-74.007309000000006 40.619892)'),
('POINT (-73.949724000000003 41.498103999999998)'),
('POINT (-72.390808000000007 40.961891999999999)'),
('POINT (-77.937476000000004 43.218451000000002)'),
('POINT (-73.842978000000002 40.944997999999998)'),
('POINT (-73.928291999999999 40.680906999999998)'),
('POINT (-73.914484999999999 40.775554)'),
('POINT (-73.993281999999994 40.722833000000001)'),
('POINT (-73.900373999999999 41.691662000000001)'),
('POINT (-73.787897999999998 42.250467)'),
('POINT (-75.062138000000004 42.454003)'),
('POINT (-73.992576 40.753692000000001)'),
('POINT (-75.647559999999999 44.590586000000002)'),
('POINT (-73.698974000000007 40.832500000000003)'),
('POINT (-73.957140999999993 40.728864000000002)'),
('POINT (-73.954773000000003 40.732667999999997)'),
('POINT (-74.024456000000001 41.521116999999997)'),
('POINT (-73.785302000000001 43.081800000000001)'),
('POINT (-74.860436000000007 43.040708000000002)'),
('POINT (-74.001165999999998 40.687367000000002)'),
('POINT (-78.809790000000007 42.845362000000002)'),
('POINT (-75.061370999999994 41.608192000000003)'),
('POINT (-76.499137000000005 42.440623000000002)'),
('POINT (-73.379208000000006 43.027712000000001)'),
('POINT (-73.684466999999998 41.259498000000001)'),
('POINT (-74.003300999999993 40.706865999999998)'),
('POINT (-76.384369000000007 43.034204000000003)'),
('POINT (-73.983408999999995 40.723255000000002)'),
('POINT (-74.670794000000001 42.371471999999997)'),
('POINT (-73.963970000000003 40.804766999999998)'),
('POINT (-73.989228999999995 40.743865)'),
('POINT (-73.487143000000003 42.587166000000003)'),
('POINT (-78.608046999999999 42.767758999999998)'),
('POINT (-78.824476000000004 42.754263999999999)'),
('POINT (-72.185676999999998 40.963121999999998)'),
('POINT (-78.639556999999996 43.001435000000001)'),
('POINT (-77.428916999999998 43.212463)'),
('POINT (-73.964771999999996 40.770071999999999)'),
('POINT (-73.937073999999996 40.578499000000001)'),
('POINT (-74.076237000000006 40.636749000000002)'),
('POINT (-73.923480999999995 41.091296)'),
('POINT (-78.00206 42.718395000000001)'),
('POINT (-74.992647000000005 43.018610000000002)'),
('POINT (-73.991900000000001 40.683962000000001)'),
('POINT (-74.119761999999994 42.040447)'),
('POINT (-73.955706000000006 40.785203000000003)'),
('POINT (-73.698363000000001 42.709778)'),
('POINT (-75.296575000000004 43.098322000000003)'),
('POINT (-73.804747000000006 42.709560000000003)'),
('POINT (-79.326158000000007 42.102457000000001)'),
('POINT (-73.938518000000002 40.837735000000002)'),
('POINT (-73.976410999999999 40.672561999999999)'),
('POINT (-73.978993000000003 40.745123999999997)'),
('POINT (-77.587306999999996 43.159300999999999)'),
('POINT (-74.001251999999994 40.734977000000001)'),
('POINT (-76.206723999999994 43.102012000000002)'),
('POINT (-78.876722000000001 42.922820999999999)'),
('POINT (-73.495369999999994 44.703318000000003)'),
('POINT (-79.052965999999998 43.105055)'),
('POINT (-73.551120999999995 41.189729)'),
('POINT (-73.760520999999997 40.809649999999998)'),
('POINT (-73.952360999999996 42.077744000000003)'),
('POINT (-76.511525000000006 43.456606000000001)'),
('POINT (-75.036815000000004 42.448793000000002)'),
('POINT (-75.544407000000007 42.826815000000003)'),
('POINT (-73.686974000000006 40.724902)'),
('POINT (-77.582922999999994 43.154963000000002)'),
('POINT (-78.836257000000003 42.960456999999998)'),
('POINT (-73.919056999999995 41.091124999999998)'),
('POINT (-73.998487999999995 40.733795999999998)'),
('POINT (-73.939606999999995 40.815913000000002)'),
('POINT (-74.058107000000007 41.083153000000003)'),
('POINT (-73.991060000000004 40.733919)'),
('POINT (-73.967905000000002 40.758032999999998)'),
('POINT (-73.875281000000001 41.016382999999998)'),
('POINT (-77.134634000000005 42.961303000000001)'),
('POINT (-78.885418999999999 42.906629000000002)'),
('POINT (-73.553145999999998 40.665826000000003)'),
('POINT (-73.834867000000003 42.621578999999997)'),
('POINT (-73.982307000000006 40.752575)'),
('POINT (-77.816855000000004 42.795613000000003)'),
('POINT (-73.962288000000001 40.671320999999999)'),
('POINT (-72.354918999999995 41.083874999999999)'),
('POINT (-73.965331000000006 40.806128999999999)'),
('POINT (-74.080895999999996 41.844256000000001)'),
('POINT (-73.942249000000004 40.812187000000002)'),
('POINT (-73.974255999999997 40.783757999999999)'),
('POINT (-73.990206999999998 40.754969000000003)'),
('POINT (-73.601747000000003 41.562685000000002)'),
('POINT (-74.117880999999997 42.040733000000003)'),
('POINT (-73.979851999999994 40.664327999999998)'),
('POINT (-73.960042999999999 40.657589000000002)'),
('POINT (-74.009314000000003 40.715324000000003)'),
('POINT (-73.903509 40.703226000000001)'),
('POINT (-74.030330000000006 40.624713999999997)'),
('POINT (-73.909406000000004 40.814979000000001)'),
('POINT (-73.903160999999997 40.744929999999997)'),
('POINT (-73.504622999999995 41.952860000000001)'),
('POINT (-73.865554000000003 42.219304000000001)'),
('POINT (-73.981960000000001 40.766441)'),
('POINT (-73.913732999999993 40.698230000000002)'),
('POINT (-73.596703000000005 40.874966000000001)'),
('POINT (-73.974082999999993 40.686098000000001)'),
('POINT (-73.996341999999999 40.725087000000002)'),
('POINT (-73.595579999999998 42.363210000000002)'),
('POINT (-73.952259999999995 40.674118999999997)'),
('POINT (-73.993341000000001 40.730010999999998)'),
('POINT (-73.989086999999998 40.721902)'),
('POINT (-73.955855999999997 40.655028000000001)'),
('POINT (-74.002930000000006 40.731848999999997)'),
('POINT (-76.086753000000002 44.242874)'),
('POINT (-73.839067 42.681877999999998)'),
('POINT (-74.020105000000001 41.935105)'),
('POINT (-73.980250999999996 44.279376999999997)'),
('POINT (-74.085936000000004 41.747742000000002)'),
('POINT (-77.227361999999999 43.063468)'),
('POINT (-74.093547000000001 40.924697999999999)'),
('POINT (-74.986999999999995 44.669899999999998)'),
('POINT (-73.793597000000005 41.135866)'),
('POINT (-78.889482000000001 42.924545000000002)'),
('POINT (-73.914946 41.936036999999999)'),
('POINT (-73.9071 41.721612999999998)'),
('POINT (-73.993579999999994 40.736569000000003)'),
('POINT (-73.900388000000007 41.691189000000001)'),
('POINT (-73.989261999999997 40.663339000000001)'),
('POINT (-73.918380999999997 41.090746000000003)'),
('POINT (-73.862252999999995 40.839613)'),
('POINT (-73.806933000000001 40.989787999999997)'),
('POINT (-73.635006000000004 40.744909)'),
('POINT (-73.794276999999994 41.008797000000001)'),
('POINT (-73.759060000000005 41.022123000000001)'),
('POINT (-73.711965000000006 40.968676000000002)'),
('POINT (-74.668248000000006 42.372920000000001)'),
('POINT (-73.992476999999994 40.739109999999997)'),
('POINT (-74.131673000000006 44.326892000000001)'),
('POINT (-73.974738000000002 40.732664)'),
('POINT (-73.812349999999995 40.798867000000001)'),
('POINT (-78.641949999999994 42.991985)'),
('POINT (-73.962226999999999 40.778944000000003)'),
('POINT (-74.374174999999994 43.006495000000001)'),
('POINT (-77.095714000000001 43.041158000000003)'),
('POINT (-73.425764000000001 40.871898000000002)'),
('POINT (-73.466729000000001 44.708036)'),
('POINT (-73.978020999999998 40.686546999999997)'),
('POINT (-73.974082999999993 40.686098000000001)'),
('POINT (-73.971849000000006 40.602485000000001)'),
('POINT (-73.983153999999999 40.690553000000001)'),
('POINT (-77.052417000000005 42.143089000000003)'),
('POINT (-73.776431000000002 40.934483)'),
('POINT (-74.002019000000004 40.730927999999999)'),
('POINT (-73.772469999999998 42.264814000000001)'),
('POINT (-73.772876999999994 41.158641000000003)'),
('POINT (-72.296062000000006 41.002409)'),
('POINT (-74.021124999999998 41.933720000000001)'),
('POINT (-74.058107000000007 41.083153000000003)'),
('POINT (-73.967922999999999 40.801301000000002)'),
('POINT (-72.294278000000006 41.000610999999999)'),
('POINT (-76.499340000000004 42.438741999999998)'),
('POINT (-73.976597999999996 40.786610000000003)'),
('POINT (-73.988552999999996 40.703600000000002)'),
('POINT (-74.004855000000006 40.741959000000001)'),
('POINT (-73.844444999999993 40.925654999999999)'),
('POINT (-74.005101999999994 40.746654999999997)'),
('POINT (-73.937281999999996 42.819218999999997)'),
('POINT (-74.001677000000001 40.736243000000002)'),
('POINT (-73.684112999999996 40.981757000000002)'),
('POINT (-73.919064000000006 40.832836999999998)'),
('POINT (-78.885268999999994 42.944516)'),
('POINT (-72.299519000000004 40.994070999999998)'),
('POINT (-73.833477999999999 40.940247999999997)'),
('POINT (-73.695717000000002 41.783996999999999)'),
('POINT (-73.926891999999995 40.807422000000003)'),
('POINT (-74.085899999999995 41.747892999999998)'),
('POINT (-73.957583 41.417974999999998)');

Run the query below, which gathers the points into a single geometry using ST_Union, and converts the geometry to so that we can view it with geojson.io:

WITH
    the_geoms_table
        AS (
            SELECT
                st_union(st_geomfromtext(geom_text)) AS the_union
            FROM
                tmp
        )
SELECT
    st_asgeojson(the_union)
FROM
    the_geoms_table;

{"type":"MultiPoint","coordinates":[[-79.326158,42.102457],[-79.052966,43.105055],[-78.889482,42.924545],[-78.885419,42.906629],[-78.885269,42.944516],[-78.876722,42.922821],[-78.836257,42.960457],[-78.824476,42.754264],[-78.80979,42.845362],[-78.64195,42.991985],[-78.639557,43.001435],[-78.608047,42.767759],[-78.00206,42.718395],[-77.937476,43.218451],[-77.816855,42.795613],[-77.587307,43.159301],[-77.582923,43.154963],[-77.428917,43.212463],[-77.227362,43.063468],[-77.134634,42.961303],[-77.095714,43.041158],[-77.052417,42.143089],[-76.511525,43.456606],[-76.49934,42.438742],[-76.499137,42.440623],[-76.384369,43.034204],[-76.206724,43.102012],[-76.086753,44.242874],[-75.64756,44.590586],[-75.544407,42.826815],[-75.296575,43.098322],[-75.062138,42.454003],[-75.061371,41.608192],[-75.036815,42.448793],[-74.992647,43.01861],[-74.987,44.6699],[-74.860436,43.040708],[-74.670794,42.371472],[-74.668248,42.37292],[-74.374175,43.006495],[-74.131673,44.326892],[-74.119762,42.040447],[-74.117881,42.040733],[-74.093547,40.924698],[-74.085936,41.747742],[-74.0859,41.747893],[-74.080896,41.844256],[-74.076237,40.636749],[-74.058107,41.083153],[-74.03033,40.624714],[-74.024456,41.521117],[-74.021125,41.93372],[-74.020105,41.935105],[-74.009314,40.715324],[-74.007309,40.619892],[-74.005102,40.746655],[-74.004855,40.741959],[-74.003301,40.706866],[-74.00293,40.731849],[-74.002019,40.730928],[-74.001677,40.736243],[-74.001252,40.734977],[-74.001166,40.687367],[-73.998488,40.733796],[-73.996342,40.725087],[-73.99358,40.736569],[-73.993341,40.730011],[-73.993282,40.722833],[-73.992576,40.753692],[-73.992477,40.73911],[-73.9919,40.683962],[-73.99106,40.733919],[-73.990207,40.754969],[-73.989262,40.663339],[-73.989229,40.743865],[-73.989087,40.721902],[-73.988553,40.7036],[-73.983409,40.723255],[-73.983154,40.690553],[-73.982307,40.752575],[-73.98196,40.766441],[-73.980251,44.279377],[-73.979852,40.664328],[-73.978993,40.745124],[-73.978021,40.686547],[-73.976598,40.78661],[-73.976411,40.672562],[-73.974738,40.732664],[-73.974256,40.783758],[-73.974083,40.686098],[-73.971849,40.602485],[-73.967923,40.801301],[-73.967905,40.758033],[-73.965331,40.806129],[-73.964772,40.770072],[-73.96397,40.804767],[-73.962288,40.671321],[-73.962227,40.778944],[-73.96209,40.609226],[-73.960043,40.657589],[-73.957583,41.417975],[-73.957141,40.728864],[-73.955856,40.655028],[-73.955706,40.785203],[-73.954773,40.732668],[-73.952361,42.077744],[-73.95226,40.674119],[-73.949724,41.498104],[-73.942249,40.812187],[-73.939607,40.815913],[-73.938518,40.837735],[-73.937282,42.819219],[-73.937074,40.578499],[-73.928292,40.680907],[-73.926892,40.807422],[-73.923481,41.091296],[-73.919064,40.832837],[-73.919057,41.091125],[-73.918381,41.090746],[-73.914946,41.936037],[-73.914485,40.775554],[-73.913733,40.69823],[-73.909406,40.814979],[-73.9071,41.721613],[-73.903509,40.703226],[-73.903161,40.74493],[-73.900388,41.691189],[-73.900374,41.691662],[-73.875281,41.016383],[-73.865554,42.219304],[-73.862253,40.839613],[-73.844445,40.925655],[-73.842978,40.944998],[-73.839067,42.681878],[-73.834867,42.621579],[-73.833478,40.940248],[-73.81235,40.798867],[-73.806933,40.989788],[-73.804747,42.70956],[-73.794277,41.008797],[-73.793597,41.135866],[-73.787898,42.250467],[-73.785302,43.0818],[-73.776431,40.934483],[-73.772877,41.158641],[-73.77247,42.264814],[-73.760521,40.80965],[-73.75906,41.022123],[-73.711965,40.968676],[-73.698974,40.8325],[-73.698363,42.709778],[-73.695717,41.783997],[-73.686974,40.724902],[-73.684467,41.259498],[-73.684113,40.981757],[-73.635006,40.744909],[-73.601747,41.562685],[-73.596703,40.874966],[-73.59558,42.36321],[-73.553146,40.665826],[-73.551121,41.189729],[-73.504623,41.95286],[-73.49537,44.703318],[-73.487143,42.587166],[-73.466729,44.708036],[-73.425764,40.871898],[-73.379208,43.027712],[-72.390808,40.961892],[-72.354919,41.083875],[-72.299519,40.994071],[-72.296062,41.002409],[-72.294278,41.000611],[-72.185677,40.963122]]}

Paste the JSON emitted in the previous step into geojson.io and you should see an image like the following, which shows the location of most of the independent bookstores in New York State:
Finally, drop the temporary table if you no longer need it:
DROP TABLE tmp;

​Examples

​See also

Examples

See also