PostGIS equivalent of ArcMap Union
1
What is the equivalent in PostGIS / PostgreSQL of the "Union" operation in ArcMap?
Say you have two shapefiles with two features each. (PostGIS equivalent: two tables with two rows with polygon geometries)
then the result would be 1 shapefile with 7 features. (PostGIS equivalent: Table with 7 rows with geometries)
I've looked at ST_Intersect, ST_Union and ST_Collect but can't find the right combination. Your help is much appreciated.
postgresql union postgis arcmap
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
add a comment |
1
What is the equivalent in PostGIS / PostgreSQL of the "Union" operation in ArcMap?
Say you have two shapefiles with two features each. (PostGIS equivalent: two tables with two rows with polygon geometries)
then the result would be 1 shapefile with 7 features. (PostGIS equivalent: Table with 7 rows with geometries)
I've looked at ST_Intersect, ST_Union and ST_Collect but can't find the right combination. Your help is much appreciated.
postgresql union postgis arcmap
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56
add a comment |
1
1
1
What is the equivalent in PostGIS / PostgreSQL of the "Union" operation in ArcMap?
Say you have two shapefiles with two features each. (PostGIS equivalent: two tables with two rows with polygon geometries)
then the result would be 1 shapefile with 7 features. (PostGIS equivalent: Table with 7 rows with geometries)
I've looked at ST_Intersect, ST_Union and ST_Collect but can't find the right combination. Your help is much appreciated.
postgresql union postgis arcmap
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
What is the equivalent in PostGIS / PostgreSQL of the "Union" operation in ArcMap?
Say you have two shapefiles with two features each. (PostGIS equivalent: two tables with two rows with polygon geometries)
then the result would be 1 shapefile with 7 features. (PostGIS equivalent: Table with 7 rows with geometries)
I've looked at ST_Intersect, ST_Union and ST_Collect but can't find the right combination. Your help is much appreciated.
postgresql union postgis arcmap
postgresql union postgis arcmap
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
296k46451546
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
asked Nov 13 '18 at 14:54
Rutger HofsteRutger Hofste
878816
878816
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56
add a comment |
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56
add a comment |
2 Answers
2
active
oldest
votes
1
Here is a working query based on this answer from gis.stackexchange:
Read it from a) to d):
-- d) Extract the path number and the geom from the geometry dump
SELECT
(dump).path[1] id,
(dump).geom
FROM
(
-- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
-- Dump them to return individual geometries
SELECT
ST_Dump(ST_Polygonize(geom)) dump
FROM
(
-- b) Union all rings in one big geometry
SELECT
ST_Union(geom) geom
FROM
(
-- a) First get the exterior ring from all geoms
SELECT
ST_ExteriorRing(geom) geom
FROM
rectangles
) a
) b
) c
Result:
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
Note thatST_ExteriorRingdrops any holes.ST_Boundarywill preserve the interior rings, but it will also create a polygon inside them.
– jpmc26
Jan 18 at 18:41
add a comment |
0
Many thanks to Michael Entin
-- input data
with polys1 AS (
SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
UNION ALL
SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
UNION ALL
SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283714%2fpostgis-equivalent-of-arcmap-union%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Here is a working query based on this answer from gis.stackexchange:
Read it from a) to d):
-- d) Extract the path number and the geom from the geometry dump
SELECT
(dump).path[1] id,
(dump).geom
FROM
(
-- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
-- Dump them to return individual geometries
SELECT
ST_Dump(ST_Polygonize(geom)) dump
FROM
(
-- b) Union all rings in one big geometry
SELECT
ST_Union(geom) geom
FROM
(
-- a) First get the exterior ring from all geoms
SELECT
ST_ExteriorRing(geom) geom
FROM
rectangles
) a
) b
) c
Result:
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
Note thatST_ExteriorRingdrops any holes.ST_Boundarywill preserve the interior rings, but it will also create a polygon inside them.
– jpmc26
Jan 18 at 18:41
add a comment |
1
Here is a working query based on this answer from gis.stackexchange:
Read it from a) to d):
-- d) Extract the path number and the geom from the geometry dump
SELECT
(dump).path[1] id,
(dump).geom
FROM
(
-- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
-- Dump them to return individual geometries
SELECT
ST_Dump(ST_Polygonize(geom)) dump
FROM
(
-- b) Union all rings in one big geometry
SELECT
ST_Union(geom) geom
FROM
(
-- a) First get the exterior ring from all geoms
SELECT
ST_ExteriorRing(geom) geom
FROM
rectangles
) a
) b
) c
Result:
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
Note thatST_ExteriorRingdrops any holes.ST_Boundarywill preserve the interior rings, but it will also create a polygon inside them.
– jpmc26
Jan 18 at 18:41
add a comment |
1
1
1
Here is a working query based on this answer from gis.stackexchange:
Read it from a) to d):
-- d) Extract the path number and the geom from the geometry dump
SELECT
(dump).path[1] id,
(dump).geom
FROM
(
-- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
-- Dump them to return individual geometries
SELECT
ST_Dump(ST_Polygonize(geom)) dump
FROM
(
-- b) Union all rings in one big geometry
SELECT
ST_Union(geom) geom
FROM
(
-- a) First get the exterior ring from all geoms
SELECT
ST_ExteriorRing(geom) geom
FROM
rectangles
) a
) b
) c
Result:
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
Here is a working query based on this answer from gis.stackexchange:
Read it from a) to d):
-- d) Extract the path number and the geom from the geometry dump
SELECT
(dump).path[1] id,
(dump).geom
FROM
(
-- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
-- Dump them to return individual geometries
SELECT
ST_Dump(ST_Polygonize(geom)) dump
FROM
(
-- b) Union all rings in one big geometry
SELECT
ST_Union(geom) geom
FROM
(
-- a) First get the exterior ring from all geoms
SELECT
ST_ExteriorRing(geom) geom
FROM
rectangles
) a
) b
) c
Result:
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
answered Nov 13 '18 at 22:05
thibautgthibautg
1,0391510
1,0391510
Note thatST_ExteriorRingdrops any holes.ST_Boundarywill preserve the interior rings, but it will also create a polygon inside them.
– jpmc26
Jan 18 at 18:41
add a comment |
Note thatST_ExteriorRingdrops any holes.ST_Boundarywill preserve the interior rings, but it will also create a polygon inside them.
– jpmc26
Jan 18 at 18:41
Note that
ST_ExteriorRing drops any holes. ST_Boundary will preserve the interior rings, but it will also create a polygon inside them.– jpmc26
Jan 18 at 18:41
Note that
ST_ExteriorRing drops any holes. ST_Boundary will preserve the interior rings, but it will also create a polygon inside them.– jpmc26
Jan 18 at 18:41
add a comment |
0
Many thanks to Michael Entin
-- input data
with polys1 AS (
SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
UNION ALL
SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
UNION ALL
SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
add a comment |
0
Many thanks to Michael Entin
-- input data
with polys1 AS (
SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
UNION ALL
SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
UNION ALL
SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
add a comment |
0
0
0
Many thanks to Michael Entin
-- input data
with polys1 AS (
SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
UNION ALL
SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
UNION ALL
SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
Many thanks to Michael Entin
-- input data
with polys1 AS (
SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
UNION ALL
SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
UNION ALL
SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
UNION ALL
SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
answered Nov 15 '18 at 11:45
Rutger HofsteRutger Hofste
878816
878816
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283714%2fpostgis-equivalent-of-arcmap-union%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have a look at this answer on gis.se: gis.stackexchange.com/questions/83/…
– thibautg
Nov 13 '18 at 21:56