Categories
Ask Development Geography

Postgis : How to make only one query with 2 queries

Traces db :

A trace is an hiking path

  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

With the first query, I get an array of POIs(ptb => poi2) around one track (tr), from one POI(pta => poi1)

      WITH RECURSIVE locate_point_a AS (
          select ST_LineLocatePoint(tr.path, pta.lonlat::geometry) AS locate_point_a
          FROM traces tr, pois pta
          WHERE tr.id = #{trace.id}
          AND pta.id = #{poi1.id}
          )
      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,
          locate_point_a + (25 * 1000) / ST_Length(tr.path, false),
          locate_point_a + (250 * 1000) / ST_Length(tr.path, false)
          )::geography,
          ptb.lonlat::geography,
          4000)

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

     WITH locate_point_a AS (
          select ST_LineLocatePoint(tr.path, pta.lonlat::geometry) AS locate_point_a
          FROM traces tr, pois pta
          WHERE tr.id = #{trace.id}
              AND pta.id = #{poi1.id}
          ),
        locate_point_b AS (
          select ST_LineLocatePoint(tr.path, ptb.lonlat::geometry) AS locate_point_b
          FROM traces tr, pois ptb
          WHERE tr.id = #{trace.id}
              AND ptb.id = #{poi2.id}
          )
      SELECT
        ST_Distance(tr.path::geography, pta.lonlat::geography) +
        ST_Distance(tr.path::geography, ptb.lonlat::geography) +
        ST_Length(ST_LineSubstring(
          tr.path,
          least(locate_point_a, locate_point_b),
          greatest(locate_point_a, locate_point_b)),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}

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).

For exemple :

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.

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

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… ?

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]

Leave a Reply

Your email address will not be published. Required fields are marked *