Vairāk

Vai iegūt ST_ClosestPoint for Geography tipu?

Vai iegūt ST_ClosestPoint for Geography tipu?


Izmantojot PostGIS 2.0.0, es vēlētos atrast punktu LINESTRING, kas ir vistuvāk konkrētajam punktam. LINESTRING apzīmē lielu apļa līniju (ti, ģeogrāfijas tipu). Šķiet, ka ST_ClosestPoint dara tieši to, ko es vēlos, tomēr es uzskatu, ka atgrieztais punkts neatrodas uz lielā apļa līnijas:

AR punktiem AS (SELECT ST_GeomFromEWKT ('SRID = 4326; POINT (41 12)') AS punkts, ST_GeomFromEWKT ('SRID = 4326; LINESTRING (40 5,41 15)') AS robeža) SELECT ST_AsText (ST_ClosestPoint (robeža, punkts )), - PUNKTS (40.7029702970297 12.029702970297) ST_DWithin (ST_ClosestPoint (robeža, punkts) :: ģeogrāfija, robeža :: ģeogrāfija, 700) - nepatiesi FROM punkti;

Tas faktiski atrodas vairāk nekā 700 metru attālumā no līnijas. Es uzskatu, ka tas notiek tāpēc, ka ST_ClosestPoint darbojas lidmašīnā. Kā es varētu darīt to pašu uz sfēras (vai vismaz uz sfēras)?


Es izmēģināju @MerseyViking funkciju (pārnesta uz plpgsql), un tā lielākoties darbojas ļoti labi, bet dažos gadījumos atgriež punktu, kas nav uz līnijas. Vienkāršs piemērs:

WITH punkti AS (SELECT ST_GeomFromEWKT ('SRID = 4326; POINT (10 3)') AS point, ST_GeomFromEWKT ('SRID = 4326; LINESTRING (10 5,10 15)') AS border), calc AS (SELECT ST_ClosestPoint (border , punkts) :: ģeogrāfija AS plaknes tuvākais, tuvākais_punkta_līnijai (robeža, punkts) AS sfēras tuvākais no punktiem) SELECT ST_AsText (plaknes tuvākais) AS plaknes tuvākais, - PUNKTS (10 5) ST_DWinin (plaknes tuvākais, robeža :: ģeogrāfija, 1), - true ST_AsText (sfēras_closest) AS sfēras_closest, - PUNKTS (10 3) ST_DWinin (sfēras_closest, robeža :: ģeogrāfija, 1), - nepatiesi FROM punkti, aprēķins;

Diemžēl nav ģeogrāfiskās versijasST_ClosestPoint, tāpēc jums būs jāraksta sava funkcija. Ir divi veidi, kā aprēķināt tuvākā lielā apļa punktu: sfēriskā trigonometrija vai 3D vektora algebra. Par laimi, es tikko uzrakstīju šādu funkciju pēdējai metodei; Es neesmu mēģinājis pirmo, jo mans sfēriskais trig ir diezgan slikts.

Esmu uzrakstījis funkciju PL/Python, ko varat pievienot savai datu bāzei, lai gan jums būs jāinstalē Python bibliotēkas un PL/Python iesaiņojumi, kas, ja izmantojat Linux, ir vienkārši jāinstalēpostgresql-python-x.xpakete (kurx.xir jūsu izmantotā PostgreSQL versija).

Šī funkcija ne vienmēr ir visefektīvākā, tai nav robežu vai kļūdu pārbaudes, un tā izmanto tikai sfēru, taču tā noteikti darbojas pietiekami labi ar datiem, ar kuriem esmu to izmēģinājis. Jūtieties brīvi attiecīgi pārveidot.

Vispirms es definēju savu vienkāršo garā/lata veidu:

CREATE TYPE lon_lat AS (lon float, lat float);

Funkcija izskatās šādi:

IZVEIDOT VAI NOMAINIET FUNKCIJU geog_nearest_point (lp1 lon_lat, lp2 lon_lat, p lon_lat) ATGRIEZI lon_lat AS $$ importēt math def sph2cart (lon, lat, r): return (r * math.cos (lat) * math.cos (lon), r * math.cos (lat) * math.sin (lon), r * math.sin (lat)) def cart2sph (x, y, z): atgriezties (math.atan2 (y, x), math.atan2 ( z, math.sqrt (x * x + y * y)), math.sqrt (x * x + y * y + z * z)) def cross_prod (v1, v2): atgriezties (v1 [1] * v2 [ 2] - v1 [2] * v2 [1], v1 [2] * v2 [0] - v1 [0] * v2 [2], v1 [0] * v2 [1] - v1 [1] * v2 [ 0]) lv1 = sph2cart (math.radians (lp1 ['lon']), math.radians (lp1 ['lat']), 1.0) lv2 = sph2cart (math.radians (lp2 ['lon']), math .radians (lp2 ['lat']), 1.0) pv = sph2cart (math.radians (p ['lon']), math.radians (p ['lat']), 1.0) f = cross_prod (lv1, lv2 ) g = krusta_prods (pv, f) h = krusta_prods (f, g) tuvākais_punkts = grozs2sph (h [0], h [1], h [2]) atgriešanās (matemātikas grādi (tuvākais_punkts [0]), matemātika). grādi (tuvākais_punkts [1])) $$ VALODA 'plpythonu' IMMUTABLE;

Un, sniedzot tam jūsu sniegtos datus, man ir šāds:

geog_db =# SELECT geog_nearest_point ((41, 15) :: lon_lat, (40, 5) :: lon_lat, (41, 12) :: lon_lat); geog_nearest_point ------------------------------- (40.6960040707,12.0295700818) (1 rinda)
  • Tas darbojas, atrodot punktu f sfērā, kas ir normāla plaknei, kas raksturo lielo apli. Šis punkts ir unikāls katram lieliskajam lokam.
  • Tālāk tas izmanto f un ievades punktu lpp lai definētu plakni, kas ir perpendikulāra ievades lielajam aplim, un nosaka tās unikālo normālo punktu g. Vektori no sfēras centra līdz f un g definējiet plakni, kas ir perpendikulāra divām iepriekšējām plaknēm.
  • Šīs trešās plaknes normas atrašana dod mums punktu, kurā krustojas divi lielie apļi.
  • Visbeidzot, šī Dekarta koordināta tiek pārvērsta atpakaļ platuma un garuma grādos.

Ja kādam ir sfēriska trig metode, kas, manuprāt, būs nedaudz efektīvāka, es labprāt to redzētu.


Šī ir PL/pgSQL versija MerseyVikingkods. Tas izmanto arī PostGIS ģeogrāfijuPunktsunLineStringtipus, nevis pielāgotu veidu, lai attēlotu koordinātas.

IZVEIDOT VAI Nomainiet FUNKCIJU _punkts_to_kartesianam (punkta ģeometrija (punkts), rādiusa pludiņš, OUT x pludiņš, OUT y pludiņš, OUT z pludiņš) ATGRIEŽ REKORDU kā $ BODY $ DECLARE lon float; lat pludiņš; SĀKT lon: = radiāni (ST_X (punkts)); lat: = radiāni (ST_Y (punkts)); x: = rādiuss * cos (lat) * cos (lon); y: = rādiuss * cos (lat) * sin (lon); z: = rādiuss * sin (lats); END $ BODY $ LANGUAGE plpgsql IMMUTABLE; IZVEIDOT VAI Nomainiet FUNKCIJU _cartesian_to_point (x pludiņš, y pludiņš, z pludiņš) ATGRIEŽ ģeometriju (punkts) AS $ BODY $ DECLARE lon float; lat pludiņš; SĀKT lon: = grādi (atan2 (y, x)); lat: = grādi (atan2 (z, sqrt (x * x + y * y))); - Z koordinātas ignorētas: grādi (kv. (X * x + y * y + z * z)); RETURN ST_MakePoint (lon, lat); END $ BODY $ LANGUAGE plpgsql IMMUTABLE; IZVEIDOT VAI NOMAINIET FUNKCIJU _cross_product (x1 pludiņš, y1 pludiņš, z1 pludiņš, x2 pludiņš, y2 pludiņš, z2 pludiņš, OUT x pludiņš, OUT y pludiņš, OUT z peldlīdzeklis) ATGRIEŽ REKORDU $ BODY $ BEGIN x: = y1 * z2 - z1 * y2; y: = z1 * x2 - x1 * z2; z: = x1 * y2 - y1 * x2; END $ BODY $ LANGUAGE plpgsql IMMUTABLE; IZVEIDOT VAI NOMAINIET FUNKCIJU vistuvākais_punkts_līnijai (līnijas ģeogrāfija (LineString), punktu ģeogrāfija (Punkts)) ATGRIEŽ ģeogrāfiju (Punkts) AS $ BODY $ DECLARE num_points vesels skaitlis; lv1 RECORD; lv2 RECORD; pv RECORD; f Ierakstīt; g RECORD; h RECORD; BEGIN num_points: = ST_NumPoints (līnija :: ģeometrija); IF num_points! = 2 THEN RAISE EXCEPTION 'Pašlaik tiek atbalstīti tikai divi punkti, bet līnijai ir %.', Num_points; END IF; lv1: = _punkt_to_cartesian (ST_PointN (līnija :: ģeometrija, 1), 1,0); lv2: = _punkt_to_cartesian (ST_PointN (līnija :: ģeometrija, 2), 1,0); pv: = _punkts_to_cartesian (punkts :: ģeometrija, 1,0); f: = _cross_product (lv1.x, lv1.y, lv1.z, lv2.x, lv2.y, lv2.z); g: = _cross_product (pv.x, pv.y, pv.z, fx, f.y, f.z); h: = _cross_product (f.x, f.y, f.z, g.x, g.y, g.z); RETURN _cartesian_to_point (h.x, h.y, h.z) :: ģeogrāfija; END $ BODY $ LANGUAGE plpgsql IMMUTABLE;

Jūs varētu izmantot līdzīgu uzlaušanu ST_Intersection ģeogrāfijai. Izskatītos šādi:

IZVEIDOT VAI Nomainiet FUNKCIJU st_closestpoint (ģeogrāfija, ģeogrāfija) ATGRIEŽ ģeogrāfiju kā $$ SELECT ģeogrāfiju (ST_Transform (ST_ClosestPoint (ST_Transform (geometry ($ 1)), _ST_BestSRID ($ 1, $ 2)), ST_Transform (geometry ($ 2)), _ST_Best, )), 4326)) $ $ VALODA sql NEPIECIEŠAMA STRIKTA CENA 500;