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.

1 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.]

  • At the moment I don't have the order of the segments for each polygon although I could add that information to the join table. I will do some experimenting with this. The reason I did not specify things too much is that, at this stage I am still exploring. I an a very experience IT person but a GIS novice and I am wary of simply doing things that way I know when there are much better ways that I don't know about. I like to think that I know enough to at least have some idea about what I don't know! Jan 1, 2014 at 10:15
  • Warning: If there is no order and no fixed limit to the gaps, then you're in the land of heuristics.
    – BradHards
    Jan 1, 2014 at 10:17
  • Understood -- one solution I thought of was to write a python plugin that would take a layer of line features and join them up producing either a polygon or a single line using best fit heuristics -- one should be able to formulate it as a least square fit (i.e. linear regression) problem. Jan 1, 2014 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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