[GIS] Create multi-line from point in PostGIS

geometry-conversionlinestringpointpostgis

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:

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:

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.

Best Answer

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.

Related Question