[PHP/(My)SQL] voor dummies - Deel 72

Vraagje:

Ik heb de volgende functie om te berekenen hoeveel afstand er tussen 2 coordinaten zit:



Echter, nu heb ik een database met +300.000 records.
Ik wil dat de gebruiker kan zeggen: ik wil binnen een straal van xx KM de resultaten zien.
Dat kunnen ze doen door een postcode in te vullen, waar ik een ander script voor heb om de coordinaten te berekenen.

Maar, om nu elk record te doorlopen om te kijken wat de afstand is, komt natuurlijk niet ten goede van de performance.

Weet iemand hoe ik het bovenstaande het beste kan berekenen? Ik heb in de database de coordinaten al opgeslagen. Dus ik zou eigenlijk in mijn mysql query kunnen zoeken op bijvoorbeeld: LIKE Lat = 1234'%' ,,, maar hoe dit te berekenen?

En hoe gaat dat in zn werk ?

quote:

Op donderdag 18 juni 2009 18:21 schreef Likkende_Lassie het volgende:
En hoe gaat dat in zn werk ?

Je hebt die 300.000+ records in een database. Ik neem aan dat die dingen ook allemaal een eigen id hebben. Dan maak je een tabel aan met de velden id1, id2 en afstand (vergeet ook de indexen niet). En een scriptje om die tabel te vullen.

pseudocode:


Voordeel: je berekent iedere afstand maar 1 keer.
Nadeel: bij het opvragen van afstanden moet je er rekening mee houden dat het punt met het hoogste id altijd als tweede genoemd wordt.

je kan ze ook ALLEMAAL uitrekenen

Dus zowel de heenweg als de terugweg zeg maar... Dan voorkom je je nadeel, en heb je feitelijk de helft aan overbodige records

quote:

Op donderdag 18 juni 2009 19:45 schreef Xcalibur het volgende:
je kan ze ook ALLEMAAL uitrekenen

Dus zowel de heenweg als de terugweg zeg maar... Dan voorkom je je nadeel, en heb je feitelijk de helft aan overbodige records

Ja, ik had de drie weken vakantie nog maar niet genoemd

Ja maar... zo kan ik de afstand van slechts 1 startlocatie opslaan

Er zullen straks flink wat gebruikers verschillende startpunten gaan invoeren, waardoor de afstand per locatie scheelt. Toch?

Zoiets zou opzich wel moeten kunnen denk ik ja. Maar, hoe bereken je de lat/long die je in de SQL query gebruikt?

Kun je het niet generaliseren tot stad/dorp/iets groters dan de exacte coordinaten van de postcode, en een tabel maken die de afstand tussen die grotere blokken opslaat? Tenzij het erg is dat de meting niet 100% nauwkeurig is natuurlijk, dan is dat geen optie.. Mocht je de exacte afstand alsnog willen weten kun je alles selecteren waarbij de afstand tussen de 2 dorpen zegmaar <55km is, daar de exacte afstanden van berekenen, en dan alles <50km weergeven.. ofzoiets..

Ik wil een str_ireplace gebruiken om een link ergens omheen te zetten, maar hij moet het originele woord niet veranderen qua hoofd/kleine letters. Is dat mogelijk of wordt het een eregi oid?
Bijvoorbeeld "Dit IS eEn Test" wordt niet "dit is een test" (met link eromheen dan).

quote:

Op vrijdag 19 juni 2009 18:33 schreef splendor het volgende:
Ik wil een str_ireplace gebruiken om een link ergens omheen te zetten, maar hij moet het originele woord niet veranderen qua hoofd/kleine letters. Is dat mogelijk of wordt het een eregi oid?
Bijvoorbeeld "Dit IS eEn Test" wordt niet "dit is een test" (met link eromheen dan).

Ik zou preg_replace() gebruiken.

quote:

Op vrijdag 19 juni 2009 19:09 schreef Light het volgende:

[..]

Ik zou preg_replace() gebruiken.

Lekker goed voor de performance ook
Aangezien de functionaliteit in php (afaik) niet bestaat om hoofdletterongevoelig te vergelijken maar vervolgens wel de hoofdletters te behouden zou ik er gewoon een eigen functietje voor maken:

Stuk beter voor de performance dan een regular expression

quote:

Op vrijdag 19 juni 2009 23:51 schreef Intrepidity het volgende:

[..]

Lekker goed voor de performance ook
Aangezien de functionaliteit in php (afaik) niet bestaat om hoofdletterongevoelig te vergelijken maar vervolgens wel de hoofdletters te behouden zou ik er gewoon een eigen functietje voor maken:
[ code verwijderd ]

Stuk beter voor de performance dan een regular expression

Ik ben het met je eens dat het sneller is als je geen regular expression nodig hebt. Maar van de regular expression functies presteren de preg_* functies over het algemeen veel beter dan de ereg_* functies.

En jouw functie is leuk, maar werkt alleen als de input een exacte match is met de compare-tekst (hoofdletters buiten beschouwing gelaten). Met preg_replace() mag er ook tekst voor en/of achter de gezochte tekst staan en kun je toch een goede vervanging maken.

quote:

Op zaterdag 20 juni 2009 00:08 schreef Light het volgende:

[..]

Ik ben het met je eens dat het sneller is als je geen regular expression nodig hebt. Maar van de regular expression functies presteren de preg_* functies over het algemeen veel beter dan de ereg_* functies.

En jouw functie is leuk, maar werkt alleen als de input een exacte match is met de compare-tekst (hoofdletters buiten beschouwing gelaten). Met preg_replace() mag er ook tekst voor en/of achter de gezochte tekst staan en kun je toch een goede vervanging maken.

Goed punt, het is ook al laat Het is opzich wel werkend te krijgen met een stripos.. Maar tenzij je deze functionaliteit een paar 1000 keer op een pagina gebruikt is de tijdswinst zo minimaal dat je inderdaad beter af bent met een simpele regex

quote:

Op donderdag 18 juni 2009 17:30 schreef Likkende_Lassie het volgende:
Vraagje:

Ik heb de volgende functie om te berekenen hoeveel afstand er tussen 2 coordinaten zit:
[ code verwijderd ]

Echter, nu heb ik een database met +300.000 records.
Ik wil dat de gebruiker kan zeggen: ik wil binnen een straal van xx KM de resultaten zien.
Dat kunnen ze doen door een postcode in te vullen, waar ik een ander script voor heb om de coordinaten te berekenen.

Maar, om nu elk record te doorlopen om te kijken wat de afstand is, komt natuurlijk niet ten goede van de performance.

Weet iemand hoe ik het bovenstaande het beste kan berekenen? Ik heb in de database de coordinaten al opgeslagen. Dus ik zou eigenlijk in mijn mysql query kunnen zoeken op bijvoorbeeld: LIKE Lat = 1234'%' ,,, maar hoe dit te berekenen?

Wat is een te verwachten waarde voor de straal? 1km? 10km? 100km? Dit is namelijk heel erg bepalend voor hoeveel procent van je records je terug zou kunnen krijgen. Bij 100km zou je in theorie bijna alle locaties terug kunnen krijgen, dus dan heeft slim querien weinig zin, bijvoorbeeld.

Stel dat je straal maximaal zo'n 40 km is, dan zou je het volgende kunnen doen:
* Sla de lattitude op in z'n eigen kolom, met een aparte index op alleen deze kolom.
* Sla de longitude op in z'n eigen kolom, met een aparte index op alleen deze kolom.
* Als je gaat querien, gebruik dan niet de cirkel met een straal van X kilometer, maar het vierkant wat deze cirkel omsluit (oftewel "teken het kleinst mogelijke vierkant om de cirkel met straal X"). Het voordeel is dat je deze vierkant rechtstreeks kan gebruiken in je query ("WHERE Lat between 51,201 and 51,323 and Long between 54.30 and 54.37") en dat dit geoptimaliseerd kan door de indexen op de Lat en Long kolommen.
* De resultset die je terugkrijgt bevat nu nog wel punten die wel binnen het vierkant vielen, maar niet binnen de cirkel. Deze zul je er in code uit moeten filteren met de berekening die je postte, maar dat is niet zo erg, omdat dit slechts ong. 20% van je rijen zal betreffen - de overige 80% valt zowel binnen het vierkant als binnen de cirkel.

Dit lijkt me de best haalbare combinatie van een eenvoudige query die relatief weinig nutteloze rijen teruggeeft.

quote:

Op zaterdag 20 juni 2009 11:23 schreef GlowMouse het volgende:

[..]

Niet waar, ook met MySQL 5.x niet:
[..]

Hmm, volgens mij zou je dan het stuk over index intersections moeten quoten, en ik maak hieruit op dat MySQL dan wel beide indexen gebruikt?

Ik gebruik zelf geen MySQL, dus ik kan het niet testen, maar gelet op de documentatie (en mijn gevoel bij wat je van een redelijke RDBMS zou mogen verwachten) geef ik het eigenlijk wel een goede kans dat het wel geoptimaliseerd wordt.

quote:

Op zaterdag 20 juni 2009 11:46 schreef GlowMouse het volgende:

[..]

De enige ongelijkheid die ik zie staan daar is er eentje op een primary key van een InnoDB-tabel.

Oh, zo, ja dat zou ook kunnen. Ik lette met name op het feit dat alle condities met AND's gecombineerd moesten worden en dat per index al zijn kolommen in de conditie aanwezig moesten zijn. Daar lijkt mijn oplossing aan te voldoen, maar inderdaad, bij het gegeven voorbeeld gebruiken ze geen ongelijkheden.
Dus de vraag is of we daaruit moeten afleiden dat dit deel van het algoritme alleen met gelijkheden werkt.

quote:

Dat zal er wel mee te maken hebben dat die key bij elke entry in de index op key_col1 opgeslagen wordt, en MySQL dus eigenlijk alleen de index op key_col1 hoeft te raadplegen en daarbij direct en 'gratis' de ongelijkheid kan checken.

Ik heb een andere verklaring Ik denk dat InnoDB zijn primary key index altijd 'clustered' maakt, d.w.z. dat de records fysiek geordend op primary key worden opgeslagen. En wanneer gegevens fysiek gesorteerd opgeslagen zijn, is het heel efficient om een range query uit te voeren: je zoekt het beginpunt, het eindpunt, en je retourneert alle rijen ertussenin.

quote:

Queries op grote tabellen die geen resultaat kunnen geven op basis van maximaal één index, vermijd ik zelf liever bij webapplicaties.

De meeste webapplicaties gebruiken dan ook geen coordinaten

quote:

Op zaterdag 20 juni 2009 12:09 schreef GlowMouse het volgende:

[..]

Maar wat doe je dan met die index op key_col1?

Het feit dat je gegevens fysiek geordend zijn op key_col1 maakt dat de tabel al geindexeerd is - het heeft geen aparte opslagstructuur nodig. Je tabel is tevens je index, als het ware.

Je snapt dat dit trucje maar voor 1 index toepasbaar is - je kunt je tabel tenslotte maar op 1 manier fysiek gesorteerd opslaan. Dus voor alle andere indexen zul je wel een aparte opslagstructuur moeten bijhouden.

quote:

edit: EXPLAIN geeft aan toch iets te doen met index_intersect. Vreemd, ik zou zeggen dat alleen de index op key_col1 gebruiken efficienter is.

Welke situatie heb je nu uitgeprobeerd dan?

[ Bericht 15% gewijzigd door HenryHill op 20-06-2009 12:19:48 ]

http://www.sitemasters.be(...)s_online_script_v2.0

Bezoekers online script zeer onveilig. zitten er leuke ideeën in.
Het grappig is dat de meeste scripts online niet aan de veiligheid denken maar wel aan het script zelf.

Dit script is opzich wel een leuk idee. Jammer van de sql escape.

quote:

Op vrijdag 19 juni 2009 19:09 schreef Light het volgende:

[..]

Ik zou preg_replace() gebruiken.

Dankje, die werkte een stuk beter.
Alleen heb ik met /b gezegd dat het alleen hele woorden moeten zijn, maar dat pakt ie niet erg.

quote:

Op zaterdag 20 juni 2009 19:48 schreef splendor het volgende:

[..]

Dankje, die werkte een stuk beter.
Alleen heb ik met /b gezegd dat het alleen hele woorden moeten zijn, maar dat pakt ie niet erg.

Moet je dat niet met \b doen?