Create furthest point in multilineString

Question

I am very new to PostGIS. I have a table with sections (multilineString) drawed on a map and a table with observations (point).

Users can make observations and I have to automatically place the point on the section.

My first try was to use ST_Centroid to place the observations in the middle of the section and it technically works.

Unfortunately, it is not usable later. I print all observations on a map, and they all have same positions, so the top observation only is visible and clickable.

I would like to create the furthest new point from existings points that is still in the multilineString (So it will naturally distribute on the multilineString)

So for exemple:

• 1st point : Center
• 2nd and 3rd point : begin or end of multilineString (0% or 100% lenght of the section)
• 4th and 5th : 25% or 75% length of the section
• etc.

I don't need to find the 1st observation, only the followings.

I saw a lot of resources saying that we should use ST_Distance DESC but it's not working as my points are new and not already in the database.

Could someone help me, please?

Answer 1

I would try to use ST_Line_Interpolate_Point. Count number of points you need to place on a line, and generate them by ST_Line_Interpolate_Point with fraction equal to 0, 1/(n-1), 2/(n-1), ... 1.

Answer 2

I suppose your observations are point geometry. If that's the case you can use ST_ClosestPoint to find the closest point on line and plot them on line itself.

    SELECT ST_ClosestPoint(
    ST_GeomFromText('MULTILINESTRING ((10 10, 40 50), (20 20, 50 20, 50 60, 20 20))'),
    ST_GeomFromText('POINT(20 40)')
);