[postgis-users] Removing / Polygonising multiple overlapping lines

[Ben Madin]

Hmm, I'm not sure this is getting through, but for my own edification, this worked for islands, accepting that I can loose a small amount of accuracy on the polygon representations. However, it is unlikely to work for mainland land masses, as they will overlap their neighbours.

1. Pull out the offending record

create table rubbish (gid serial unique);
select addGeometryColumn('rubbish','the_geom',4326,'GEOMETRY',2);
insert into rubbish (the_geom) select st_exteriorring((st_dump(the_geom)).geom) as the_geom from tmp_admin where gid = 91;
create index rubbish_gist on rubbish using gist (the_geom);
VACUUM ANALYZE rubbish;

2. list the problems
select gid, st_geometrytype(the_geom), st_isvalid(the_geom), st_issimple(the_geom), st_isring(the_geom) from rubbish where st_issimple(the_geom) is false;

3. Get the outer extents of the linestrings.
update rubbish set the_geom = st_exteriorring(st_buffer(the_geom, 0.00000001)) where st_isring(the_geom) is false;

4. Turn all the linestrings into 1 polygon
insert into rubbish(the_geom) select st_multi(st_collect(st_makepolygon(the_geom))) from rubbish;

5. put it back into the original table.
update tmp_admin set the_geom = r.the_geom from (select the_geom from rubbish where gid = (select max(gid) from rubbish)) as r where gid = 91

So not a great outcome so far, but a small step in the right direction.

cheers

Ben




On 05/01/2010, at 17:31 , Ben Madin wrote:

> G'day all,
> 
> I have a problem with some administrative boundary data I have failing validity checks.
> 
> As an example, I had a polygon which kept reporting self-intersections. When I tried :
> 
> select st_exteriorring((st_dump(the_geom)).geom) as the_geom from tmp_admin where gid = 91;
> 
> So the one polygon returned 887 linestrings, which in themselves were valid, but :
> 
> prices=# select gid, st_geometrytype(the_geom), st_isvalid(the_geom), st_issimple(the_geom), st_isring(the_geom) from rubbish where st_isring(the_geom) is false;
> gid | st_geometrytype | st_isvalid | st_issimple | st_isring 
> -----+-----------------+------------+-------------+-----------
> 103 | ST_LineString   | t          | f           | f
> 197 | ST_LineString   | t          | f           | f
> 199 | ST_LineString   | t          | f           | f
> 313 | ST_LineString   | t          | f           | f
> 485 | ST_LineString   | t          | f           | f
> 489 | ST_LineString   | t          | f           | f
> 470 | ST_LineString   | t          | f           | f
> 474 | ST_LineString   | t          | f           | f
> 516 | ST_LineString   | t          | f           | f
> 887 | ST_LineString   | t          | f           | f
> (10 rows)
> 
> so for example 485 is in green and looks like (apologies if the picture is scrubbed). The blue island is OK 
> 
> <Screen shot 2010-01-05 at 5.24.40 PM.png>
> 
> It appears that the one line goes around the island three times, and this was the basis for the polygon. I'm a little stuck with 1.4.0 at the moment, so I can't use the st_lineCrossingDirection function, but what I really want is to produce a polygon which matches the outermost line of the object.
> 
> Any ideas... I'd rather do this in postgis as there are more of them...
> 
> cheers
> 
> Ben
> 
>