Cel: znalezienie miejscowości w Polsce najbardziej oddalonych od cywilizacji
Realizacja: Zakładamy że miejscowość najbardziej oddalona od cywilizacji to taka której odległość od najbliższego sąsiada jest największa. Jako źródło posłużą nam dane z OSM pobrane z dumpa udostępnionego przez
GeoFabrik.
Użyjemy polskiego układu odniesienia
SRID 2180
SELECT p1.gid,p1.name,p2.name AS closest,p1.geom<->p2.geom AS "4326_dist",p1.geom2<->p2.geom2 AS "2180_dist",ST_DISTANCE(p2.geog,p1.geog,TRUE) as geog_dist FROM
( SELECT p1.gid as g1,
(SELECT p.gid
FROM places AS p
WHERE p1.gid<>p.gid
AND p.type<>'locality'
ORDER BY p.geom2<->p1.geom2 ASC LIMIT 1) AS g2
FROM places AS p1
WHERE p1.type NOT IN ('locality','suburb')
OFFSET 0
) AS q
JOIN places AS p1
ON q.g1=p1.gid
JOIN places AS p2
ON q.g2=p2.gid
ORDER BY "2180_dist" DESC
LIMIT 10;
EXPLAIN ANALYZE
Interesują nas tylko miejscowości ale mierzymy ich odległość od przedmieść by choć trochę nadrobić to że nie mierzymy faktycznych granic administracyjnych.
gid | name | closest | 4326_dist | 2180_dist | geog_dist
-------+-----------+----------------+--------------------+------------------+------------------
8426 | Jastrowie | Ptusza | 0.0616304650991193 | 6306.86042240474 | 6309.65174708274
7930 | Niekursko | Sarcz | 0.0696506877357633 | 6059.07941299731 | 6061.08526001983
28586 | Hel | Hel Bór | 0.0653122224030758 | 6057.77326343045 | 6062.04615190325
7297 | Szwecja | Ostrowiec | 0.0617091111621273 | 5865.59766775731 | 5867.79900767539
7808 | Ostrowiec | Wałcz | 0.0708066344153986 | 5459.97500003219 | 5461.9397373565
7488 | Skórka | Paruszka | 0.0559207089411363 | 5383.63952364084 | 5386.10143273522
74 | Kuźnica | Chałupy | 0.0754909744769629 | 5337.0960945683 | 5340.76237394738
75 | Chałupy | Kuźnica | 0.0754909744769629 | 5337.0960945683 | 5340.76237394726
24770 | Wołosate | Ustrzyki Górne | 0.0537518787199188 | 5229.74155008295 | 5228.79126557615
53625 | Łebki | Kamińsko | 0.0636011795158242 | 5157.97617185783 | 5161.54924296325
Wyniki z indeksu dla geometry dla SRID 2180 są dość zbliżone do tych z typu geography, te dla geometry SRID 4326 niestety dość odbiegają. Można też zauważyć że przy użyciu geography dystans z Kuźnicy do Chałup jest mniejszy niż z Chałup do Kuźnicy. Podmieniamy pierwszą linijkę na:
SELECT p1.gid,
RANK() OVER (ORDER BY p1.geom<->p2.geom DESC) AS "4326_rank",
RANK() OVER (ORDER BY p1.geom2<->p2.geom2 DESC) AS "2180_rank",
RANK () OVER (ORDER BY ST_DISTANCE(p2.geog,p1.geog,TRUE) DESC) as geog_rank
I otrzymujemy porównanie sortowania:
gid | 4326_rank | 2180_rank | geog_rank
-------+-----------+-----------+-----------
8426 | 26 | 1 | 1
7930 | 7 | 2 | 3
28586 | 12 | 3 | 2
7297 | 24 | 4 | 4
7808 | 6 | 5 | 5
7488 | 52 | 6 | 6
74 | 1 | 7 | 7
75 | 1 | 7 | 8
24770 | 77 | 9 | 9
53625 | 17 | 10 | 10
Brak komentarzy:
Prześlij komentarz