# Return roads that extend entire length of bounding box from OSM data

Lets say I have a bounding box of lat/lon of:

Left -122.27671
Bottom 37.80445
Right -122.26673
Top 37.81449

I have OSM Data of all roads/ways/nodes...etc within this bounding box. Is it possible to return roads that extend the entire length of the bounding box? Or that intersect with two different areas of the bounding box?

Or is there a better way to solve this: I'm essentially trying to filter out small side streets. Maybe I could do this by the type of way or by the total length of the way?

Try this..

1. Decompose the box with ST_Boundary into the border lines
2. Calculate the ST_Intersection of it. That'll return a MULTIPOINT.
3. Count with the points inside the MULTIPOINT ST_NPoints

Sample code.

SELECT ST_NPoints( ST_Intersection(ST_Boundary(box), line) )
FROM ( VALUES
(-122.27671, 37.80445, -122.26673, 37.81449)
) AS t(l, b, r, t)
CROSS JOIN LATERAL ST_MakeEnvelope(l,b,r,t) AS box
CROSS JOIN LATERAL ST_MakeLine(
ST_MakePoint( (l+r)/2, b - 0.1 ),
ST_MakePoint( (l+r)/2, t + 0.1 )
) AS line;

We generate a line that exceeds the bounding box right down the center with those exact coordinates you've provided.

You can get the points instead of just the count of the points, using ST_DumpPoints()

• Thank you! This seems to be working, and when I use ST_DumpPoints this is what it returns ("{1,1}",0101000000D36A48DC63915EC034A2B437F8E64240) ("{2,1}",0101000000D36A48DC63915EC0A375543541E84240) What is it referencing? – theartofbeing Jan 10 '17 at 17:36
• The {1,1} is the position in the parent geometry. Look at the example on the bottom of the page. postgis.net/docs/ST_DumpPoints.html – Evan Carroll Jan 10 '17 at 17:38
• how do I link this back to a specific way or road? – theartofbeing Jan 10 '17 at 17:39
• See which roads ST_Crosses() that specific point of intersection. There may be more than one. – Evan Carroll Jan 10 '17 at 17:41
• Sorry, I'm completely new to PostGIS and doing spatial work. Are the dump points referencing the bounding box or are they referencing lines that intersect with the bounding box and their position? I'm having a hard time tying this back to OSM data to find which roads intersect the box. – theartofbeing Jan 10 '17 at 17:45