Query to find all buildings in area using PostGIS on OpenStreetMap database

Question

I have a PostGIS database filled using OpenStreetMap (OSM) data of a French region.

If I made no mistake, the zone I want to investigate is bounded to the zone of the data, so I should be able to extract it.

One of the queries tried:

SELECT count(building) 
FROM   planet_osm_polygon 
WHERE  way && st_setsrid(
          st_makebox2d(st_makepoint(50.990729,2.269084), 
          st_makepoint(51.084423,2.530352)),3857);

I tried searching the site for similar problems and tried the solutions but still get nothing (empty table as a result of my queries).

I think I have a problem with the SRID of my bounding box but I only just started using PostGIS and OSM data for the first time in my life.

Some more info that may help :

• polygon(way) gives me a list of point of this format (178086.28309914726,6521135.618583532) but using select count(building) from planet_osm_polygon where way && st_setsrid(st_makebox2d(st_makepoint(5099072.9,226908.4), st_makepoint(5108442.3,253035.2)),3857); did not help
• st_srid = 3857
• using: ST_GeomFromText('POLYGON( 50.990729 2.269084, 50.990729 2.530352,51.084423 2.530352, 51.084423 2.269084)') to make my bounding box does not help either.
• I tried reversing latitude and longitude values in case I had just mixed the two but still nothing.

I also tried st_within but got nothing.

---

I had just copied and pasted the coordinates of my points but didn't check the lat/lon order, resulting in no results while the queries were right. @Vince’s comment pushed me to double-check.

Correct query:

SELECT count(building)
FROM planet_osm_polygon 
WHERE way && st_transform(
      st_setsrid(
      st_makebox2d(st_point(2.269084,50.990729),  
      st_point(2.530352,51.084423))
       ,4326)
       ,3857);
`

Answer 1

I needed to project the geographic (4326) coordinates with ST_Transform to get them into Web Mercator (3857).

I also had just copied and pasted the coordinates of my points but didn't check the lat/lon order, resulting in no results while the queries were right.

Correct query:

SELECT count(building)
FROM planet_osm_polygon 
WHERE way && st_transform(
      st_setsrid(
      st_makebox2d(st_point(2.269084,50.990729),  
      st_point(2.530352,51.084423))
       ,4326)
       ,3857);