Postgis:如何用两个查询只进行一个查询

Question

跟踪数据库：

一条痕迹是一条徒步路线

create_table "traces", force: :cascade do |t|
t.string "name"
t.geometry "path", limit: {:srid=>4326, :type=>"line_string"}
end

Pois db:

Poi是兴趣点(城市、种姓…(

create_table "pois", force: :cascade do |t|
t.string "address"
t.string "address2"
t.integer "zip_code"
t.string "city"
t.string "department"
t.string "region"
t.float "latitude"
t.float "longitude"
t.geography "lonlat", limit: {:srid=>4326, :type=>"st_point", :geographic=>true}
end

对于第一个查询，我从一个POI(pta=>poi1(中获得一个轨道(tr(周围的POI数组(ptb=>poi2(

SELECT
ptb.* AS pois
FROM traces tr, pois pta, pois ptb, locate_point_a
WHERE tr.id = #{trace.id}
AND pta.id = #{poi1.id}
AND ST_DWithin(
ST_LineSubstring(
tr.path,
ST_LineLocatePoint(tr.path, pta.lonlat::geometry) + (25 * 1000) / ST_Length(tr.path, false),
ST_LineLocatePoint(tr.path, pta.lonlat::geometry) + (250 * 1000) / ST_Length(tr.path, false)
)::geography,
ptb.lonlat::geography,
4000)

对于第二个查询，我计算一个POI和其他POI(在轨道上(之间的距离

SELECT
ST_Distance(tr.path::geography, pta.lonlat::geography) +
ST_Distance(tr.path::geography, ptb.lonlat::geography) +
ST_Length(ST_LineSubstring(
tr.path,
least(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, ptb.lonlat::geometry)),
greatest(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, ptb.lonlat::geometry))),false)  AS dst_line
FROM traces tr, pois pta, pois ptb, locate_point_a, locate_point_b
WHERE tr.id = #{trace.id}
AND pta.id = #{poi1.id}
AND ptb.id = #{poi2.id}

我只想做一个查询，并获得轨道周围POI的列表(按距离排序(以及从一个POI到列表中所有其他POI的距离(来自第一个查询(。

例如：

我从一个城镇出发。我想走25公里(距离(，知道在这段距离附近哪里可以找到一家旅馆睡觉。通过第一个查询，我可以得到一个列表，其中包含跟踪周围4000m的所有酒店(ptb(。

例如，对于第一个查询的结果，我得到了一个poi.ids的无序列表：[1, 7, 8, 3]

但是，我也需要知道并显示我的起点(pta(和每个酒店(ptb(之间到底有多少公里。它们是在21公里、22公里还是24公里？

因此，根据第二次查询的结果，我得到了每个poi的信息(来自第一次查询(：
[1 => 21.6] [7 => 26.2] [8 => 21.2] [3 => 20.4 ]

这两个查询完成了任务(但分别完成(。我需要有相同的结果，但只有一个查询。

按里程排序的所有酒店列表：

[3 => 20,4 , 8 => 21.2 ,  1=> 21,6 , 7 => 26,2]

Answer 1

SELECT
ST_Distance(tr.path::geography, pta.lonlat::geography) +
ST_Distance(tr.path::geography, poi.lonlat::geography) +
ST_Length(ST_LineSubstring(
tr.path,
least(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, poi.lonlat::geometry)),
greatest(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, poi.lonlat::geometry))),false)  AS dst_line, poi.*
FROM traces tr, pois pta, (
SELECT poi.* AS pois
FROM traces tr, pois pta, pois poi
WHERE tr.id = #{trace.id}
AND pta.id = #{poi.id}
AND ST_DWithin(ST_LineSubstring(
tr.path,
ST_LineLocatePoint(tr.path, pta.lonlat::geometry) + (#{dist} * 1000) / (tr.length * 1000) ,
1)::geography,
poi.lonlat::geography,
2000)
) as poi
WHERE tr.id = #{trace.id}
AND pta.id = #{poi.id}
AND poi.id = poi.id
ORDER BY dst_line ASC

现在，我需要优化它：D