PeHa ICT

Het is vandaag 9 september. Ik heb een paar aanpassingen gedaan in deze post: de aanroep naar de api van Google maps is veranderd, en ik gebruik nu zoals Jop voorstelde de functie RADIANS() om de graden naar radialen om te zetten

Al weer een tijd geleden heb ik wat geschreven over een project waarbij de dichtstbijzijnde winkels gevonden moesten worden. /afstanden-bepalen-mbv-google-maps

Ik was met mijn verhaal zo ver gekomen, dat de coordinaten van de winkels via Google Maps achterhaald waren.

De volgende stap is nu, dat een klant zich meldt en de winkels wil weten die in de buurt liggen.

Stap 1: waar is de bezoeker?

De bezoeker vragen we om zijn locatie. Met sommige devices is het mogelijk om de exacte locatie te achterhalen, mits de gebruiker daar toestemming voor geeft, maar in mijn geval vraag ik de gezoeker om zijn woonplaats of postcode op te geven. Het land leid ik af uit een eerder gemaakte keuze op de site.

Laten we zeggen, dat hij opgeeft zich in Hasselt te bevinden. Deze plaats komt zowel voor in Nederland als in België, dus het is van belang om het land te kennen.

Via de aanroep die we in deel 1 naar Google Maps deden om de winkels te vinden, kunnen we ook de bezoeker localiseren.

http://maps.google.com/maps/geo?q=6333AT%20Nederland

De api is wat veranderd, zie ook deel 1 van deze reeks artikelen. De aanroep wordt nu:

http://maps.googleapis.com/maps/api/geocode/json?address=6333AT,+Nederland&sensor=false

De response daarvan is:

{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "6333 AT",
               "short_name" : "6333 AT",
               "types" : [ "postal_code" ]
            },
            {
               "long_name" : "Schimmert",
               "short_name" : "Schimmert",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Nuth",
               "short_name" : "Nuth",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "Limburg",
               "short_name" : "LI",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "Nederland",
               "short_name" : "NL",
               "types" : [ "country", "political" ]
            }
         ],
         "formatted_address" : "6333 AT Schimmert, Nederland",
         "geometry" : {
            "bounds" : {
               "northeast" : {
                  "lat" : 50.8962940,
                  "lng" : 5.8233660
               },
               "southwest" : {
                  "lat" : 50.89209850,
                  "lng" : 5.81726150
               }
            },
            "location" : {
               "lat" : 50.89539530,
               "lng" : 5.81917950
            },
            "location_type" : "APPROXIMATE",
            "viewport" : {
               "northeast" : {
                  "lat" : 50.8962940,
                  "lng" : 5.82071750
               },
               "southwest" : {
                  "lat" : 50.893550,
                  "lng" : 5.81726150
               }
            }
         },
         "types" : [ "postal_code" ]
      }
   ],
   "status" : "OK"
}

Stap 2: een sql-functie

Om de afstand tussen 2 punten op een plat vlak te bepalen, zou de stelling van Pythagoras uitkomst bieden:

a² + b² = afstand²

Maar op een bol ligt dat moeilijker.

Nu kunnen we die lastige functie gewoon in de query verwerken, maar die wordt dan erg onoverzichtelijk. Bovendien zouden we die functie misschien wel vaker willen gebruiken, en dan is het zonde om overal die ingewikkelde code weer te moeten copy-pasten.

Dus: ik maak er een stored function van in sql. We hebben zelfs 2 functions nodig:

De eerste heet HAVERSIN:

CREATE DEFINER=root@localhost FUNCTION HAVERSIN(x FLOAT)
	RETURNS float
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
    RETURN ( 1 - COS(x) ) / 2;
END

Zelf gebruik ik HeidiSQL als tool om met de database te werken. Daarin kun je het hele stuk tot BEGIN regelen via wat invulveldjes en hoef je alleen de werkelijke code zelf te typen.

Als je bovenstaande echter gewoon als query uitvoert, wordt de function aangemaakt.

Het tweede deel is ingewikkelder. Deze function heet HAVERSINE en gebruikt de function HAVERSIN weer.

CREATE DEFINER=root@localhost FUNCTION HAVERSINE(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT)
	RETURNS float
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
             -- Constants (Earth radius in KM)
             DECLARE earth_radius INT;
             -- Holds the haversine function
 
             DECLARE haversine FLOAT;
             SET earth_radius = 6371;
             -- Convert the latitudes to radians
             SET lat1 = RADIANS( lat1 );
             SET lon1 = RADIANS( lon1 );
 
             SET lat2 = RADIANS( lat2 );
             SET lon2 = RADIANS( lon2 );
             -- Calculate the Haversine function
             SET haversine = (
                HAVERSIN(lat1 - lat2)
 
                +
                (
                    COS(lat1)
                    *
                    COS(lat2)
                    *
                    HAVERSIN( ABS ( lon1 - lon2 ) )
                )
 
             );
             -- Calculate the Haversine distance
             RETURN 2 * earth_radius * ASIN( SQRT ( haversine ) );
 END

Stap 3: afstanden bepalen

We hebben nu de functie die we daadwerkelijk nodig hebben. In de tabel staan de winkels opgeslagen met de coordinaten in de kolommen latitude en longitude. De query om de 10 dichtstbijzijnde vestigingen te vinden, luidt dan:

 
SELECT *, 
       HAVERSINE( LATBEZOEKER  , LONGBEZOEKER, latitude, longitude) afstand  
FROM stores 
WHERE 1 
ORDER BY afstand ASC  
LIMIT 10 

Nu zou het mogelijk zijn, dat als we een beetje in een uithoek wonen, we winkels op 120 kilometer afstand te zien krijgen. Als we dat willen beperken tot 25 kilometer, dan wordt de query

SELECT *, 
       HAVERSINE( LATBEZOEKER  , LONGBEZOEKER, latitude, longitude) afstand  
FROM stores 
WHERE 1 
  AND HAVERSINE( LATBEZOEKER  , LONGBEZOEKER, latitude, longitude) < 25
ORDER BY afstand ASC  
LIMIT 10 

Ik wil het nu hier even bij laten. Binnekort, en dan hopelijk wat sneller dan de maanden dat ik het nu heb laten liggen, zal ik er wat meer over zeggen.

En dan zoek ik ook direct een code highlighter voor de SQL en PHP code.