Finding all ways that are partly within a bounding box

Question

I'm trying to write a query that will return all OSM ways which are contained within a bounding box of min/max gps coordinates.

I get these cooridinates from the greater london bounding box on http://wiki.openstreetmap.org/wiki/Bounding_Box

From googling I have this query, but it doesn't return any results:

SELECT 
    planet_osm_roads.* 
FROM planet_osm_roads 
WHERE ST_Contains( 
    ST_SetSRID(
        ST_MakeBox2D(
            ST_Point(0.3340155, 51.2867602), ST_Point(-0.5103751, 51.6918741)
        ), 
        4326
    ), 
    planet_osm_roads.way
)

Any idea what I'm doing wrong?

Update

So I've swapped the points around, tested that the polygon created is what I expect by getting the geometry as geojson and visualising it on geojson.io - it looks fine, however I still get 0 results and it happens basically instantly (which seems weird). The table is definitely fully populated! I also changed to use ST_Intersects but that hasn't made it return anything either

SELECT 
    planet_osm_roads.* 
FROM planet_osm_roads 
WHERE ST_Intersects(
    planet_osm_roads.way, 
    ST_SetSRID(
        ST_MakeBox2D(
    ST_Point(0.3340155, 51.2867602),
            ST_Point(-0.5103751, 51.6918741)
        ), 
        4326
    )
)

It gives me this GeoJSON when I SELECT ST_AsGeoJSON

{
  "type": "Polygon",
  "coordinates": [
    [
      [-0.5103751, 51.2867602],
      [-0.5103751, 51.6918741],
      [0.3340155, 51.6918741],
      [0.3340155, 51.2867602],
      [-0.5103751, 51.2867602]
    ]
  ]
}

Which as I said, looks correct on geojson.io :)

Update #2

I've copied a query from another SO question that gets the closest point on a road or something, with a point in London, it returns some results roughly as I expect

SELECT 
 osm_id,
 highway,
 name,
 ref, 
 ST_X(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom)),      
 ST_Y(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom)),
 ST_Distance_Sphere(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom), point.geom)
FROM 
  planet_osm_roads r,
  (Select ST_SetSRID(ST_MakePoint(-0.1367229, 51.5377733), 4326) as geom) point
ORDER BY 7 ASC
LIMIT 10;

returns

41383228;"trunk";"Camden Street";"A400";-0.137183850111055;51.5373791549638;54.195918203
253353304;"primary";"Royal College Street";"A5202";-0.13568782069246;51.538616921758;118.002625807
17513372;"primary";"Royal College Street";"A5202";-0.134746843460286;51.5374616146768;140.996741591
166369355;"primary";"Royal College Street";"A5202";-0.136110935219222;51.5391336013284;157.068830304
253352352;"primary";"Royal College Street";"A5202";-0.136578148998492;51.5397000983131;214.484260552
6247747;"primary";"Pratt Street";"A503";-0.140021211819466;51.5376420920654;228.588127843
211950233;"primary";"Bayham Street";"A503";-0.140021211819466;51.5376420920654;228.588127843
253350830;"primary";"St. Pancras Way";"A5202";-0.135177855133607;51.5396274637255;232.22039167
11266300;"primary";"Royal College Street";"A5202";-0.136734905015571;51.5398841985372;234.722999647
253350834;"primary";"St Pancras Way";"A5202";-0.135399101773244;51.5397738652997;240.557396864

The point is actually closest to Mandela Street which runs next to Camden Street, but I'll worry about that after I have my initial query working.

My data came from download england osm data and running this command:

osm2pgsql --create --database england_osm -C 6144 ~/Downloads/england-latest.osm

Answer 1

The query looks good but if you want all ways which are partly in a bounding box you have to use the st_intersects function.

If you want to show the error of your defined bounding box and you don't have any software you can put you bounding box in an ST_AsGeoJson function to get an geojson string as result. The easiest way to check your geometry is to enter the geojson result in an online viewer like http://geojson.io/

Answer 2

So, it was because I hadn't specified that points should be stored in latitude and longitude when running osm2pgsql.

The command I used successfully was

osm2pgsql -c -l -s -d england_osm ~/Downloads/england-latest.osm.pbf

Answer 3

-- roads inside bounding box
SELECT l.osm_id
FROM planet_osm_line l,
  (
    SELECT ST_Transform(ST_MakeEnvelope(min_x, min_y, max_x, max_y, 4326),3857) AS box
    FROM (
           SELECT
             least(x1, x2)    AS min_x,
             greatest(x1, x2) AS max_x,
             least(y1, y2)    AS min_y,
             greatest(y1, y2) AS max_y
           FROM (SELECT
                   :lon1 AS x1, 
                   :lon2 AS x2,
                   :lat1  AS y1,
                   :lat2  AS y2) cr
         ) r) bb
WHERE l.way && bb.box -- intersection (use @ for contain)
  and l.highway IS NOT NULL;