PostGIS bounding box query returned strange results

Question

I tried the following SQL commands:

CREATE TABLE places(
    lat_lng geography(Point,4326),
    place_name varchar(50)
);

CREATE INDEX places_lat_lng_idx ON places USING gist(lat_lng);

INSERT INTO places values ('POINT(-126.4 45.32)', 'Food Bar1');
INSERT INTO places values ('POINT(-126.4 47.32)', 'Food Bar2');
INSERT INTO places values ('POINT(-125.4 47.42)', 'Food Bar3');

SELECT place_name, ST_AsText(lat_lng) as point
FROM places WHERE places.lat_lng && 
  ST_MakeEnvelope(-130.0, 44.0,
                  -100.0, 46.7, 4326);

The result is:

 place_name |        point        
------------+---------------------
 Food Bar1  | POINT(-126.4 45.32)
 Food Bar2  | POINT(-126.4 47.32)
 Food Bar3  | POINT(-125.4 47.42)

This doesn't look right to me, as the ymax is 46.7, but the "Food Bar2" and "Food Bar3" have ymax values of 47.32 and 47.42, perspectively. Where is the problem?

Answer 1

Here is your geography envelope:

Here it is, with your query points. The envelope is flattened to Cartesian space with ST_Segmentize:

SELECT ST_Segmentize(
   ST_MakeEnvelope(-130.0, 44.0,
                   -100.0, 46.7, 4326)::geography,50000);

So you are correct that the points should be within the geography envelope, however you used a && bounding box operator, which ignores the geometry shape. The bounding box for the geography envelope looks like this:

which shows all points in the bounding box.

Fix the query with something like this:

SELECT place_name, ST_AsText(lat_lng) as point
FROM places
WHERE ST_Intersects(
    ST_MakeEnvelope(-130.0, 44.0,
                    -100.0, 46.7, 4326), 
    places.lat_lng)