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

Question

痕迹数据库：

一条小路就是一条远足之路

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

Pois数据库：

Poi是一个兴趣点(城市，castel.)

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 => poi2) (pta => poi1)中得到围绕一个轨道(tr)的一系列POI(ptb=>poi1)。

      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的距离。

例如：

我要从一个小镇(pta)开始。我想步行25公里(距离)，并知道在哪里可以找到一个宿舍供睡眠围绕这一距离。使用第一个查询，我可以得到一个列表，所有的酒店(ptb)，4000米左右的跟踪。

例如，对于第一个查询的结果，我得到了一个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