2

I am using this query in PostGIS to get the closests points around a trace. This query is taking a long time to run : 15 sec with only 800 records in the POIs table.
The first query get a list of all POIs around the trace from a start point. The subquery calculate the distance to each point form the list, from the start point (ordered by distance).

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 = 2
    AND pta.id = 2
    AND ST_DWithin(ST_LineSubstring(
            tr.path,
            ST_LineLocatePoint(tr.path, pta.lonlat::geometry) + (20 * 1000) / (tr.length * 1000) ,
            1)::geography,
            poi.lonlat::geography,
            2000)
      ) as poi
    WHERE tr.id = 2
    AND pta.id = 2
    AND poi.id = poi.id
    ORDER BY dst_line ASC

Explain :

"Sort  (cost=31.03..31.04 rows=1 width=208)"
"  Sort Key: (((_st_distance((tr.path)::geography, pta.lonlat, '0'::double precision, true) + _st_distance((tr.path)::geography, poi.lonlat, '0'::double precision, true)) + 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))))::geography, false)))"
"  ->  Nested Loop  (cost=0.58..31.02 rows=1 width=208)"
"        ->  Nested Loop  (cost=0.30..21.52 rows=1 width=136)"
"              ->  Nested Loop  (cost=0.30..18.93 rows=1 width=104)"
"                    ->  Nested Loop  (cost=0.15..10.75 rows=1 width=64)"
"                          ->  Index Scan using traces_pkey on traces tr  (cost=0.15..8.17 rows=1 width=32)"
"                                Index Cond: (id = 2)"
"                          ->  Seq Scan on pois pta  (cost=0.00..2.58 rows=1 width=32)"
"                                Filter: (id = 2)"
"                    ->  Index Scan using traces_pkey on traces tr_1  (cost=0.15..8.17 rows=1 width=40)"
"                          Index Cond: (id = 2)"
"              ->  Seq Scan on pois pta_1  (cost=0.00..2.58 rows=1 width=32)"
"                    Filter: (id = 2)"
"        ->  Index Scan using index_pois_on_lonlat on pois poi  (cost=0.28..8.71 rows=1 width=200)"
"              Index Cond: (lonlat && _st_expand((st_linesubstring(tr_1.path, (st_linelocatepoint(tr_1.path, (pta_1.lonlat)::geometry) + ('20000'::double precision / (tr_1.length * '1000'::double precision))), '1'::double precision))::geography, '2000'::double precision))"
"              Filter: ((id IS NOT NULL) AND ((st_linesubstring(tr_1.path, (st_linelocatepoint(tr_1.path, (pta_1.lonlat)::geometry) + ('20000'::double precision / (tr_1.length * '1000'::double precision))), '1'::double precision))::geography && _st_expand(lonlat, '2000'::double precision)) AND _st_dwithin((st_linesubstring(tr_1.path, (st_linelocatepoint(tr_1.path, (pta_1.lonlat)::geometry) + ('20000'::double precision / (tr_1.length * '1000'::double precision))), '1'::double precision))::geography, lonlat, '2000'::double precision, true))"```  

Data Tables :

  create_table "pois", force: :cascade do |t|
    t.string "address"
    t.string "address2"
    t.integer "zip_code"
    t.string "city"
    t.string "departement"
    t.string "region"
    t.float "latitude"
    t.float "longitude"
    t.geography "lonlat", limit: {:srid=>4326, :type=>"st_point", :geographic=>true}
    t.string "poitable_type"
    t.bigint "poitable_id"
    t.bigint "user_id"
    t.index ["lonlat"], name: "index_pois_on_lonlat", using: :gist
    t.index ["master_poi_id"], name: "index_pois_on_master_poi_id"
    t.index ["poitable_type", "poitable_id"], name: "index_pois_on_poitable_type_and_poitable_id"
    t.index ["user_id"], name: "index_pois_on_user_id"
  end  

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

  • It looks a bit overcomplicated to me... what do you need to achieve? Select first N closest points to a trace and then compute their distance from trace first point? – DavidP Nov 11 '19 at 6:04
  • Write a new query with the <-> operator; "used in the ORDER BY clause provides index-assisted nearest-neighbor result sets." – CL. Nov 11 '19 at 9:02
  • @CL. I need to calculate the distance along a trace not by fly ;) – Ben Nov 13 '19 at 11:36
  • 1
    Then you can still use it to filter out objects that are too far away before doing the expensive computations. – CL. Nov 13 '19 at 11:46
  • Could you explain, how I can to do that ? – Ben Nov 13 '19 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.