3 dichtsbijzijnde postcodes ophalen

Door Furio Scripting op 30-08-2015 14:17 gewijzigd op 30-08-2015 15:52

2.878 views

Beste forumleden,

Voor een offerte systeem wil ik de 3 dichts bijzijnde bedrijven ophalen die voldoen aan de aanvragers eisen en wensen. Dit gaat op basis van de postcode.

Dit is de code die ik tot nu toe heb:

<?php     $database = new database;
    $q = 'SELECT * FROM klanten';
    $result = $database->query($q);

    $afstand = array();
    while ($rows = mysqli_fetch_assoc($result))
    {
       
            // check postcode afstanden.
            
            $afstand[] =  verkrijgKmAfstandsTussenPostcodes($postcode,$woonplaats,$rows['postcode'],$rows['plaats']);
 
    }
       
       ksort( $afstand );
       array_slice( $afstand, 0, 3, true );
       
       foreach( array_slice( $afstand, 0, 3, true ) as $dichtsBijzijndeBedrijven)
       {
            echo $dichtsBijzijndeBedrijven;
       } ?>

Hij weergeeft nu de drie dichtsbijzijnde afstanden van alle bedrijven maar ik wil graag de 3 bedrijf ids hebben zodat ik daar verder mee kan werken, hoe krijg ik dus ipv de afstand de klant ids maar wel op basis van de dichtsbijzijnde postcodes.

Thomas van den Heuvel

30-08-2015 15:41

Geen idee.

Wat is de return waarde van verkrijgKmAfstandsTussenPostcodes()? De naam doet vermoeden dat dit enkel afstanden zijn.

Als je daar nou eens een paar van maakt? bedrijfs_id => afstand?

Furio Scripting

30-08-2015 15:51

Thomas van den Heuvel op 30/08/2015 15:41:37

Geen idee.

Wat is de return waarde van verkrijgKmAfstandsTussenPostcodes()? De naam doet vermoeden dat dit enkel afstanden zijn.

Als je daar nou eens een paar van maakt? bedrijfs_id => afstand?

Ik heb het werkend gekregen via:

<?php    asort($afstand , SORT_NUMERIC);
       
      
       foreach(array_slice($afstand, 0, 3, true) as $key => $value)
       { ?>

Thomas van den Heuvel

30-08-2015 15:59 gewijzigd op 30-08-2015 16:02

Dat is knap - hoe weet verkrijgKmAfstandsTussenPostcodes welk bedrijfs-id dit is als je enkel $rows['postcode'] en $rows['plaats'] meegeeft? Weet je zeker dat dat de juiste bedrijfs-ids zijn?

Voer je binnen die functie nog meer queries uit (van informatie die je in feite al hebt)? Lekker efficient dan :/.

Ger van Steenderen

31-08-2015 21:11 gewijzigd op 31-08-2015 21:12

Je kan de afstand ook in SQL laten berekenen via een stored function


DELIMITER $$
CREATE FUNCTION GetDistance(orgLat DECIMAL(10,6),
			    orgLong DECIMAL(10,6),
			    destLat DECIMAL(10,6),
			   destLong DECIMAL(10,6)) RETURNS int(5)
BEGIN
    DECLARE dist INT(11);
  SET dist := ROUND(6371 *
        acos(cos(radians(orgLat) ) *
        cos(radians(destLat)) *
        cos(radians(destLong) - radians(orgLong)) + sin(radians(orgLat))
        * sin(radians(destLat))));
    RETURN dist;
END$$
DELIMITER ;

Dit moet je eenmalig uitvoeren in je SQL tool.
Wil je dit via PMA doen kan dit (in de nieuwste versie) door de database te selecteren en dan tab routines,
je moet dan wel de eerste 4 regels en de laatste regel weg te halen en ook de $$ achter END.

Het gebruik:


SELECT
	b.bedrijf,
	b.plaats,
	GetDistance(@orglat, @orglng, p.lat, p.lng) afstand
FROM
	bedrijven b
JOIN
	postcodes p
	ON p.postcode = b.postcode
CROSS JOIN
	(SELECT lat, lng INTO @orglat, @orglng FROM postcodes WHERE postcode = '1234AB')
ORDER BY afstand LIMIT 3

Thomas van den Heuvel

01-09-2015 14:01

Mogelijke optimalisatie:

Definieer eerst een vierkant met lengte 2xstraal en breedte 2xstraal en middelpunt orglat, orglng. Kijk eerst wat binnen dit vierkant valt alvorens bovenstaande (en nogal dure) operatie uit te voeren.

Naarmate je in een grotere set zoekt wordt het enkel gebruik maken van bovenstaande functie nogal duur, het is dan beter om eerst een voorselectie te maken met een berekening die een stuk simpeler (en goedkoper) is.

Ger van Steenderen

01-09-2015 21:17

Ik ben heel benieuwd naar de geoptimaliseerde versie.

Thomas van den Heuvel

01-09-2015 21:45

Abstract:

WHERE <makkelijke berekening>
AND <moeilijke berekening>

Door "lazy evaluation" bekijk je dan een (veel) relevant(er)e subset doordat je het domein waarbinnen je zoekt veel strakker afbakent = minder calls naar GetDistance = snellere query.

Het idee lijkt mij zo duidelijk?

Ger van Steenderen

07-09-2015 20:24

Het idee was me al duidelijk, ik was nieuwsgierig naar de uitvoering.
Er zijn mensen die dit via een subquery doen, en dat werkt (vaak) averechts.

Overigens is het belangrijker dat er een index op postcode in de postcode tabel staat.

Reageren

Inloggen om te reageren