PostgreSQL:在边界框内显示行程 [英] PostgreSQL: show trips within a bounding box
问题描述
我有一个trips
表,其中包含用户的旅行信息,如下所示:
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 | seconds_end | lat_end | lon_end | distance
---------+---------------+-------------+---------------+------------+------------+-------------+------------+------------+------------------
594221 | 16772 | {170487} | 1561324555 | 41.1175475 | -8.6298934 | 1561325119 | 41.1554091 | -8.6283493 | 5875.39697884959
563097 | 7682 | {128618} | 1495295471 | 41.1782829 | -8.5950303 | 1495299137 | 41.1783908 | -8.5948965 | 5364.81067787512
596303 | 17264 | {172851} | 1578011699 | 41.5195598 | -8.6393526 | 1578012513 | 41.4614024 | -8.717709 | 11187.7956426909
595648 | 17124 | {172119} | 1575620857 | 41.1553116 | -8.6439528 | 1575621885 | 41.1621821 | -8.6383042 | 1774.83365424607
566061 | 8720 | {133624} | 1509005051 | 41.1241975 | -8.5958988 | 1509006310 | 41.1424158 | -8.6101461 | 3066.40306678979
566753 | 8947 | {134662} | 1511127813 | 41.1887996 | -8.5844238 | 1511129839 | 41.2107519 | -8.5511712 | 5264.64026582458
561179 | 7198 | {125861} | 1493311197 | 41.1776935 | -8.5947254 | 1493311859 | 41.1773815 | -8.5947254 | 771.437257541019
541328 | 2119 | {46950} | 1461103381 | 41.1779 | -8.5949738 | 1461103613 | 41.1779129 | -8.5950202 | 177.610819150637
535519 | 908 | {6016} | 1460140650 | 41.1644658 | -8.6422775 | 1460141201 | 41.1642646 | -8.6423309 | 1484.61552373019
548460 | 3525 | {102026} | 1462289206 | 41.177689 | -8.594679 | 1462289843 | 41.1734476 | -8.5916326 | 1108.05119077308
(10 rows)
任务是过滤在upper left: 41.24895, -8.68494
和lower right: 41.11591, -8.47569
定义的边界框中开始和结束的行程.
The task is to filter trips that start and end within the bounding box defined by upper left: 41.24895, -8.68494
and lower right: 41.11591, -8.47569
.
推荐答案
由于坐标存储在x,y列中,因此必须使用 ST_MakeEnvelope
创建一个BBOX,并检查开始和结束坐标使用 ST_Contains
,例如
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));
注意: CTE
不是必需的,它在查询中仅用于说明目的.您可以在WHERE
子句(而不是bbox.geom
)的两种情况下都重复ST_MakeEnvelope
函数.此查询还假定使用SRS WGS84
(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).
这篇关于PostgreSQL:在边界框内显示行程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!