Query een lijst met addressen aan de hand van een postcode
Ik heb een vraagje. Ik heb een tabel in de database met restaurants waarbij de postcode opgeslagen staat.
Nu wil ik dat gebruikers kunnen zoeken op postcode naar deze restaurants aan de hand van postcode. Maar niet alleen de postcode, maar ook omliggende, dus stel iemand zoekt op 1528 dan moeten ook de omliggende eruit komen, wellicht een straal nodig? Denk aan zoiets als thuisbezorgd zoeken.
Ik heb gedacht aan het getal plus en min doen maar dit geeft niet overal het gewenste resultaat uiteraard.
Is hier een API voor of iets wat ik kan gebruiken?
Misschien Google API, maar hoe zou ik dit dan moeten laten werken.
Groeten,
Michel
- Ariën - op 08/04/2020 21:44:39:
Kijk eens naar dit topic:
https://www.phphulp.nl/php/forum/topic/postcode-straal-uitlezen/93810/
https://www.phphulp.nl/php/forum/topic/postcode-straal-uitlezen/93810/
Hey, dankjewel deze had ik niet gevonden! Dat delimiter gedeelte ken ik eigenlijk niet. De lat/long kan ik waarschijnlijk wel bepalen met een google api call. Die functie dat je de afstanden kan bepalen is wel slim als ik de delimiter begrijp.
( ik gebruik php symfony)
Gr
Michel
Alvast bedankt voor de hulp! Snelle reactie :)
Wordt geen standaard Doctrine oplossing vrees ik maar gelukkig zijn er de native queries.
Frank Nietbelangrijk op 08/04/2020 22:50:50:
Lees ditmaar..
Wordt geen standaard Doctrine oplossing vrees ik maar gelukkig zijn er de native queries.
Wordt geen standaard Doctrine oplossing vrees ik maar gelukkig zijn er de native queries.
Ja de SQL gaat ook prima lukken in Symfony. Het is meer dat ik de aanpak van dit en waar ik deze gegevens vandaan haal moet weten.
Wat je in wezen doet is de set van coordinaten waar je complexe berekeningen op uitvoert van tevoren zoveel mogelijk inperken zodat je alleen op de echte interessante informatie deze berekeningen uitvoert. Dit levert waarschijnlijk veel performancewinst op.
Thomas van den Heuvel op 08/04/2020 23:22:18:
Oeps, had een reactie aan het oude topic toegevoegd.
Wat je in wezen doet is de set van coordinaten waar je complexe berekeningen op uitvoert van tevoren zoveel mogelijk inperken zodat je alleen op de echte interessante informatie deze berekeningen uitvoert. Dit levert waarschijnlijk veel performancewinst op.
Wat je in wezen doet is de set van coordinaten waar je complexe berekeningen op uitvoert van tevoren zoveel mogelijk inperken zodat je alleen op de echte interessante informatie deze berekeningen uitvoert. Dit levert waarschijnlijk veel performancewinst op.
Het mag ook een vierkant zijn, dat is in principe geen probleem. Zoals thuisbezorgd of marktplaats moet het zijn dat je aan de hand van een postcode moet kunnen zoeken en resultaten krijgt met ook omliggende postcodes. Dus ik moet dus elke zoekopdracht postcode moet omzetten naar lat/long en dan ingevoerde lat/longs daarmee vergelijken. Is dit ook hoe ze dit bij zulke grotere sites aanpakken?
Michel Hamelink op 09/04/2020 10:55:18:
Het mag ook een vierkant zijn, dat is in principe geen probleem.
Daar gaat het niet om, het gaat om de efficiëntie en schaalbaarheid van jouw query.
Je moet het als volgt zien. Stel je hebt nu 100 filialen in jouw database staan. Vervolgens heb je een postcode die je omzet naar lat/lon (dit zou je trouwens ook kunnen bijhouden in je database zodat je dat niet elke keer opnieuw hoeft te bepalen) en sla je aan het rekenen. Voor al die 100 filialen moet een complexe rekensom worden uitgevoerd om te bepalen of er aan die conditie wordt voldaan en dus of dit in de resultaten moet worden opgenomen.
Stel dat er straks 10.000 filialen in jouw database zitten. Dan moet die berekening 10.000 keer worden uitgevoerd. Maar wat als je een extra conditie hebt die eerst het zoekgebied flink kleiner maakt, alsof je met een schaar een vierkantje knipt uit een hele grote kaart, op dat vierkantje staan misschien in zijn totaliteit maar 50 filialen. Alleen deze filialen worden vervolgens onderworpen aan de complexe rekensom, want de rest was al afgevallen omdat deze niet binnen het vierkantje vielen. Door deze voorselectie houd je de query schaalbaar.
En dan nog iets anders: het is niet per se erg dat er soms grote, langzame (en dit is iets anders dan "trage") queries worden uitgevoerd. Maar als dit onderdeel van zoekfunctionaliteit is (waar zit het meest nabijgelegen filiaal), dan zal zo'n query relatief vaak worden uitgevoerd. En het resultaat zal ook snel teruggeserveerd moeten worden want hier zitten mensen op te wachten. Je moet dus ook nadenken over de frequentie van aanroep en eventuele tijdsconstraints (maakt het uit dat de query lang duurt?), dit bepaalt tezamen mede het ontwerp van je queries en ook je database. Stel dat er 50 mensen tegelijkertijd aan het zoeken zijn en de database zit dan te stampen op de berekening van afstanden van 10.000 filialen? Dit heeft al snel een merkbaar sneeuwbaleffect.
Michel Hamelink op 09/04/2020 10:55:18:
Is dit ook hoe ze dit bij zulke grotere sites aanpakken?
Waarschijnlijk zullen zij iets soortgelijks gebruiken want uiteindelijk komt dit alles neer op afstanden berekenen in een of andere vorm.
Alleen lost dat een belangrijkere complicatie niet op: de reisafstand tussen twee geografische punten is vaak heel anders dan je hemelsbreed meet met een rechte lijn. Bijvoorbeeld omdat er een rivier tussen ligt en de dichtstbijzijnde brug 20 km omrijden is. Dat los je alleen op met een API zoals de Distance Matrix API van het Google Maps Platform.
Thomas van den Heuvel op 09/04/2020 16:19:07:
Daar gaat het niet om, het gaat om de efficiëntie en schaalbaarheid van jouw query.
Je moet het als volgt zien. Stel je hebt nu 100 filialen in jouw database staan. Vervolgens heb je een postcode die je omzet naar lat/lon (dit zou je trouwens ook kunnen bijhouden in je database zodat je dat niet elke keer opnieuw hoeft te bepalen) en sla je aan het rekenen. Voor al die 100 filialen moet een complexe rekensom worden uitgevoerd om te bepalen of er aan die conditie wordt voldaan en dus of dit in de resultaten moet worden opgenomen.
Stel dat er straks 10.000 filialen in jouw database zitten. Dan moet die berekening 10.000 keer worden uitgevoerd. Maar wat als je een extra conditie hebt die eerst het zoekgebied flink kleiner maakt, alsof je met een schaar een vierkantje knipt uit een hele grote kaart, op dat vierkantje staan misschien in zijn totaliteit maar 50 filialen. Alleen deze filialen worden vervolgens onderworpen aan de complexe rekensom, want de rest was al afgevallen omdat deze niet binnen het vierkantje vielen. Door deze voorselectie houd je de query schaalbaar.
En dan nog iets anders: het is niet per se erg dat er soms grote, langzame (en dit is iets anders dan "trage") queries worden uitgevoerd. Maar als dit onderdeel van zoekfunctionaliteit is (waar zit het meest nabijgelegen filiaal), dan zal zo'n query relatief vaak worden uitgevoerd. En het resultaat zal ook snel teruggeserveerd moeten worden want hier zitten mensen op te wachten. Je moet dus ook nadenken over de frequentie van aanroep en eventuele tijdsconstraints (maakt het uit dat de query lang duurt?), dit bepaalt tezamen mede het ontwerp van je queries en ook je database. Stel dat er 50 mensen tegelijkertijd aan het zoeken zijn en de database zit dan te stampen op de berekening van afstanden van 10.000 filialen? Dit heeft al snel een merkbaar sneeuwbaleffect.
Waarschijnlijk zullen zij iets soortgelijks gebruiken want uiteindelijk komt dit alles neer op afstanden berekenen in een of andere vorm.
Michel Hamelink op 09/04/2020 10:55:18:
Het mag ook een vierkant zijn, dat is in principe geen probleem.
Daar gaat het niet om, het gaat om de efficiëntie en schaalbaarheid van jouw query.
Je moet het als volgt zien. Stel je hebt nu 100 filialen in jouw database staan. Vervolgens heb je een postcode die je omzet naar lat/lon (dit zou je trouwens ook kunnen bijhouden in je database zodat je dat niet elke keer opnieuw hoeft te bepalen) en sla je aan het rekenen. Voor al die 100 filialen moet een complexe rekensom worden uitgevoerd om te bepalen of er aan die conditie wordt voldaan en dus of dit in de resultaten moet worden opgenomen.
Stel dat er straks 10.000 filialen in jouw database zitten. Dan moet die berekening 10.000 keer worden uitgevoerd. Maar wat als je een extra conditie hebt die eerst het zoekgebied flink kleiner maakt, alsof je met een schaar een vierkantje knipt uit een hele grote kaart, op dat vierkantje staan misschien in zijn totaliteit maar 50 filialen. Alleen deze filialen worden vervolgens onderworpen aan de complexe rekensom, want de rest was al afgevallen omdat deze niet binnen het vierkantje vielen. Door deze voorselectie houd je de query schaalbaar.
En dan nog iets anders: het is niet per se erg dat er soms grote, langzame (en dit is iets anders dan "trage") queries worden uitgevoerd. Maar als dit onderdeel van zoekfunctionaliteit is (waar zit het meest nabijgelegen filiaal), dan zal zo'n query relatief vaak worden uitgevoerd. En het resultaat zal ook snel teruggeserveerd moeten worden want hier zitten mensen op te wachten. Je moet dus ook nadenken over de frequentie van aanroep en eventuele tijdsconstraints (maakt het uit dat de query lang duurt?), dit bepaalt tezamen mede het ontwerp van je queries en ook je database. Stel dat er 50 mensen tegelijkertijd aan het zoeken zijn en de database zit dan te stampen op de berekening van afstanden van 10.000 filialen? Dit heeft al snel een merkbaar sneeuwbaleffect.
Michel Hamelink op 09/04/2020 10:55:18:
Is dit ook hoe ze dit bij zulke grotere sites aanpakken?
Waarschijnlijk zullen zij iets soortgelijks gebruiken want uiteindelijk komt dit alles neer op afstanden berekenen in een of andere vorm.
Oke dan begin ik het te begrijpen, nog best complex. Er is niet gevraagd om een kaart mogelijkheid. Dat lat long opslaan van bepaalde postcodes in de database is een slimme, scheelt api calls en snelheid. Hoe kan ik een query opzetten die flink kleiner is.
Is het rendabel om via het opgezochte adres de lat/long te krijgen (geo van google) en dan doormiddel van een eerder genoemde berekening die afstand te meten tussen de ingevoerde filialen (die ook lat/long hebben). Dan nog steeds misschien het zoekgebied kleiner maken, nog geen idee hoe ik dat dan wil aanpakken.
Michel Hamelink op 10/04/2020 13:09:04:
Is het rendabel om via het opgezochte adres de lat/long te krijgen (geo van google) en dan doormiddel van een eerder genoemde berekening die afstand te meten tussen de ingevoerde filialen (die ook lat/long hebben).
Die vraag beantwoordt je al min of meer zelf als je je bedient van postcodes:
Michel Hamelink op 10/04/2020 13:09:04:
Dat lat long opslaan van bepaalde postcodes in de database is een slimme, scheelt api calls en snelheid.
Michel Hamelink op 10/04/2020 13:09:04:
Hoe kan ik een query opzetten die flink kleiner is.
Weet niet precies wat je hiermee bedoelt, maar je kunt dit vraagstuk opdelen in delen. Je hebt het stuk waar je postcodes (eenmalig) omzet naar lat/lon, je hebt het stuk waarbij je (in PHP) een vierkant om de straal heenzet waarbij je twee coordinatenparen berekent en je hebt het stuk waarbij je daadwerkelijk afstanden berekent, en daarvoor heb je al een lap SQL. Het "enige" wat je nog moet doen is deze alle combineren.
Michel Hamelink op 10/04/2020 13:09:04:
Dan nog steeds misschien het zoekgebied kleiner maken, nog geen idee hoe ik dat dan wil aanpakken.
Zoals eerder aangegeven is hier (gegeven een lat/lon coordinaat en een afstand in een bepaalde richting, bereken op grond hiervan een nieuw lat/lon paar) code voor te vinden, misschien heb ik deze zelf nog, ik zal hier eens naar zoeken.
Dit doe je dus op grond van het middelpunt van de cirkel (de postcode die je opgeeft). De linker bovenhoek van het omvattende vierkant is (middelPuntY - straalY, middelPuntX - straalX) en de rechter onderhoek is (middelPuntY + straalY, middelPuntX + straalX), waarbij straalX en straalY de berekende lat/lon verschillen zijn. Zoals in de andere thread aangegeven doe je met behulp van deze coordinaten een voorselectie op de filiaalkandidaten. Vervolgens inspecteer je enkel de filialen die binnen het vierkant en binnen de gewenste straal vallen.
En zoals @Ward terecht aangaf, er is waarschijnlijk nog steeds een verschil tussen de hemelsbrede afstand en afgelegde weg van begin- naar eindpunt.
Je zou ook kunnen kijken of het voorstel van @Ward meer soelaas biedt. Aangezien het een apart datatype betreft is dit waarschijnlijk vrij ver uitgeoptimaliseerd. Je zou ook beide kunnen uitproberen, en dan kijken welke sneller is.
Gewijzigd op 10/04/2020 17:03:34 door Thomas van den Heuvel
Er zijn een aantal eenvoudige functies waarmee je gegeven een lat- of lon-coordinaat (in graden) en een afstand een nieuw lat- of lon-coordinaat kunt berekenen.
De bron en de wiskundige onderbouwing zul je maar voor lief moeten nemen (weet niet meer waar ik deze vandaan heb gehaald en mijn geodesische kennis is op zijn zachtst gezegd roestig :p), maar dit levert redelijk nauwkeurige resultaten, zoals we zullen zien.
Deze reactie is voornamelijk bedoeld om te laten zien hoe je het omvattende vierkant bouwt. Je zou voor de gein dit deel uit het WHERE-statement van je uiteindelijke query (tijdelijk, uiteraard) kunnen weglaten om te zien in hoeverre dit de performance beïnvloedt. Uiteraard hangt dit af van het aantal aanwezige locaties en hoe groot je je zoekstraal maakt, maar als ik uit kan gaan van de paar tests die ik heb gedaan (o.a. op mijn relatief trage Raspberry Pi) heeft zo'n bounding box een aanzienlijke (positieve) invloed op de querysnelheid.
Waar mogelijk zal ik proberen wat extra toelichting te geven die deels afgeleid zijn uit het schaarse commentaar wat ik ergens in een vaag ver verleden heb toegevoegd.
Allereerst wat definities, zoals ik ze begrijp. Kan er mogelijk helemaal naast zitten, but here goes :p.
De breedtegraad (latitude) geeft de noord-zuid positie aan. Deze ligt tussen 0 en 90 graden (positief of negatief, of met de toevoeging noorderbreedte (+) of zuiderbreedte (-)).
De lengtegraad (longitude) geeft de oost-west positie aan. Kan zowel positief als negatief of met oosterlengte (+) en westerlengte (-) en ligt tussen de 0 en 180 graden.
De functie voor het berekenen van een nieuwe breedtegraad, gegeven een breedtegraad $lat en een afstand in meters ($diff positief, richting het noorden of negatief, richting het zuiden) luidt als volgt:
Die 111111 is een of andere constante die meestal wel opgaat, maar blijkbaar is 111319.5 beter voor hogere breedtegraden, dus wanneer je dichter bij de polen komt, maar 111111 volstaat meestal wel. Dit waarschijnlijk vanwege, of zijdelings gerelateerd aan het feit dat de wereld geen perfecte bol is, maar meer een soort van ellips of ei.
Als je dus een nieuwe breedtegraad wilt uitrekenen die 5 km noordelijker ligt, dan doe je dat dus als volgt:
Code (php)
1
2
3
2
3
<?php
$newLat = diffLat($currentLat, 5000);
?>
$newLat = diffLat($currentLat, 5000);
?>
De functie voor het berekenen van een nieuwe lengtegraad, gegeven een huidige lengtegraad $lon, een huidige breedtegraad (want dit bepaalt mede de nieuwe lengtegraad) $lon, en een afstand in meters ($diff positief, richting het oosten, of negatief, richting het westen) luidt als volgt:
Code (php)
Waarbij cos($lat) * 111111 blijkbaar overeen komt met 1 graad; ik vertrouw de persoon die dit zei volledig op zijn/haar woord :p.
Wanneer je een nieuwe lengtegraad wilt berekenen die 7,5 km naar het westen ligt, dan doe je dat dus als volgt:
Code (php)
1
2
3
2
3
<?php
$newLon = diffLon($currentLat, $currentLon, -7500);
?>
$newLon = diffLon($currentLat, $currentLon, -7500);
?>
Makkelijk toch? :p
Dan zou je een test tabelletje kunnen bouwen waar je een (grote) hoeveelheid coordinaten ingooit.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
CREATE TABLE `coordinates` (
`crd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`crd_lat` float(9,6) NOT NULL,
`crd_lon` float(10,6) NOT NULL,
PRIMARY KEY (`crd_id`),
KEY `index_lat` (`crd_lat`),
KEY `index_lon` (`crd_lon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`crd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`crd_lat` float(9,6) NOT NULL,
`crd_lon` float(10,6) NOT NULL,
PRIMARY KEY (`crd_id`),
KEY `index_lat` (`crd_lat`),
KEY `index_lon` (`crd_lon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Zoals eerder aangegeven ligt een breedtegraad tussen de 0 en 90 graden (max 2 cijfers), dit kan een positief of negatief getal zijn (sign, 1 cijfer). Vervolgens zul je een beslissing moeten nemen over de precisie. Voor dit voorbeeld, en waarschijnlijk ook voor de meeste zoekfunctionaliteit, volstaan 6 decimalen. Wanneer je een hogere precisie nodig hebt zul je je mogelijk ook moeten bedienen van andere, nauwkeurigere meetfuncties. De breedtegraad neemt dus maximaal 9 cijfers in, waarvan 6 decimalen. Op een soortgelijke manier bepaal je de ruimte voor de lengtegraad (1 sign, 3 cijfers 0-180 graden, 6 decimalen = 10 cijfers). Vervolgens maak je indexen aan op zowel de breedtegraad-kolom alsook de lengtegraad-kolom zodat je snel op kunt zoeken of iets in de breedte of de lengte in een bepaald interval ligt. Hier was het uiteindelijk per slot van rekening om te doen.
Voor deze test neem ik het Centraal Station te Eindhoven als uitgangspunt (51.4434401, 5.479096) en vul ik de coordinates-tabel met coordinaat-paren die ongeveer op een kilometer afstand van elkaar liggen. Hierbij maak ik dankbaar gebruik van de eerder genoemde functies:
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
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
<?php
// vul in <host>, <database> et cetera de juiste gegevens in, uiteraard
$dsn = 'mysql:host=<host>;dbname=<database>;charset=utf8';
$username = '<user>';
$password = '<password>';
try {
$db = new PDO($dsn, $username, $password, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => true,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
try {
$db->query('TRUNCATE coordinates;'); // maak de tabel leeg
$st = $db->prepare('INSERT INTO coordinates (crd_lat, crd_lon) VALUES (?, ?)'); // creeer prepared statement
// vul de tabel
$db->beginTransaction();
for ($x = -99; $x < 100; $x++) {
for ($y = -99; $y < 100; $y++) {
$lat = diffLat(51.4434401, $y * 1000);
$lon = diffLon(51.4434401, 5.479096, $x * 1000);
$st->execute(array($lat, $lon));
}
}
$db->commit();
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
?>[end]
// vul in <host>, <database> et cetera de juiste gegevens in, uiteraard
$dsn = 'mysql:host=<host>;dbname=<database>;charset=utf8';
$username = '<user>';
$password = '<password>';
try {
$db = new PDO($dsn, $username, $password, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => true,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
try {
$db->query('TRUNCATE coordinates;'); // maak de tabel leeg
$st = $db->prepare('INSERT INTO coordinates (crd_lat, crd_lon) VALUES (?, ?)'); // creeer prepared statement
// vul de tabel
$db->beginTransaction();
for ($x = -99; $x < 100; $x++) {
for ($y = -99; $y < 100; $y++) {
$lat = diffLat(51.4434401, $y * 1000);
$lon = diffLon(51.4434401, 5.479096, $x * 1000);
$st->execute(array($lat, $lon));
}
}
$db->commit();
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
?>[end]
(en ja, ik heb eindelijk een nut gevonden voor prepared statements :p)
Nu wordt het tijd om een bounding box te gaan bouwen en wat dingen te gaan testen.
Stel dat het Centraal Station tevens het uitgangspunt is voor jouw zoekopdracht, en dat je in een straal van ~12 kilometer wilt zoeken.
Hiervoor bouwen we dus eerst het vierkant om dit middelpunt heen. Hiertoe pakken we de linker onderhoek van het vierkant, en de rechter bovenhoek. Deze bevatten respectievelijk de laagste en hoogste coordinatenparen, zodat je hier makkelijk een BETWEEN op kunt toepassen. Hierbij gaan we gemakshalve uit van gevalideerde gegevens, om het verhaal niet onnodig complexer te maken.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$searchRadius = 12000; // metres
$searchLat = 51.443440;
$searchLon = 5.479096;
$precision = 6; // decimals
// bottom left Y
$y1 = round(diffLat($searchLat, -$searchRadius), $precision);
// bottom left X
$x1 = round(diffLon($searchLat, $searchLon, -$searchRadius), $precision);
// top right Y
$y2 = round(diffLat($searchLat, $searchRadius), $precision);
// top right X
$x2 = round(diffLon($searchLat, $searchLon, $searchRadius), $precision);
?>
$searchRadius = 12000; // metres
$searchLat = 51.443440;
$searchLon = 5.479096;
$precision = 6; // decimals
// bottom left Y
$y1 = round(diffLat($searchLat, -$searchRadius), $precision);
// bottom left X
$x1 = round(diffLon($searchLat, $searchLon, -$searchRadius), $precision);
// top right Y
$y2 = round(diffLat($searchLat, $searchRadius), $precision);
// top right X
$x2 = round(diffLon($searchLat, $searchLon, $searchRadius), $precision);
?>
Meestal is het geen goede gewoonte om tussentijds af te ronden, maar omdat je deze gegevens aan de database gaat voeren is dit wel verstandig om in dezelfde precisie te werken, anders begint MySQL te emmeren over truncated values in een heleboel warnings :p.
De eerste query met enkel het vierkant wordt dan dus zoiets:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
<?php
$query =
"SELECT crd_id, crd_lat, crd_lon
FROM coordinates
WHERE (crd_lat BETWEEN ".$y1." AND ".$y2.")
AND (crd_lon BETWEEN ".$x1." AND ".$x2.")";
?>
$query =
"SELECT crd_id, crd_lat, crd_lon
FROM coordinates
WHERE (crd_lat BETWEEN ".$y1." AND ".$y2.")
AND (crd_lon BETWEEN ".$x1." AND ".$x2.")";
?>
Dit levert je als het goed is 576 resultaten op in enkele miliseconden.
Dan zou je de functie uit de andere thread over kunnen nemen. Deze heb ik voor mijn gebruik een beetje gepimpt zodat 'ie lekker(der) werkt met deze informatie, heb ook gelijk wat spellingsfouten eruit gehaald :p.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP FUNCTION IF EXISTS GetDistance;
DELIMITER $$
CREATE FUNCTION GetDistance(orgLat FLOAT(9,6), orgLong FLOAT(10,6), destLat FLOAT(9,6), destLong FLOAT(10,6)) RETURNS FLOAT DETERMINISTIC
BEGIN
RETURN 6371 *
acos(cos(radians(orgLat) ) *
cos(radians(destLat)) *
cos(radians(destLong) - radians(orgLong)) + sin(radians(orgLat))
* sin(radians(destLat)));
END$$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION GetDistance(orgLat FLOAT(9,6), orgLong FLOAT(10,6), destLat FLOAT(9,6), destLong FLOAT(10,6)) RETURNS FLOAT DETERMINISTIC
BEGIN
RETURN 6371 *
acos(cos(radians(orgLat) ) *
cos(radians(destLat)) *
cos(radians(destLong) - radians(orgLong)) + sin(radians(orgLat))
* sin(radians(destLat)));
END$$
DELIMITER ;
Vervolgens kun je de query uitbreiden met een berekening van de straal binnen het aangepaste zoekgebied:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
<?php
$query =
"SELECT crd_id, crd_lat, crd_lon, GetDistance(".$searchLat.", ".$searchLon.", crd_lat, crd_lon) AS distance
FROM coordinates
WHERE (crd_lat BETWEEN ".$y1." AND ".$y2.")
AND (crd_lon BETWEEN ".$x1." AND ".$x2.")
AND GetDistance(".$searchLat.", ".$searchLon.", crd_lat, crd_lon) < ".($searchRadius/1000);
?>
$query =
"SELECT crd_id, crd_lat, crd_lon, GetDistance(".$searchLat.", ".$searchLon.", crd_lat, crd_lon) AS distance
FROM coordinates
WHERE (crd_lat BETWEEN ".$y1." AND ".$y2.")
AND (crd_lon BETWEEN ".$x1." AND ".$x2.")
AND GetDistance(".$searchLat.", ".$searchLon.", crd_lat, crd_lon) < ".($searchRadius/1000);
?>
NB GetDistance werkt met kilometers, dit zou je zelf nog aan kunnen passen.
Dit levert je als het goed is 437 records op, en de query zou nog steeds supersnel moeten zijn.
Dat was het zo'n beetje, van begin tot eind.
Je gaf aan dat er geen kaartmogelijkheid wordt gevraagd, maar als je dit combineert met bijvoorbeeld Google Maps, dan kun je dit alles visueel maken, hiermee kun je makkelijker en intuitiever testen.
En het levert ook leuke plaatjes op.
Doe je dit niet (waarde op true) dan vuurt PDO gewone INSERT-queries op de database af. De prepared statement functionaliteit beperkt zich in dat geval enkel tot de PDO-laag en maakt dan geen gebruik van aanwezige optimalisaties.
Wanneer je deze waarde op false zet zal er eerst een echt querytemplate worden verstuurd, en vervolgens gaat er bij INSERTs nagenoeg alleen maar INSERT-data over de lijn en geen complete, op zichzelf staande queries. Dit geeft wat extra performancewinst.
Dit is eigenlijk het gedrag wat je standaard zou verwachten bij databases die prepared statements ondersteunen, maar PDO is niet geschreven voor een specifieke database, dus zul je goed moeten kijken naar hoe je je driver (PDO_MYSQL) configureert en gebruikt.
$sql = 'SELECT *, ( 6371 * acos( cos( radians(:lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(lat)) ) )
AS distance FROM restaurant HAVING distance < :radius ORDER BY distance';
Zo krijg ik alle restaurants in de DB binnen een radius in km.
Thx!
Super bedankt, het werkt helemaal! ik heb het zelfs nagecheckt door eerst te kijken hoeveel resultaten ik kreeg met het vierkant en vervolgens vergeleken met het aantal resultaten van de straal en daar zat een klein verschil in (namelijk de coördinaten die binnen het vierkant vielen maar buiten de cirkel, logisch).
Ik gebruik het op mijn site die ik heb gemaakt voor een dierenwelzijn stichting om geregistreerde dieren zoekers of buddy's of gastgezinnen te koppelen aan dieren. Nogmaals bedankt,
Met vriendelijke groet,
Paul Spaan
P.S. Nu werk ik ook met postcodes, dit is simpelweg op te lossen met een gratis sql database bestand met alle postcodes en bijbehorende coördinaten die ik ergens (weet niet meer precies waar :( ) heb kunnen downloaden, bij elke nieuwe registratie bij onze dierenwelzijn organisatie zoekt hij passende coördinaten bij de ingevoerde postcode die op worden geslagen bij de respectievelijke buddy bijvoorbeeld, en nu zoekt mijn pagina naar de coördinaten van alle buddy's die binnen de cirkel liggen door te zoeken op een postcode (die dus vervolgens wordt omgezet naar coördinaten).
Dus houd je lijst uptodate.