Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Totally brand new at using PostGIS, my first post here on GIS. Apologies if the answer to my question is obvious!

I've got regional data loaded into a PostGIS table and want to merge regions that share the same parent name. (and ultimately output the data as KML)

This is my query (I'm using php):

SELECT ST_AsTEXT(geom) FROM ST_UNION(ARRAY(SELECT geom FROM RegionsRaw WHERE (ParentName = '" . $parentName . "'))) AS geom;

When I run this script, I'm getting strange results. I can't post a picture because I'm a newbie so check it out here: http://i42.tinypic.com/2qtip8l.png

It's pretty obvious that certain areas are a little messed up and I have a hunch that it's because some of the polygons I'm merging are next to each other (where I'm seeing success) while others are islands and separated from the rest (getting weird results).

Looking for some guidance from the community on this, thanks!

share|improve this question
add comment

1 Answer

The most common reason to get results like this is that the original polygons are invalid. You can check with:

SELECT ST_IsValidReason(geom) from RegionsRaw where not ST_IsValid(geom);

If you get any rows in return you have bad polygons.

But you can try to fix them. In PostGIS 2.0 ther will be a function called ST_MakeValid which will try to fix the polygons.

But if you are not running some beta-release of 2.0 you can try with a buffer with 0 as argument.

Another note is that there is no reason to make an array before unioning. ST_Union also works as an aggregate function, so try something like:

SELECT ST_AsText(ST_Union(ST_Buffer(geom,0.0))) FROM
RegionRaw 
WHERE ParentName = '" . $parentName . "';

if you want the text output of the result.

Also note that if you want one unioned polygon for each "parentName" you can just group by parentName instead and get them all at once. One on each row

HTH
Nicklas

share|improve this answer
    
Hi Nicklas. Thanks for the reply. I ran the validation checker you had above on my RegionsRaw table and it returned zero rows - which I believe means everything checks out okay, right? I also modified my query using the suggestions you had above, but it gives me exactly the same result. Could I be using the KML output incorrectly? I'm just copying/pasting the st_askml coordinates directly into my custom-made kml file (for now - I will automate it once I get a everything working small-scale.) –  capikaw Mar 12 '12 at 20:41
    
Ok, would it be possible to share that part of the data set that is problematic? –  Nicklas Avén Mar 13 '12 at 8:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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