Po përpiqem të krijoj një skript ku njerëzit mund të futin gjerësinë dhe gjatësinë gjeografike për të treguar vendin më të afërt nga tabela e vendeve. Përdorimi i "like" ose "=" do të tregojë marrjen e rezultateve me përputhje të saktë. Por unë dua të jem në gjendje të tregoj vendin më të afërt nëse nuk ka përputhje të saktë. A ka ndonjë mënyrë për të llogaritur distancën në MySQL? Ju lutem ndihmë. Faleminderit
Llogaritni distancën duke përdorur gjerësinë dhe gjatësinë gjeografike në mysql
Përgjigjet:
Ju mund të përdorni Formulën Vincenty https://en.wikipedia.org/wiki/Vincenty%27s_formulae .
Në MySql mund të krijoni një funksion për të llogaritur distancën me këtë kod:
DELIMITER $$
DROP FUNCTION IF EXISTS vincenty_distance$$
CREATE FUNCTION vincenty_distance(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE) RETURNS DOUBLE
BEGIN
DECLARE TO_RAD DOUBLE;
DECLARE a INT;
DECLARE b DOUBLE;
DECLARE f DOUBLE;
DECLARE L DOUBLE;
DECLARE U1 DOUBLE;
DECLARE U2 DOUBLE;
DECLARE sinU1 DOUBLE;
DECLARE cosU1 DOUBLE;
DECLARE sinU2 DOUBLE;
DECLARE cosU2 DOUBLE;
DECLARE lambda DOUBLE;
DECLARE lambdaP DOUBLE;
DECLARE iterLimit INT;
DECLARE sinLambda DOUBLE;
DECLARE cosLambda DOUBLE;
DECLARE sinSigma DOUBLE;
DECLARE cosSigma DOUBLE;
DECLARE sigma DOUBLE;
DECLARE sinAlpha DOUBLE;
DECLARE cosSqAlpha DOUBLE;
DECLARE cos2SigmaM DOUBLE;
DECLARE C DOUBLE;
DECLARE D DOUBLE;
DECLARE E DOUBLE;
DECLARE uSq DOUBLE;
DECLARE deltaSigma DOUBLE;
DECLARE s DOUBLE;
SET TO_RAD = pi() / 180; /*converts degree to raians*/
SET a = 6378137;
SET b = 6356752.3142;
SET f = 1 / 298.257223563; /* WGS-84 ellipsoid params*/
SET L = (lon2-lon1) * TO_RAD;
SET U1 = atan((1 - f) * tan(lat1 * TO_RAD));
SET U2 = atan((1 - f) * tan(lat2 * TO_RAD));
SET sinU1 = sin(U1);
SET cosU1 = cos(U1);
SET sinU2 = sin(U2);
SET cosU2 = cos(U2);
SET lambda = L;
SET iterLimit = 100;
REPEAT
SET sinLambda = sin(lambda);
SET cosLambda = cos(lambda);
SET sinSigma = sqrt((cosU2 * sinLambda) * (cosU2 * sinLambda) + (cosU1 * sinU2 - sinU1 * cosU2 * cosLambda) * (cosU1 * sinU2 - sinU1 * cosU2 * cosLambda));
IF 0 = sinSigma THEN
RETURN 0; /* co-incident points*/
END IF;
SET cosSigma = sinU1 * sinU2 + cosU1 * cosU2 * cosLambda;
SET sigma = atan2(sinSigma, cosSigma);
SET sinAlpha = cosU1 * cosU2 * sinLambda / sinSigma;
SET cosSqAlpha = 1 - sinAlpha * sinAlpha;
IF (cosSqAlpha = 0) THEN
SET cos2SigmaM = 0; /* equatorial line: cosSqAlpha = 0 (§6)*/
ELSE
SET cos2SigmaM = cosSigma - 2 * sinU1 * sinU2 / cosSqAlpha;
SET C = f / 16 * cosSqAlpha * (4 + f * (4 - 3 * cosSqAlpha));
END IF;
SET lambdaP = lambda;
SET lambda = L + (1 - C) * f * sinAlpha * (sigma + C * sinSigma * (cos2SigmaM + C * cosSigma * (-1 + 2 * cos2SigmaM * cos2SigmaM)));
SET iterLimit = iterLimit - 1;
UNTIL (abs(lambda - lambdaP) <= 0.0000000001 && iterLimit = 0) END REPEAT;
SET uSq = cosSqAlpha * (a * a - b * b) / (b * b);
SET D = 1 + uSq / 16384 * (4096 + uSq * (-768 + uSq * (320 - 175 * uSq)));
SET E = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)));
SET deltaSigma = E * sinSigma * (cos2SigmaM + E / 4 * (cosSigma * (-1 + 2 * cos2SigmaM * cos2SigmaM) - E / 6 * cos2SigmaM * (-3 + 4 * sinSigma * sinSigma) * (-3 + 4 * cos2SigmaM * cos2SigmaM)));
SET s = b * D * (sigma - deltaSigma);
RETURN round(s, 3); /* round to 1mm precision*/
END$$
DELIMITER ;
Për ta përdorur, thjesht duhet të thërrisni funksionin vincenty_distance me parametrat e gjerësisë dhe gjatësisë, shembull:
SELECT vincenty_distance(47.6593,10.97647,46.2512010,10.069972);
Formula e Vincentit është një nga metodat më të sakta për të llogaritur distancën, sepse ajo merr figurën e tokës si një sferoid i shtrirë.
Algoritmi kthen një distancë në metra me një saktësi prej 1 mm.
Përndryshe, nëse preferoni një llogaritje më të shpejtë por të pasaktë (e vlefshme vetëm në distancë të vogël), përdorni një teoremë të pitagorës: Llogaritja e distancës (pythagoras) dhe numri i ekzekutimit në pyetjen sql
Ka shumë formula të tjera për të llogaritur distancën, ju duhet të zgjidhni një sipas saktësisë dhe performancës që ju nevojitet:
https://en.wikipedia.org/wiki/Geographical_distance
Distanca e rrethit të madh të MySQL (formula Haversine)
[ERROR in query 2] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
. Kam përdorur saktësisht të njëjtin funksion më parë në serverin tim të mëparshëm pa problem. Tani kur dua të migroj në Amazon RDS, shfaqet ai gabim 21.02.2018Parameter Groups
të ri dhe vendos vlerënlog_bin_trust_function_creators
në 1. Më pas vendos shembullin tim MySQL RDS që të përdorë grupin e parametrave të krijuar rishtazi, ta rindiz atë dhe të ridrejtojë këtë pyetje -_- 21.02.2018