Znalezienie najbliższej linii, która otrzymała 2 punkty za pomocą PostGIS?

Mam tabelę t, która zawiera kolumnę line_positionstypu wiersza. Biorąc pod uwagę 2 punkty, chcę znaleźć najbliższą linię, która jest wystarczająco blisko (mniej niż 10 km) i która nie przechodzi zbyt blisko punktu, którego chcę uniknąć (minimum 20 km). Obecnie używam

SELECT t.*
FROM path t
WHERE
  ST_DWithin(ST_GeographyFromText('Point(69.835 22.596)'), t.line_positions, 10000, FALSE)  AND
  ST_DWithin(ST_GeographyFromText('Point(69.856 22.519)'), t.line_positions, 10000, false) AND
  NOT ST_DWithin(ST_GeographyFromText('Point(-79.804 9.141)'), t.line_positions, 20000, false)
ORDER BY
  ST_Distance(ST_GeographyFromText('Point(69.835 22.576)'), t.line_positions, false) +
  ST_Distance(ST_GeographyFromText('Point(69.856 22.519)'), t.line_positions, false)
  ASC
LIMIT 1

W ix_path_line_positionskolumnie line_positions znajduje się indeks gist .

Działa, ale wolno, od 3 do 30 sekund dla zaledwie 100 000 wierszy wt.

wyjaśnij analizuj daje:

Limit  (cost=9.95..9.95 rows=1 width=1432) (actual time=21729.253..21729.254 rows=1 loops=1)
   ->  Sort  (cost=9.95..9.95 rows=1 width=1432) (actual time=21729.251..21729.251 rows=1 loops=1)
         Sort Key: ((_st_distance('0101000020E61000003D0AD7A370755140FA7E6ABC74933640'::geography, line_positions, '0'::double precision, false) + _st_distance('0101000020E6100000105839B4C8765140BE9F1A2FDD843640'::geography, line_positions, '0'::double precision, false)))
         Sort Method: top-N heapsort  Memory: 26kB"
         ->  Index Scan using ix_path_line_positions on path t  (cost=0.28..9.94 rows=1 width=1432) (actual time=93.490..21710.562 rows=690 loops=1)
           Index Cond: ((line_positions && '0101000020E61000003D0AD7A3707551407F6ABC7493983640'::geography) AND (line_positions && '0101000020E6100000105839B4C8765140BE9F1A2FDD843640'::geography))
           Filter: (('0101000020E61000003D0AD7A3707551407F6ABC7493983640'::geography && _st_expand(line_positions, '10000'::double precision)) AND ('0101000020E6100000105839B4C8765140BE9F1A2FDD843640'::geography && _st_expand(line_positions, '10000'::double precision)) AND _st_dwithin('0101000020E61000003D0AD7A3707551407F6ABC7493983640'::geography, line_positions, '10000'::double precision, false) AND _st_dwithin('0101000020E6100000105839B4C8765140BE9F1A2FDD843640'::geography, line_positions, '10000'::double precision, false) AND ((NOT ('0101000020E6100000FA7E6ABC74F353C0D578E92631482240'::geography && _st_expand(line_positions, '20000'::double precision))) OR (NOT (line_positions && '0101000020E6100000FA7E6ABC74F353C0D578E92631482240'::geography)) OR (NOT _st_dwithin('0101000020E6100000FA7E6ABC74F353C0D578E92631482240'::geography, line_positions, '20000'::double precision, false))))
           Rows Removed by Filter: 15365
Planning time: 0.491 ms
Execution time: 21729.321 ms

Jak mogę to poprawić? Zamiast tego korzystam z obliczeń geometrii (ale czy mój tor może obejmować kilka tysięcy kilometrów, czy obliczone odległości będą prawidłowe)? Korzystając z operatora <-> KNN (ale skoro zamawiam na sumę 2 odległości, to i tak nie wydaje się używać indeksu gist)?

— Go4It
źródło

Możesz spróbować zwiększyć parametr work_mem przed wykonaniem kodu. EgSET work_mem TO '200MB';

— Jarosław

Czy dwa podane punkty są zawsze w odległości 10 km od siebie. Jeśli tak, możesz spróbować ustawić dwa punkty jako linię i wykonać jeden ST_DWithin zamiast dwóch. To może nieco poprawić sytuację.

SELECT t.*
FROM path t
WHERE
  ST_DWithin(ST_GeomFromText('LINESTRING(69.835 22.596,69.856 22.519)'), t.line_positions, 10000, FALSE)  
  NOT ST_DWithin(ST_GeographyFromText('Point(-79.804 9.141)'), t.line_positions, 20000, false)
ORDER BY
  ST_Distance(ST_GeographyFromText('Point(69.835 22.576)'), t.line_positions, false) +
  ST_Distance(ST_GeographyFromText('Point(69.856 22.519)'), t.line_positions, false)
  ASC
LIMIT 1

— użytkownik92055
źródło

Nie, 2 punkty nie znajdują się w odległości 10 km od siebie, mogą być w odległości tysiąca kilometrów. Ograniczeniem jest to, że oba znajdują się w odległości mniejszej niż 10 km od linii, ale może być naprawdę daleko, ponieważ linie rozciągają się na tysiąc km.

— Go4It