Hfrhyu

Does light intensity oscillate really fast since it is a wave?

Where to refill my bottle in India?

JSON.serialize: is it possible to suppress null values of a map?

Deadlock Graph and Interpretation, solution to avoid

Does it makes sense to buy a new cycle to learn riding?

Is bread bad for ducks?

How are circuits which use complex ICs normally simulated?

Can't find the latex code for the ⍎ (down tack jot) symbol

Why can Shazam do this?

Why isn't airport relocation done gradually?

"What time...?" or "At what time...?" - what is more grammatically correct?

Inflated grade on resume at previous job, might former employer tell new employer?

Where does the "burst of radiance" from Holy Weapon originate?

Poison Arrows Piercing damage reduced to 0, do you still get poisoned?

Re-submission of rejected manuscript without informing co-authors

Seven sins, seven Wizards, seven symbols, seven times repeated, but six super-heroes

What does "rabbited" mean/imply in this sentence?

Is domain driven design an anti-SQL pattern?

Did USCIS resume its biometric service for UK visa

How can I fix this gap between bookcases I made?

Why is it "Tumoren" and not "Tumore"?

aging parents with no investments

A poker game description that does not feel gimmicky

What does sndry explns mean in one of the Hitchhiker's guide books?

Merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

10

1

I have a postgres database with administrative boundaries and their geometry.
Each of those boundaries have an ident number.

What I want to achieve:

First I want to select all rows which are starting with a particular zip code.
For example:

SELECT * FROM "post" WHERE "post"."ident" LIKE '101%'

All of those Geometries which are adjacent should be merged and those which are not should also be returned.

I have tried those statements:

This one I have from this answer:

Joining lots of small polygons to form larger polygon using PostGIS?

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_UNION(geom) from t;

But this statement returns only those polygons which are adjacent but others are not included

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ((ST_Dump(ST_UNION(geom)))) from t;

I get 2 rows, and it seems that this could be what I want, but I cannot get the result als ST_AsText.

Is there a statement which returns all polygons merged where necessary and return it ideally as geoJSON or maybe as text?

postgresql polygon geojson merge

share|improve this question

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

add a comment | 

10

1

I have a postgres database with administrative boundaries and their geometry.
Each of those boundaries have an ident number.

What I want to achieve:

First I want to select all rows which are starting with a particular zip code.
For example:

SELECT * FROM "post" WHERE "post"."ident" LIKE '101%'

All of those Geometries which are adjacent should be merged and those which are not should also be returned.

I have tried those statements:

This one I have from this answer:

Joining lots of small polygons to form larger polygon using PostGIS?

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_UNION(geom) from t;

But this statement returns only those polygons which are adjacent but others are not included

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ((ST_Dump(ST_UNION(geom)))) from t;

I get 2 rows, and it seems that this could be what I want, but I cannot get the result als ST_AsText.

Is there a statement which returns all polygons merged where necessary and return it ideally as geoJSON or maybe as text?

postgresql polygon geojson merge

share|improve this question

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

add a comment | 

I have a postgres database with administrative boundaries and their geometry.
Each of those boundaries have an ident number.

What I want to achieve:

First I want to select all rows which are starting with a particular zip code.
For example:

SELECT * FROM "post" WHERE "post"."ident" LIKE '101%'

All of those Geometries which are adjacent should be merged and those which are not should also be returned.

I have tried those statements:

This one I have from this answer:

Joining lots of small polygons to form larger polygon using PostGIS?

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_UNION(geom) from t;

But this statement returns only those polygons which are adjacent but others are not included

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ((ST_Dump(ST_UNION(geom)))) from t;

I get 2 rows, and it seems that this could be what I want, but I cannot get the result als ST_AsText.

Is there a statement which returns all polygons merged where necessary and return it ideally as geoJSON or maybe as text?

postgresql polygon geojson merge

share|improve this question

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

SELECT * FROM "post" WHERE "post"."ident" LIKE '101%'

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_UNION(geom) from t;

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ((ST_Dump(ST_UNION(geom)))) from t;

share|improve this question

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

share|improve this question

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

edited Apr 4 at 14:24

Kadir Şahbaz

4,60221531

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

asked Aug 4 '14 at 8:16

NighthawkNighthawk

845

1 Answer
1

active

oldest

votes

0

OP's answer:

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

So you can access geometries and transform it into geoJSON. Adjacent geometries are merged and you get all other geometries which are also not included in merged polygon.

share|improve this answer

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f109271%2fmerge-adjacent-polygons-and-return-all-adjacent-and-not-adjacent-as-multipolyg%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

OP's answer:

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

So you can access geometries and transform it into geoJSON. Adjacent geometries are merged and you get all other geometries which are also not included in merged polygon.

share|improve this answer

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

add a comment | 

0

OP's answer:

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

So you can access geometries and transform it into geoJSON. Adjacent geometries are merged and you get all other geometries which are also not included in merged polygon.

share|improve this answer

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

add a comment | 

OP's answer:

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

So you can access geometries and transform it into geoJSON. Adjacent geometries are merged and you get all other geometries which are also not included in merged polygon.

share|improve this answer

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

share|improve this answer

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531

answered Apr 4 at 14:24

Kadir ŞahbazKadir Şahbaz

4,60221531