1

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.

Desired output

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?

|improve this question
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.

|improve this answer
  • Good start but doesn't work unfortunately because I'm using multilineString. It only works with lineString or with connected lineString (with combination of st_linemerge and st_makeline). – Roman Jul 6 '19 at 14:28
  • Maybe try to split it using ST_Dump into linestrings, and distribute points among lines proportionally to their lengths. But it is getting complicated. – Michael Jul 6 '19 at 17:48
  • I finally used ST_GENERATEPOINTS(ST_BUFFER(ST_SetSRID(<Section.geom Column>, 4326)::geography, 10)::geometry, 1). It doesn't distribute proportionally, but it ditribute randomly in a perimeter of 10 meters from my section. Normally, I won't have many observations points on the exact same position (unless there are too much and I'm not lucky). Thank you for your time and answers. And I valide your answer as I think it can help others people as long as they don't use MultilineString :-) – Roman Jul 7 '19 at 9:22
0

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)')
);
|improve this answer
  • I'm not sure I understood. Yes, my observations are point geometry. But with ClosestPoint, I'll have the opposite about what I need no ? Actually, I'd like to find the Furthest localisation (too bad, function doesn't exist) on my multilineString from the others points. – Roman Jul 4 '19 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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