ST_MakeEnvelope with Google Maps Coordinates problem

Question

In postgress I have the following function that uses ST_MakeEnvelope based on the Google Maps viewport coordinates to returns all projects within that viewport.

CREATE OR REPLACE FUNCTION public.search_projects_area(
  lngw double precision,
  lats double precision,
  lnge double precision,
  latn double precision
) RETURNS SETOF project_locations LANGUAGE sql STABLE AS $ function $
SELECT
  A.*
FROM
  project_locations A
WHERE
  st_intersects(
    A.location :: geography,
    ST_MakeEnvelope(lngw, lats, lnge, latn, 4326) :: geography
  ) $ function $

Below is how I get the coordinates to create the envelope:

const bounds = this.map.getBounds();
const sw = this.map.getBounds().getSouthWest();
const ne = this.map.getBounds().getNorthEast();
const lngw = sw.lng();
const lats = sw.lat();
const lnge = ne.lng();
const latn = ne.lat();

The problem is that projects in specific areas are not fetched sometimes depending on where I have the map zoomed or positioned. The projects are defenitely inside the viewport though in all cases.

Its as if the ST_MakeEnvelope method doesnt create the correct rectangle based on the map viewport.

Is my code above correct?

Answer 1

By casting to geography, the bounding box edges are created using great circle arcs instead of straight lines. Therefore, a point that looks inside the viewport (projected in 3857) may be outside of the box.

So either you do want to work with great circle arcs and it is a display issue, or you want to stick to what is displayed and you would need to remove the cast to geography