[GIS] How to select parallel lines in postgis

Question

I have roads(MultiLineString)table in postgresql database. The roads are classified based on priority attribute i.e.

  priority = 6 for motorway
  priority = 5 for highway
  priority = 4 for expressway
  priority = 3 for major road
  priority = 2 for minor road
  priority = 1 for street

i need to select all roads that are parallel to motorways(priority = 6)and are within 50 meters distance from either side.

From the above image, i want to select the green color roads which are parallel to blue color roads.

Update:
i tried using buffer but this also returns the line which is not parallel to the blue line. As shown in the image below, the lines perpendicular to blue lines are also selected as a result.

Answer 1

It would look something like this.

SELECT l1.geom, l2.geom AS non_intersecting_line
FROM lines AS l1
JOIN lines AS l2
  ON (
    ST_Intersects( ST_Buffer(l1.geom, 50), l2.geom )
    AND NOT ST_Intersects(l1.geom, l2.geom)
  )
WHERE l1.priority = 6;

WARNING: This doesn't strictly find parallel "lines." PostGIS is real world and not an abstract modeling tool. It doesn't have a notion of y=mx+b to constitute "parallel". This finds linesegments that do not intersect within 50 meters. They can be visually perpendicular non-intersecting lines in the United States and Zimbabwe, and this query will return them.

If you need visually parallel line segments defined by two points

• Calculate the azimuth of the desired line defined by two points.
• Calculate the azimuth of the line for comparison.
• Permit a margin of error for rounding points.

This still won't find lines near each other, but they should be parallel. For instance, lines of longitude are parallel with other lines of longitude but they can cross earth as separate points on the geode.

If you need visually parallel lines defined by more than two points: tough. You can either calculate the angle with any two points or you're not talking about a line anyway.

Update with sample data

This is exactly the problem with your description..

• Green is the buffer zone 50 meters.

  CREATE TABLE service_rds_buffer AS
  SELECT gid, name, priority, ST_Buffer(geom, 50)
  FROM service_rds WHERE priority = 6;
  

• The two lines inside the green are the only two roads where priority=6
• That road curves. It's not a line. It's a line segment that represents a road composed of 121 discrete points. What does it mean to be parallel to a line segment like that? It means nothing. Which takes you back to my original query.