4

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. enter image description here

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.

enter image description here

  • I would try by creating buffers for the blue lines and selecting lines which are completely within the buffered geometries. With suitable buffer distance selection should contain the green lines and not too much other features. – user30184 Jan 18 '17 at 21:00
  • creating buffers couldn't do it, as said in my updated question – Shahzad Bacha Jan 19 '17 at 5:32
  • If blue lines are not cut at intersections they are not completely within the buffered area. And by removing lines which intersect with the dark blue lines you can get rid of the thin blue ones. – user30184 Jan 19 '17 at 6:54
4

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.

Sample Data

  • Instead of using ST_Intersects in inner SQL, how about using postgis.net/docs/ST_Covers.html for selecting only roads which are totally inside the buffered road? Perhaps it would help in finding visually parallel lines? Perhaps enhanced with ST_Distance and setting a minimum distance between l1 and l2? – user30184 Jan 19 '17 at 6:49
  • Thank you @Evan, i tried the above query and i used st_contains instead of st_intersects. it seems like NOT ST_INTERSECTS does not work properly because the query still return the lines which intersect with blue lines. – Shahzad Bacha Jan 19 '17 at 8:34
  • Are you confusing ST_Intersects with ST_Intersection? ST_Intersects returns True-False? So long as they Intersect, they'll return true. We negate that so we only pull in the lines that do not intersect. – Evan Carroll Jan 19 '17 at 8:36
  • yes, i am aware of the st_intersects response. if i am right, the query should not return those lines which intersect with blue lines? – Shahzad Bacha Jan 19 '17 at 8:40
  • Could you provide some sample data? – Evan Carroll Jan 19 '17 at 8:48

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.