Postgis:如何仅通过两个查询进行一次查询 [英] Postgis : How to make only one query with 2 queries

问题描述

跟踪数据库:

踪迹是远足之路

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

Pois db:

兴趣点是景点(城市，城堡等)

A Poi is a Point of Interest (city, 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 => poi1)围绕一个磁道(tr)获得了一个POIs(ptb => poi2)数组

With the first query, I get an array of POIs(ptb => poi2) around one track (tr), from one POI(pta => 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(在轨道上)之间的距离

With the second query, I calculate the distance between one POI and an other POIs (on the track)

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

I would like to do only one query and get the liste of POIs around track (ordered by distance) and the distance from one POI to all the others POIs from the list (from the first query).

例如:

我从一个小镇(pta)开始.我想步行25公里(距离)，并且想知道在这个距离附近可以找到旅馆的地方.通过第一个查询，我可以得到一个列表，其中所有的酒店(ptb)都在迹线周围4000 m.

I'm starting in a town (pta). I would like to walk 25 kms (distance) and to know where I can find an hostel for sleep around this distance. With the first query, I can get a list, with all the hotels (ptb), 4000 m around the trace.

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

For exemple for the result of the first query, I get an unorderer list of poi.ids : [1, 7, 8, 3]

但是，我也需要知道并显示我的出发点(pta)和每家旅馆(ptb)之间到底有多少公里.他们是21公里，22公里还是24公里...?

But, I need to know and display too, exactly how many kms there are between my start point(pta) and each hotel (ptb). Are they at 21 km, 22km or 24km... ?

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

So, with the result of the second query I get this info for each poi (from the first query) :
[1 => 21.6] [7 => 26.2] [8 => 21.2] [3 => 20.4 ]

这两个查询可以完成任务(但要单独执行).我需要具有相同的结果，但只有一个查询.

The two queries do the job (but individually). I need to have the same results but with only one query.

具有里程的所有酒店的有序列表:

An ordered list of all hotels with the mileage :

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

推荐答案

  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

Now, I need to optimize it :D

这篇关于Postgis:如何仅通过两个查询进行一次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！