[PHP/MySQL] Afstanden berekenen en later op kunnen sorteren

[Dutch_guy]

Ik heb een PHP script welke op basis van een zoekveld afstanden berekent met waardes uit een tabel.

Dus, bezoeker vult zijn geldige postcode in, script zoekt met behulp van Google maps API de lat/ lon coördinaten op van die postcode, vervolgens wordt er in een tabel gezocht naar bedrijven die binnen 30 km van die postcode liggen.

De bedrijven in die tabel bevatten reeds de lat/ lon coördinaten. Met een formule bereken ik dan de afstanden en alleen de bedrijven die binnen 30 km liggen worden getoond.

Echter ik wil dan vervolgens aan de bezoeker de afstanden tot de ingevulde postcode kunnen tonen, maar ook de bedrijven sorteren op dichtstbijzijnde postcode.

Ik kom daar niet uit. Ik kan kiezen voor de makkelijkste weg en de id's van de gevonden bedrijven + de afstanden opslaan in een tijdelijke tabel en die naderhand gebruiken om mee te sorteren en de afstanden weer te geven.

Nu wordt dit geen website met duizenden bezoekers, dus daar kan ik mee uit de voeten, maar lijkt mij dat dat toch anders moet kunnen.

Iemand die mij in de juiste richting kan duwen?

[Janoz]

Je geeft aan dat je al een formule hebt die de afstand berekent en dat je daarmee je resultaten al filtert. Waar blijven de resultaten van die formule? Waarom zou je dat weer opnieuw moeten berekenen?

Laat eens een stukje van je code zien waarmee je die resutlaten filtert.

[Verwijderd]

Weet niet of mysql het intussen ondersteund (postgress in ieder geval via PostGIS): de netste oplossing is geospatial data. Daarmee kun je in de database zelf queries uitvoeren op lat/long en afstanden daartussen.

Anders een tabel met afstanden tussen postcode A en B. Zoals je zelf voorstelt. Nu zijn er 440.000 postcodes in Nederland. Dat kan aardig oplopen. Tussenvorm is een tabel met alleen de cijfers. Die gebruik je om de query uit te voeren. Vervolgens bereken je de ‘echte’ afstand op basis van de coordinaten van de volledige postcode.

Ruwe data voor 4pp is beschikbaar https://github.com/bobdenotter/4pp

[ Voor 6% gewijzigd door Verwijderd op 02-04-2020 12:54 ]

[Rensjuh]

Wat je kunt doen is de functie gebruiken om de afstand per gevonden resultaat te berekenen.
Deze gegevens in een array opslaan inclusief dus de berekende afstand.
Vervolgens sorteer je je array op afstand en doorloop je deze met de resultaten.
Let wel op dat dit je weergave behoorlijk kan vertragen als je veel resultaten hebt.

Je kunt het misschien ook met de volgende functie in MySQL doen:
https://dev.mysql.com/doc...html#function_st-distance
Vervolgens sorteren op de output hiervan in je query en je resultaten weergeven.

Scheelt je een keer door de resultaten heen lopen t.o.v. het in PHP berekenen.

[emnich]

De makkelijkste manier is toch gewoon om de afstand in MySQL te berekenen ipv in PHP. Dan kan je daar gewoon op sorteren.

Om te voorkomen dat je voor elk record de afstand gaat berekenen, trek je eerst een vierkant op je punt heen waarmee je dus de min/max lat/lng hebt. Voor elk record in dat vierkant, bereken je de echte afstand (met 30km zal dat niet zo veel verschillen).

[CurlyMo]

Of inderdaad direct in PostGIS zoals al is gezegd. Die is daar enorm snel in. Desnoods draai je PostgreSQL ernaast specifiek voor dit doel.

[Dutch_guy]

Ik had niet bedacht dat je dit in MySQL kon doen met "st_distance_sphere".

Nu gebruik ik MariaDB als database en die ondersteunt die niet, echter met het toevoegen van deze functie wel:

code:

CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT)
RETURNS double(10,2)

RETURN 6371000 * 2 * ASIN(
    SQRT(
        POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2), 2) +
        COS(ST_Y(pt1) * pi()/180 ) *
        COS(ST_Y(pt2) * pi()/180) *
        POWER(SIN((ST_X(pt2) - ST_X(pt1)) * pi()/180 / 2), 2)
    )
)

Dus dit is de perfecte oplossing, want nu kan ik uiteraard direct sorteren op de afstand, top!

[Voutloos]

Perfect is het sowieso niet, want de aarde is geen perfecte bol en je hebt eigenlijk zelfs een formule voor NL coords nodig. Je cijfers zullen niet op de meter nauwkeurig zijn.

Voor veel toepassing wel ‘goed genoeg’, dat wel. En je wil als je het met een inefficiente eigen functie doet en meer dan handvol punten vergelijkt, toch wel écht de optimalisatie met de bounding box meenemen.