Create multi-line from point in PostGIS

Question

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:

If I now apply a simple ST_MakeLine like the following:

select
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:

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.