Finding new point, with proportion, between two points Postgis

Question

I couldn't find a topic related to my problem, thus I am going to ask. I have the following problem:

I need to create a new Point based on three variables: P1 (Geometry), P2 (Geometry) and length (Integer in meters). I know the geometry of two points and want to crate a new point the lies between them given a determined length, like in the picture below:

How can I do it using only Postgis?

I am using Postgis 2.2 with PostgreSQL 9.4.

Answer 1

One potential solution is to combine 3 things:

• Use ST_MakeLine to make a line between your 2 points. (http://www.postgis.net/docs/ST_MakeLine.html)
• Compute what portion of the total length your new line will have. The total length of the line can be found using ST_Length. The ratio must be a ratio (between 0 and 1)
• Use ST_line_interpolate_point(linestring, location) to find your intermediate point (http://postgis.net/docs/manual-1.3/ch06.html#id441334)

Good luck

Answer 2

You can use st_project function. A sample is as follows. To find a point at a distance of 100 meters between points '77.0541494 28.5091006' and '77.054473971709 28.5095594' from the first point.

select st_astext(st_project('POINT(77.0541494 28.5091006)'::geography, 100, st_azimuth(st_geomfromtext('POINT(77.0541494 28.5091006)'), st_geomfromtext('POINT(77.054473971709 28.5095594)'))));

OUTPUT: POINT(77.0547393477234 28.5098372103161)

For reference:

1. https://postgis.net/docs/ST_Project.html
2. http://www.postgis.org/docs/ST_Azimuth.html