How to automatically convert line features into polygons using QGIS/PostGIS?

Question

I have a master table of line features and a number of tables that I use to join (in the SQL sense) to produce various layers. Some of theses layers consist of boundaries of administrative zones (think counties ;) Using SQL I can either show the outline of all the zones or subsets of the zones.

Now I want to have a layer that consist of a set of polygons, one for each zone -- so I can use it with the atlas generator.

I would prefer a solution that can be automated, preferably in an sql query.

Some points:

• Currently the line segments end points don't match exactly (how would one get them to?)
• I know about ST_BuildArea but I think it expects the lines to form perfect closed polygons. When I try and use it I get back n empty polygons where n is the number of lines in the input.

What I am looking for is something like ST_BuildArea which will allow some tolerance in the line end points.

Another way of coping with this would be to have a method of standardising endpoints of lines given some tolerance Does such a thing exist? Before I go and start re-inventing the wheel. I figure it should be a fairly easy plugin to write.

Answer 1

If you're pretty confident that the line segments should join up, just add a small segment from the last point to a duplicate of the first point.

You can get the first point using ST_PointN:

SELECT ST_PointN(geom, 1) FROM myCounties;

You can add that point to the line using ST_MakeLine

SELECT ST_MakeLine(geom, (SELECT ST_PointN(geom, 1) FROM myCounties)) AS newgeom FROM myCounties;

When that works the way you want, use an UPDATE / INSERT for however you want to manage the new borders.

[Hint: this would have been more appropriate to your situation if you'd posted the table schema. Please try to ask specific questions about the specific problem that you are looking for an answer for.]