[mysql] dichtsbijzijnde locatie zoeken m.b.v. gps

[Martink]

Hoi,

Stel ik heb een database met alle vestigingen van een bedrijf, met daarin alle gps posities.

hoe query ik dan de mysql database dat ik de dichtsbijzijnde vestiging krijg als ik die informatie ga wil opvragen met mijn houdige gps positie (dus waar ik nu ben). heb al lopen zoeken maar kan zo snel niks vinden.

hoop dat jullie mij kunnen helpen.

[burne]

Martink schreef op woensdag 06 augustus 2008 @ 14:40:
hoop dat jullie mij kunnen helpen.

Je kunt jezelf helpen: http://www.google.com/search?q=lat+lon+distance

(De termen zijn niet helemaal voor de hand liggend, maar je had wellicht iets meer eigen inspanning kunnen verrichten..)

[Fiander]

je bedoelt iets als :
select blah, blah from geodata where distance( geo1, geo2 )

je had google kunnen gebruiken, dan was je als eerste deze tegen gekomen
[url]http://forge.mysql.com/wiki/GIS_Functions[\url]


sh*t, te langzaam

[ Voor 5% gewijzigd door Fiander op 06-08-2008 14:45 ]

[Grandmasg]

Martink schreef op woensdag 06 augustus 2008 @ 14:40:
Stel ik heb een database met alle vestigingen van een bedrijf, met daarin alle gps posities.

Mijn eerste vraag is dan toch echt?
-- Wat voor soort coordinaten??

LAT LONG of RD coordinaten enz.

De 1e wordt omschreven hierboven. Voor de 2e heb je Wikipedia: Pythagoras nodig!!

[Martink]

LAT LONG denk ik, maargoed dat moet ik dan nog maar even onderzoeken, alvast bedankt voor de informatie:)

[glashio]

Grandmasg schreef op woensdag 06 augustus 2008 @ 14:57:
Voor de 2e heb je Wikipedia: Pythagoras nodig!!

De wereld is niet plat

Via deze referentie site ben ik zelf op deze mysql query uitgekomen :

SQL:

SET @lat=51.9210835, @lon=4.476738, @a=0;
SELECT
    prikey,
    @a:=
        POW(
            SIN((@lat - latitude) * PI() / 180 / 2)
        ,
            2
        )
    +
        COS(latitude * PI() / 180)
    *
        COS(@lat * PI() / 180)
    *
        POW(
            SIN((@lon - longitude) * PI() / 180 / 2)
        ,
            2
        )
    ,
    ATAN2(SQRT(@a, SQRT(1 - @a)) * 6371 * 2 AS km
FROM
    mytable
WHERE
    latitude IS NOT NULL
AND
    longitude IS NOT NULL
ORDER BY
    km ASC
LIMIT 1

Laat die sql-server eens zweten met dit mooie weer

[ Voor 17% gewijzigd door glashio op 06-08-2008 15:56 . Reden: Tijdelijke variable @a gebruikt die eerst 2x berekend werd ]

[pedorus]

En dan maar hopen dat je niet te veel vestigingen hebt
Zelf zou ik toch even de GPS coordinaten omzetten naar RD, zodat je makkelijk de afstanden hebt, en eventueel makkelijk een vierkantje kan afbakenen.

[P.O. Box]

glashio schreef op woensdag 06 augustus 2008 @ 15:39:
[...]

De wereld is niet plat

ik weet niet hoe precies je wilt zijn, maar volgens mij kun je voor de gewenste applicatie hier er best vanuit gaan dat de wereld een perfecte bol is, en dan kun je weer prima pythagoras gebruiken...
in het geval van de kans dat je je zo tussen twee vestigingen in bevind dat de keuze voor de dichtstbijzijnde de verkeerde is, lijkt me niet van belang. De reistijd naar die vestiging toe hangt toch niet van de hemelsbrede afstand af en die paar seconden die het eventueel zou schelen als je hemelsbreed zou kunnen reizen boeien ook niet echt lijkt me...

[Paulusbrand]

Je kunt natuurlijk eenmalig de afstanden tussen alle vestigingen berekenen en opslaan in een tabel. Scheelt serverbelasting...

[Verwijderd]

Paulusbrand schreef op woensdag 06 augustus 2008 @ 16:03:
Je kunt natuurlijk eenmalig de afstanden tussen alle vestigingen berekenen en opslaan in een tabel. Scheelt serverbelasting...

View anyone? Goed op dit idee gekomen (voor de TS)

glashio schreef op woensdag 06 augustus 2008 @ 15:39:
De wereld is niet plat

[Cartman!]

Misschien mis ik iets maar de afstanden zijn toch variabel? Vanaf postcode A is het toch anders dan van postcode B... hoe wil je al die verschillen berekenen en opslaan dan? Dat lijkt me nogal veel data

[Hydra]

glashio schreef op woensdag 06 augustus 2008 @ 15:39:
De wereld is niet plat

Volgens het RD stelsel wel, en voor afstanden binnen Nederland is de wereld plat genoeg Heeft als voordeel dat de berekening een stuk simpeler is dan geodistance berekeningen.

Cartman! schreef op woensdag 06 augustus 2008 @ 16:17:
Misschien mis ik iets maar de afstanden zijn toch variabel? Vanaf postcode A is het toch anders dan van postcode B... hoe wil je al die verschillen berekenen en opslaan dan? Dat lijkt me nogal veel data

Valt wel mee, wordt veelvuldig gebruikt voor drive-time matrices.

[ Voor 37% gewijzigd door Hydra op 06-08-2008 16:19 ]

[burne]

glashio schreef op woensdag 06 augustus 2008 @ 15:39:
De wereld is niet plat

Nederland is erg plat. Op een reis van Cadzand-Bad naar Oudeschip scheelt het een paar meter.

[Megamind]

Ik heb hier zelf ook veel mee zitten kloten, en je moet toch wel een beetje kennis hebben van navigatie om hier uit te komen.

Ik ben hier op uitgekomen om hemelsbreed afstand tussen 2 coordinaten uit te rekenen:

code:

SELECT 
    (1.609344 * (3958 * PI() * SQRT( POW( (".$latA." - ".$latB.") , 2) + COS( ".$latA." / 57.29578 ) * COS( ".$latB." / 57.29578 ) * POW( (".$lonA." - ".$lonB.") , 2) ) / 180) ) AS distance
FROM tabel

als GPS coordinaat opgeven, dus in decimalen en niet in graden:
$latA = 4.905987
$latB = 52.378268

Ik geef alleen 1 postcode op en dan berekent hij van alle locaties (ong. 10.000) de afstand en sorteerd op afstand. Max 0,8 sec

[ Voor 21% gewijzigd door Megamind op 06-08-2008 17:08 ]

[Hydra]

0.8 sec is op een drukke site natuurlijk veel te lang, dan gaan je queries je bottlenecken met alle gevolgen van dien. Dan kun je beter de matrix voorberekenen en simpele lookups doen.

[Grandmasg]

glashio schreef op woensdag 06 augustus 2008 @ 15:39:
[...]
De wereld is niet plat

Het is maar net hoe precies je wilt weten wat de afstand is!

Maar om nou te zeggen dat de wereld zo rond is als een gepolijste knikker

[Megamind]

Hydra schreef op woensdag 06 augustus 2008 @ 17:09:
0.8 sec is op een drukke site natuurlijk veel te lang, dan gaan je queries je bottlenecken met alle gevolgen van dien. Dan kun je beter de matrix voorberekenen en simpele lookups doen.

Ik heb het nu op de volledige postcode db draaien (432,572 records), maar voor online gebruik ik een aangepaste versie die alleen de eerste 5 tekens heeft (1011A), deze is maar 30,603 records, en is een stuk sneller.

Grandmasg schreef op woensdag 06 augustus 2008 @ 17:13:
[...]


Het is maar net hoe precies je wilt weten wat de afstand is!

Maar om nou te zeggen dat de wereld zo rond is als een gepolijste knikker

Ook niet rond

The idea of a flat Earth is that the surface of the Earth is flat (a plane), rather than the view that it is a very close approximation of the surface of a sphere

[ Voor 31% gewijzigd door Megamind op 06-08-2008 17:20 ]

[Verwijderd]

Verwijderd schreef op woensdag 06 augustus 2008 @ 16:13:
[...]
View anyone? Goed op dit idee gekomen (voor de TS)

wat scheelt dat aan server belasting dan?

Afstanden tabel genereren lijkt me handiger.

[ Voor 8% gewijzigd door Verwijderd op 06-08-2008 17:29 ]

[Cartman!]

Hydra schreef op woensdag 06 augustus 2008 @ 16:17:
[...]
Valt wel mee, wordt veelvuldig gebruikt voor drive-time matrices.

16 miljoen records vind ik anders best veel voor zo'n functionaliteit. Weet niet hoe populair de site van de maker gaat worden maar als je weinig bezoekers hebt lijkt me on the fly berekenen met simpeler.

[Hydra]

Cartman! schreef op woensdag 06 augustus 2008 @ 17:34:
16 miljoen records vind ik anders best veel voor zo'n functionaliteit. Weet niet hoe populair de site van de maker gaat worden maar als je weinig bezoekers hebt lijkt me on the fly berekenen met simpeler.

Kan best, het is alleen niet schaalbaar naar hogere bezoekersaantallen als een query je 0.8s kost. Wij hebben die functionaliteit ook in ons systeem ingeplugd, alleen wordt hier gewoon die tabel in het geheugen gehouden. Da's een simpele kwestie van 3 array lookups en dus retesnel.

[ Voor 17% gewijzigd door Hydra op 06-08-2008 17:42 ]

[Flipke84]

Misschien heb je nog iets aan deze powerpoint presentatie van MySQL

[Cartman!]

Hydra schreef op woensdag 06 augustus 2008 @ 17:41:
[...]


Kan best, het is alleen niet schaalbaar naar hogere bezoekersaantallen als een query je 0.8s kost. Wij hebben die functionaliteit ook in ons systeem ingeplugd, alleen wordt hier gewoon die tabel in het geheugen gehouden. Da's een simpele kwestie van 3 array lookups en dus retesnel.

Dat zeg ik, voor een kleine site slaat dat nergens op Welke sharedhosting-account heeft nu memcache (of gelijke) mogelijkheden?

[DaCoTa]

Martink schreef op woensdag 06 augustus 2008 @ 14:40:
Hoi,

Stel ik heb een database met alle vestigingen van een bedrijf, met daarin alle gps posities.

hoe query ik dan de mysql database dat ik de dichtsbijzijnde vestiging krijg als ik die informatie ga wil opvragen met mijn houdige gps positie (dus waar ik nu ben). heb al lopen zoeken maar kan zo snel niks vinden.

hoop dat jullie mij kunnen helpen.

Dichtsbijzijnd op welke manier? Hemelsbreed, met de auto, lopend, boot of space shuttle?

Verder: als het binnen Nederland is, kun je het beter offline een keer alle postcodes doorrekeken en dan bijvoorbeeld de range opslaan van alle aansluitende postcodes die het dichts bij dezelfde verstiging zijn. VB:

code:

1000AA - 1563BC  Vestiging A
1563BD - 1611FD  Vestiging B
1611FE - 2105ZZ Vestiging A
2106AA - etc etc

Dan heb je niet eens zo heel veel records nodig, aangezien postcodegebieden redelijk aansluiten zijn. Andere mogelijkheid is het grootste gemeenschappelijke postcodegebied per vestiging opslaan, en alleen meer opslaan indien nodig.

[DutchKel]

DaCoTa schreef op woensdag 06 augustus 2008 @ 18:42:
[...]

Dichtsbijzijnd op welke manier? Hemelsbreed, met de auto, lopend, boot of space shuttle?

Verder: als het binnen Nederland is, kun je het beter offline een keer alle postcodes doorrekeken en dan bijvoorbeeld de range opslaan van alle aansluitende postcodes die het dichts bij dezelfde verstiging zijn. VB:

code:

1000AA - 1563BC  Vestiging A
1563BD - 1611FD  Vestiging B
1611FE - 2105ZZ Vestiging A
2106AA - etc etc

Dan heb je niet eens zo heel veel records nodig, aangezien postcodegebieden redelijk aansluiten zijn. Andere mogelijkheid is het grootste gemeenschappelijke postcodegebied per vestiging opslaan, en alleen meer opslaan indien nodig.

Je kunt dit verhaal ook andersom doen en de postcode van elke vestiging opslaan en dan ga je op postcode zoeken. Als je van achteren naar voren gaat zoeken dan kom je bij een dichtstbijzijnde vestiging uit. Er vanuit gaande dat er toch wel 1 vestiging in elk hoofdpostcode gebied zit.
Bijvoorbeeld:
1111AA - Vestiging A
2222BB - Vestiging B

Zoek je postcode 1234AB dan voer je een like uit.
where postcode like "%1234AB%"
geen resultaat
where postcode like "%1234A%"
geen resultaat
where postcode like "%1234%"
geen resultaat
where postcode like "%123%"
geen resultaat
where postcode like "%12%"
geen resultaat
where postcode like "%1%"
1111AA - Vestiging A

Als je genoeg vestigingen hebt dan kun je dit makkelijk doen. Overigens heb ik deze methode op een website toegepast en het werkt perfect. pm me maar als je het live wilt testen.

[Cartman!]

Je maakt alleen de fout dat 1234 hemelsbreed 100km verder kan liggen dan 1290. Een logisch systeem voor postcodes is er niet echt. De plaats Hilversum heeft bijv. 1211 en Soest (wat echt een paar km verder ligt) 3760. De enige goede manier om afstanden te bepalen adhv. postcodes is met geocoding en dat kan dus latitude/longitude of x/y zijn zegmaar.

edit:
een like op %1% is helemaal raar btw dan matchen zowel 1000, 9001, 2010 etc.

[ Voor 11% gewijzigd door Cartman! op 06-08-2008 21:02 ]

[P.O. Box]

kfaessen schreef op woensdag 06 augustus 2008 @ 18:55:
[...]

Je kunt dit verhaal ook andersom doen en de postcode van elke vestiging opslaan en dan ga je op postcode zoeken. Als je van achteren naar voren gaat zoeken dan kom je bij een dichtstbijzijnde vestiging uit. Er vanuit gaande dat er toch wel 1 vestiging in elk hoofdpostcode gebied zit.
Bijvoorbeeld:
1111AA - Vestiging A
2222BB - Vestiging B

Zoek je postcode 1234AB dan voer je een like uit.
where postcode like "%1234AB%"
geen resultaat
where postcode like "%1234A%"
geen resultaat
where postcode like "%1234%"
geen resultaat
where postcode like "%123%"
geen resultaat
where postcode like "%12%"
geen resultaat
where postcode like "%1%"
1111AA - Vestiging A

Als je genoeg vestigingen hebt dan kun je dit makkelijk doen. Overigens heb ik deze methode op een website toegepast en het werkt perfect. pm me maar als je het live wilt testen.

als je het op die manier doet (en dat is zoals hierboven genoemd inderdaad niet de ideale manier), kun je volgens mij beter de getallen van de postcodes van elkaar aftrekken (letters boeien niet echt) en daar de absolute waarde van nemen. De postcode met de kleinste absolute waarde is dan het dichts bij.... (nou ja, vaak dan)...

dus:

SQL:

select
   vestiging, postcode
from
   vestigingentabel
order by
   ABS(LEFT(postcode, 4) - LEFT('$postcode', 4))
limit
   1

@hieronder:

DaCoTa schreef op donderdag 07 augustus 2008 @ 09:05:
Die laatste 2 zijn wel erg hacky...

helemaal mee eens...

[ Voor 6% gewijzigd door P.O. Box op 07-08-2008 09:18 ]

[DaCoTa]

Die laatste 2 zijn wel erg hacky... De nummers van postcodes zeggen verder niet zoveel, dus met 2 postcodes weet je niks zonder verdere informatie over die postcodes. Kijk eens naar


Maar nogmaals, op welke manier wil je afstand berekenen? Hemelsbreed of met auto? Want voor die tweede zijn er ook diensten die je kunt inkopen.

[ Voor 22% gewijzigd door DaCoTa op 07-08-2008 09:06 ]

[P_de_B]

http://dev.mysql.com/doc/...n/spatial-extensions.html

MySQL heeft ondersteuning voor spatial data. Hoe goed de ondersteuning is weet ik niet, maar het is wellicht de moeite waard eens te kijken. Er zijn databases die geindexeerd op een veld met gps-posities berekeningen (zoals afstand) kunnen doen.

[MSalters]

PS: Je wilt nooit op distance sorteren, maar altijd op distance². Dat scheelt je namelijk een volstrek zinloze SQRT in je vergelijking. Op dezelfde manier hoef je dus ook geen constantes mee te nemen; als de afstand in radialen groter is, dan is de afstand in graden dat ook. En tenslotte is POW((x-y), 2) waarschinlijk veel trager dan (x-y)*(x-y).

Overigens zou ik Nederland gewoon tot een trapezium platslaan. Rechthoek is misschien iets overdreven: 1 breedtegraad varieert van ongeveer 64 kilometer in het zuiden tot 59 kilometer in het noorden, maar daartussen loopt het behoorlijk lineair af.