I'm working on an old table where polygons are stored as points. Now, I want to migrate these points to PostGIS and store them as polygons (or multi-polygons). The table structure is as follows: loc_id; loc_shape_id; geom;

Here the points for loc_id 29930:

points for one loc_id

If I now apply a simple ST_MakeLine like the following:

st_makeline(geom) as line, loc_id
from (select * from test_regionen_11 
order by loc_id, loc_shape_id ) 
as ordered_points
where loc_id = 29930
group by loc_id

I get this messed up linestring:

messed up linestring

Well, does anyone has an idea how I could create multi-linestrings that represent the island in this case?

There is no column or value to distinguish them.

  • 2
    Without an "island id" of some sort, it's going to be very hard to make a general purpose solution. Some points will cluster nicely into islands, in other cases, islands near to each other will be very difficult to distinguish using proximity alone. Why are these points so important? Coastline files are pretty easy to find in the public domain, why not use them? You could use an external source of "this is an island" to group the points into islands and i.d. them that way. Nov 28, 2013 at 17:22
  • Thanks. It was really hard work (I followed your advice of getting some "island ids"), had to snap the points etc. Well I managed to "rescue" most of the polygons but some are such a mess that it won't be possible to re-modell them. Thanks for your help!
    – hoge6b01
    Dec 11, 2013 at 8:35
  • Is it only the matter of the few objects in your question, or is your data more extensive? As Paul said it would be very hard to create the polygons, using points that are concentrated in an area, because there are islands that are very close to each other.
    – Stefan
    Dec 29, 2013 at 8:20
  • Maybe it is easier to digitize the few islands in GIS. The other question is about the accuracy of your polygons, when you could create them from the points. In my opinion the few points are not really representing the shape of your island. My advices: digitize the polygons by your one (small data), or get the coastline data (as Paul said).
    – Stefan
    Dec 29, 2013 at 8:27

3 Answers 3


You can try kmeans-postgresql. For the installation you can follow this instructions.

Kmeans is clustering point data, with a predefined so-called K integer (number 5 in the query).

SELECT kmeans(ARRAY[ST_X(geom), ST_Y(geom)], 5)

Nevertheless it would be difficult to get correct polygons, because of the irregular distribution of your points.

Maybe you can combine the points and assign them a gid or something.


Would it be possible to define polygons containing islands? I've tried doing it that way and it looks like this:

WITH points as (
SELECT points.the_geom FROM points, polygons WHERE ST_Within(points.the_geom, polygons.the_geom) AND polygons.gid = 1)

select st_makeline(points.the_geom) into line FROM points;

You select all points within the polygon with gid = 1 first, then you make line out of them. However, this line is not closed.

  • Well, as written above, I had to try to get some "island id" group on.
    – hoge6b01
    Dec 11, 2013 at 8:50

You can not magically create islands out of the points you have. Any algorithm will struggle, as even if you can work out a way to automatically separate the points into distinct islands, you wont know what order to join the points in.

Unfortunately, I suggest that you manually create the islands. You can do this in QGIS with snapping to your points, so you'll use the right coordinates. But doing it automatically will be futile, I predict.

Edit: actually, if you're original dataset was sensible, then the points would be in the right order, so maybe you can make a polyline or polygon from them. Did you try grouping by loc_id and also loc_shape? Just change the last line to:

group by loc_id, loc_shape_id

also, to close off the line, you could get the top 1 point and union all it onto the end of your line, so that the line closes, maybe.

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.