PostgreSQL: Show Trips Within A Bounding Box

I have a trips table containing user's trip information, like so: select * from trips limit 10; trip_id | daily_user_id | session_ids | seconds_start | lat_start | lon_start | s

Solution 1:

Since your coordinates are stored in x,y columns, you have to use ST_MakePoint to create a proper geometry. After that, you can create a BBOX using the function ST_MakeEnvelope and check if start and end coordinates are inside the BBOX using ST_Contains, e.g.

WITH bbox(geom) AS (
  VALUES (ST_MakeEnvelope(-8.68494,41.24895,-8.47569,41.11591,4326))
)
SELECT * FROM trips,bbox
WHERE 
  ST_Contains(bbox.geom,ST_SetSRID(ST_MakePoint(lon_start,lat_start),4326)) AND
  ST_Contains(bbox.geom,ST_SetSRID(ST_MakePoint(lon_end,lat_end),4326));

Note: the CTE isn't really necessary and is in the query just for illustration purposes. You can repeat the ST_MakeEnvelope function on both conditions in the WHERE clause instead of bbox.geom. This query also assumes the SRS WGS84 (4326).

Solution 2:

If I understand correctly, you can just compare that starting and ending coordinates:

select t.*
from trips t
where lat_start >= 41.11591 and lat_start <= 41.24895 and
      lat_end >= 41.11591 and lat_end <= 41.24895 and
      long_start >= -8.68494 and long_start <= -8.47569 and
      long_end >= -8.68494 and long_end <= -8.47569