Tijden veranderen
Afstand tussen verschillende coordinaten uitrekenen
Ik heb een database met alle plaatsnamen inclusief coordinaten van Nederland.
Elke plaats heeft zijn eigen webpagina. Op deze webpagina wil ik de 10 dichtstbij gelegen steden inclusief afstand opsommen.
Bijvoorbeeld: We bekijken de pagina van Den Haag. Dan moeten de 10 dichstbij gelegen steden/dorpen waarschijnlijk Rijswijk, Voorburg, Delft, etc zijn.
Ik heb onderstaand zelf kunnen uitzoeken. Zou iemand mij kunnen helpen om de juiste query te maken inclusief ORDER BY ASC en LIMIT 10. ($query = "SELECT * FROM db LIMIT 10";)
Ik hoor graag of jullie meer uitleg nodig hebben.
Groeten Tony
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
//connect
$query = "SELECT * FROM db WHERE name = '" . mysql_real_escape_string( name ) . "'";
$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while ($row = mysql_fetch_assoc($query)){
//dit zijn de begin coordinaten
$original_lat = $row['lat'];
$original_long = $row['long'];
}}
?>
//connect
$query = "SELECT * FROM db WHERE name = '" . mysql_real_escape_string( name ) . "'";
$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while ($row = mysql_fetch_assoc($query)){
//dit zijn de begin coordinaten
$original_lat = $row['lat'];
$original_long = $row['long'];
}}
?>
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?php
function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2) {
$theta = $longitude1 - $longitude2;
$miles = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
$miles = acos($miles);
$miles = rad2deg($miles);
$miles = $miles * 60 * 1.1515;
$feet = $miles * 5280;
$yards = $feet / 3;
$kilometers = $miles * 1.609344;
$meters = $kilometers * 1000;
return compact('kilometers');
}
//connect
//hier krijgen we 10 results zonder de juiste ORDER BY...
$query = "SELECT * FROM db LIMIT 10";
$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while ($row = mysql_fetch_assoc($query)){
$destination_lat = $row['lat'];
$destination_long = $row['long'];
$plaatsnaam = $row['plaatsnaam'];
$point1 = array('lat' => $original_lat, 'long' => $original_long);
$point2 = array('lat' => $destination_lat, 'long' => $destination_long);
$distance = getDistanceBetweenPointsNew($point1['lat'], $point1['long'], $point2['lat'], $point2['long']);
foreach ($distance as $unit => $value) {
echo $plaatsnaam. ': '.number_format($value,1).'<br />';
}
}}
?>
function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2) {
$theta = $longitude1 - $longitude2;
$miles = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
$miles = acos($miles);
$miles = rad2deg($miles);
$miles = $miles * 60 * 1.1515;
$feet = $miles * 5280;
$yards = $feet / 3;
$kilometers = $miles * 1.609344;
$meters = $kilometers * 1000;
return compact('kilometers');
}
//connect
//hier krijgen we 10 results zonder de juiste ORDER BY...
$query = "SELECT * FROM db LIMIT 10";
$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while ($row = mysql_fetch_assoc($query)){
$destination_lat = $row['lat'];
$destination_long = $row['long'];
$plaatsnaam = $row['plaatsnaam'];
$point1 = array('lat' => $original_lat, 'long' => $original_long);
$point2 = array('lat' => $destination_lat, 'long' => $destination_long);
$distance = getDistanceBetweenPointsNew($point1['lat'], $point1['long'], $point2['lat'], $point2['long']);
foreach ($distance as $unit => $value) {
echo $plaatsnaam. ': '.number_format($value,1).'<br />';
}
}}
?>
Toevoeging op 20/11/2012 20:31:33:
Kan niemand mij helpen of heb ik mijn vraag niet goed uitgelegd?
Zijn die queries van stack overflow vooruit te branden? Ik zou namelijk denken dat de database voor alle steden/dorpen in heel nederland eerst een afstand gaat uitrekenen en daarna pas kan selecteren. Dat leek me nogal traag, maar misschien was ik te pessimistich, dan kun je deze reactie negeren.
Mijn idee voor een snellere zoekfunctie was om eerst alle steden/derpen binnen bepaalde begrenzingen op te vragen, bijvoorbeeld overeenkomstig een gebied van (ruim) 5 kilometer in noord, oost, zuid, en westelijke richting. Daarvan kun je de grenzen voor longitude en latitude in PHP uitrekenen (kom ik op terug). Als je in de database eerst een gecombineerde index aanmaakt op longitude en latitude zal een query die daar met AND en letterlijke getallen op selecteert heel snel gaan.
Nu je die query van stack overflow toch hebt is het het simpelst om die aan te passen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
$query = "SELECT *,
( 3959 * acos( cos( radians('$slatitude') ) *
cos( radians( latitude ) ) *
cos( radians( longitude ) -
radians('$slongitude') ) +
sin( radians('$slatitude') ) *
sin( radians( latitude ) ) ) )
AS distance
FROM cities
WHERE (longitude BETWEEN '$longMin' AND '$longMax')
AND (latitude BETWEEN '$latMin' AND '$latMax')
ORDER BY distance ASC LIMIT 0, 10
( 3959 * acos( cos( radians('$slatitude') ) *
cos( radians( latitude ) ) *
cos( radians( longitude ) -
radians('$slongitude') ) +
sin( radians('$slatitude') ) *
sin( radians( latitude ) ) ) )
AS distance
FROM cities
WHERE (longitude BETWEEN '$longMin' AND '$longMax')
AND (latitude BETWEEN '$latMin' AND '$latMax')
ORDER BY distance ASC LIMIT 0, 10
Stel dat dat 5 rows oplevert. Dan vergroot je het gebied aan elke kant, bijvoorbeeld met nog eens vijf kilometer.
Stel dat de 10e row dan een afstand heeft van 11 kilometer. Dan zou het kunnen zijn dat er buiten het gebied dat je geselecteerd hebt nog steden/dorpen liggen die dichterbij zijn. Vergroot dan het gebied met nog eens 1 kilometer in elke richting.
Nu kun je er zeker van zijn dat er geen steden/dorpen meer in de database staan die op een kortere afstand liggen dan 11 kilometer. De 10e row lag al op 11 kilometer dus de eerste 10 rows zijn het antwoord.
Wat misschien nog wel wat hoofdbrekens kost is de begrenzingen ($longMin etc) te bepalen van een 'vierkant' van bijvoorbeeld vijf kilometer in de vier windrichtingen. De begrenzing geeft immers een soort van trapezium waarvan de evenwijdige zijden ook nog een beetje krom zijn. Gelukkig is nederland vrij klein, en ligt het niet al te noordelijk. Het moet daarom wel te doen zijn om een vermenigvuldigingsfactor te vinden waarmee je grenzen van long en lat kunt bepalen van een gebied dat net iets groter is, zodat een circel met een straal van bijvoorbeeld 5 kilometers er altijd helemaal binnen valt, waar ook in nederland. Dan krijgt de database misschien een paar steden/dorpen extra om te sorteren, maar veel meer dan pak weg 30 zullen het er denk ik niet worden, zodat je toch wel snel resultaat hebt.
Succes!
Gewijzigd op 26/11/2012 11:23:14 door Henk Verhoeven
Ik heb de formule die jij gebruikt in een Mysql functie staan, en daarmee duurt de query 0.07 sec.
Ik vind dat verwaarloosbaar binnen web toepassingen.
Het wordt iets anders als je bijvoorbeeld met een postcode tabel moet gaan werken, en dan een selectie binnen een bepaalde afstand wilt hebben, dan kan je beter zoals Henk aangeeft de selectie beperken door een vierkant over de selectie te leggen.