CODEWITHSUNDEEP
javasqlpostgresqlpostgisopenstreetmap
Takhir Atamuratov
Takhir Atamuratov

Reputation: 159

How to get points (latitude and longitude) between two point (Point A and Point B) in PostGIS?

A am using PostGIS (OSM), and I don't have a solution how to get points with locations (latitude and longitude) between two point (Point A and Point B). I know about ST_Distance function, but this function does not return point with location, this function only calculate distance between points. How can I get points with locations?enter image description here

Upvotes: 0

Views: 530

Answers (1)

Jim Jones
Jim Jones

Reputation: 19653

Assuming that the points you want to retrieve are locations along a road (linestring) you can split the linestring between the two points and then select the points that overlap or are close to it.

Consider these points and linestring, respectively stored in tables points and line:

enter image description here

You can use the function ST_LineLocatePoint to get the exact location of a given point in the line, and with these locations you can cut the linestring into a subset using ST_LineSubstring. With this subset you can check which points intersect or are close to it. The following example retrieves the points along the line that are between POINT(-4.71 54.12) and POINT(-4.53 54.21) - second and previous last points:

SELECT points.geom
FROM line
JOIN points ON 
  ST_Intersects(
    points.geom,
    ST_LineSubstring(
     line.geom,
     ST_LineLocatePoint(line.geom,'SRID=4326;POINT(-4.71 54.12)'::geometry),
     ST_LineLocatePoint(line.geom,'SRID=4326;POINT(-4.53 54.21)'::geometry)))

enter image description here

Demo: db<>fiddle

Upvotes: 2

Related Questions