[SQL] Hoe sorteren op absoluut verschil*

[Verwijderd]

Voor een website ben ik een soort zoekmachine (groot woord voor wat het moet kunnen) aan het maken, als er een postcode wordt ingevoerd moeten alle postcodes binnen een bepaalde reeks worden getoond.
Nu lukt het me wel om met SQL een query te maken die alle benodigde gegevens uit de database haalt, maar ik kan alleen af- of oplopend sorteren.
Ik wil graag sorteren op de dichtstbijzijnde waarde.
Zodat als postcode 2000 wordt gezocht, de volgorde als volgt is:
2000, 2001, 1999, 2002, 1998, 2003, etc.

Hoe krijg ik dit voor elkaar??? Alvast bedankt.

Adnox

[gorgi_19]

Sorteren op de absolute waarde (ABS) van het verschil tussen de gevonden en de te zoeken waarde.

En wat heeft dit trouwens met ASP te maken?

[ Voor 22% gewijzigd door gorgi_19 op 25-06-2003 08:54 ]

[sjroorda]

Waarom niet een select from postcode where pc > x - 1000 AND pc > x + 1000? Beetje kort door de bocht, maar ik weet niet of je echt een x-aantal postcodes erboven/onder wilt, of een range van +1000/-1000 of zo. Sorry, niet goed gelezen...

[ Voor 10% gewijzigd door sjroorda op 25-06-2003 09:56 ]

[gorgi_19]

Je hebt nu de range te pakken, maar nu nog het sorteren er bij... En die is op de afwijking, niet absolute verschil.

[sjroorda]

SELECT * FROM table WHERE ABS(id - 30) < 10 ORDER BY ABS(id - 30) LIMIT 0, 30

werkt bij mij gewoon! (MySQL)

[gorgi_19]

sjroorda schreef op 25 June 2003 @ 09:57:
SELECT * FROM table WHERE ABS(id - 30) < 10 ORDER BY ABS(id - 30) LIMIT 0, 30

werkt bij mij gewoon! (MySQL)

En afgezien van het limit-gedeelte werkt deze ook in Access; ik had iet soortgelijks..

Alleen ik ben wat minder fanatiek met code posten..

[jochemd]

Verwijderd schreef op 25 June 2003 @ 08:49:
Voor een website ben ik een soort zoekmachine (groot woord voor wat het moet kunnen) aan het maken, als er een postcode wordt ingevoerd moeten alle postcodes binnen een bepaalde reeks worden getoond.
Nu lukt het me wel om met SQL een query te maken die alle benodigde gegevens uit de database haalt, maar ik kan alleen af- of oplopend sorteren.
Ik wil graag sorteren op de dichtstbijzijnde waarde.
Zodat als postcode 2000 wordt gezocht, de volgorde als volgt is:
2000, 2001, 1999, 2002, 1998, 2003, etc.

Hoe krijg ik dit voor elkaar??? Alvast bedankt.

Adnox

ALs je het echt goed wil doen dan sorteer je natuurlijk ook op het alfabetische deel van een postcode Aannemende dat je een variabele startPC hebt wordt dat dan ongeveer:

code:

SELECT       pc
FROM         postcodes
ORDER BY   
             abs(
                  (Val(left(pc,4))
                  +
                      (
                        (asc(mid(pc,5,1))-65)*26
                        +asc(right(pc,1))-65
                      )/676)
               -
                  (Val(left(startpc,4))
                  +
                     (
                      (asc(mid(startpc,5,1))-65)*26
                      +asc(mid(startpc,6,1))-65
                     )/676)
               )

[ Voor 5% gewijzigd door jochemd op 25-06-2003 11:42 ]

[Bosmonster]

Misschien offtopic, maar je kunt er bij postcodes in principe niet vanuit gaan dat ze geografisch bij elkaar liggen omdat de nummers toevallig bij elkaar liggen.

Jochemd: Zo'n query gaat minuten duren bij veel entries

Aangezien die niet uit kan gaan van een index (je kunt helaas geen indices maken op 'berekende' velden) zal die voor alle entries die berekening uit moeten voeren.. Stel je dat eens voor met 50K records ofzo

[ Voor 50% gewijzigd door Bosmonster op 25-06-2003 11:48 ]

[ACM]

Bosmonster schreef op 25 June 2003 @ 11:43:
maar je kunt er bij postcodes in principe niet vanuit gaan dat ze geografisch bij elkaar liggen omdat de nummers toevallig bij elkaar liggen.

Dat was toch het hele idee achter de nummering?

groningen: 9000, friesland: 8000, zuid holland: 2000, noord holland: 1000...
etc
Het geld vast niet precies, maar globaal zal het wel zo zijn dat postcodes die dicht bij elkaar liggen ook geografisch dicht bij elkaar zijn.

Jochemd: Zo'n query gaat minuten duren bij veel entries

ach er zijn maar 6084000 mogelijke combinaties waar een aardig aantal al uitgewerkt is kwa nummers en letters. Dus das zo gepiept

Aangezien die niet uit kan gaan van een index (je kunt helaas geen indices maken op 'berekende' velden) zal die voor alle entries die berekening uit moeten voeren.. Stel je dat eens voor met 50K records ofzo

Met een beetje schipperen kom je aardig ver, zeker als je van alle postcodes de afstand tot 0000AA ofzo berekent via een functionele index en dan van je gezochte postcode het verschil neemt met die 0000AA-afstand. Dan wordt het weer een ORDER BY ABS(afstand - afstand)

[ Voor 26% gewijzigd door ACM op 25-06-2003 11:51 ]

[Bosmonster]

Geen idee wat voor database die gebruikt.. MySQL ondersteunt voor zover ik weet geen functionele indices. Als je daar dit soort dingen gaat doen kun je performance wel gedag zeggen..

edit: Zie dat het origineel om ASP ging, dus zal dan ook wel MSSQL zijn.. scheelt al weer

(sorteren op de letters heeft volgens mij al helemaal geen geografische waarde)

[ Voor 61% gewijzigd door Bosmonster op 25-06-2003 11:56 ]

[ACM]

De letters lijkt me niet, hoewel dat meestal wel dusdanig logisch geordend is dat nieuwe wijken naast elkaar oplopende letters in de straten hebben. Maar dat zal wel mooi lineair oplopen en niet circulair

[gorgi_19]

Bosmonster schreef op 25 June 2003 @ 11:54:
edit: Zie dat het origineel om ASP ging, dus zal dan ook wel MSSQL zijn.. scheelt al weer

offtopic:
Ik gok dat de meeste niet de beschikking hebben over MSSQL icm ASP; meest gebruikte combinatie is dan nog steeds MS Access icm ASP

[jochemd]

Bosmonster schreef op 25 June 2003 @ 11:43:
Misschien offtopic, maar je kunt er bij postcodes in principe niet vanuit gaan dat ze geografisch bij elkaar liggen omdat de nummers toevallig bij elkaar liggen.

Dat kan tot op zekere hoogte wel hoor.

Jochemd: Zo'n query gaat minuten duren bij veel entries

Aangezien die niet uit kan gaan van een index (je kunt helaas geen indices maken op 'berekende' velden) zal die voor alle entries die berekening uit moeten voeren.. Stel je dat eens voor met 50K records ofzo

Dan moet je maar een database nemen die wel functionele indexen aankan of moet je eerst joinen naar de tabel met entries die een postcode hebben (het lijkt me sterk dat je 50000 postcodes van vestigingen in je database hebt).

[Verwijderd]

Thnx voor de snelle reacties,

Adnox

[beetle71]

ACM schreef op 25 June 2003 @ 11:49:
[...]

Dat was toch het hele idee achter de nummering?

groningen: 9000, friesland: 8000, zuid holland: 2000, noord holland: 1000...
etc
Het geld vast niet precies, maar globaal zal het wel zo zijn dat postcodes die dicht bij elkaar liggen ook geografisch dicht bij elkaar zijn.

Mmmmh, ik zou daar maar niet te hard van uitgaan,
Van Sneek( in friesland pc: 86__ ) naar Hoorn (noord holland pc: 16__ ) is 84,5 km fietsen....
Van Sneek naar Maastricht (in limburg pc: 62__) is 313 km fietsen....

Dan hoop ik toch niet dat jij mij op basis van het verschil in postcode (86-16)=70 en (86-62)=24 naar maastricht laat rijden( fietsen/lopen ) als dichtbijzijnde vestiging....

Got the point ??

Een echte oplossing is het aanschaffen van een postcode-locatie database. Daarin is voor elke postcode in nederland een X,Y coordinaat opgenomen. aan de hand waarvan je natuurlijk een echte berekening kunt uitvoeren en met een goed advies kunt komen.

Deze databases zijn te koop vanaf +/- 300 to 400 EUR voor een database puur op postcodecijfers. Maar je kunt ze ook krijgen inclusief 1 of 2 postcodeletters tot zelfs straat-huisnummer gegevens.)
oa. bij www.bridgis.nl en www.geodan.nl

Scheelt dan toch een behoorlijk stukje fietsen naar de dichtsbijzijnde *******

Qua snelheid:
Als je op die coordinaten ook nog eens indices hebt, kun je natuurlijk zonder allerlei berekeningen supersnel de gewenste locaties vinden.

[ Voor 6% gewijzigd door beetle71 op 26-06-2003 13:00 ]

[Verwijderd]

Ik kom er nog niet helemaal uit, ik heb een hulpkolom, waarin de postcodes staan zonder de letters.

Als ik de volgende query gebruik krijg ik geen resultaten terug (wat waarschijnlijk zeer logisch is)

SELECT * FROM table WHERE ABS(2011 - 30) < 10 ORDER BY ABS(2011 - 30)

Waarin 2011 even de postcode is die is ingevoerd. De help-functie van Access hielp me ook niet echt verder, dus ik hoop dat er iemand is die het mij uit kan leggen.

Alvast bedankt

[narotic]

WHERE ABS(2011 - 30) < 10

Lijkt me dat dit nooit true gaat geven nee...

Of zeg ik nu iets doms?

Je moet iets krijgen als:

code:

SELECT * FROM postcodeTable
WHERE ABS(postcodeOmNaarTeZoeken - postcodeTable.postcode) < range
ORDER BY ABS(postcodeOmNaarTeZoeken - postcodeTable.postcode)

[ Voor 55% gewijzigd door narotic op 26-06-2003 16:47 ]

[sjroorda]

Schrijf eens uit...

SELECT * FROM table WHERE 1981 < 10 ORDER BY ABS 1981

Doelloze query, die idd nooit wat teruggeeft (want 1981 is, voor zover ik weet, nooit kleiner dan 10 ).

In mijn query

SELECT * FROM table WHERE ABS(id - 30) < 10 ORDER BY ABS(id - 30)

is dus 'id' (was gewoon gebruikt om even te testen) je postcode(cijfer), '30' is de referentie postcode en 10 is maximale verschil eromheen. Misschien niet een direct duidelijk voorbeeld, maar was ook niet bedoeld om klakkeloos over te nemen, maar om het principe te tonen...

[Verwijderd]

nu werkt het wel, ben nog niet zo ervaren met SQL vandaar.

Bedankt

[Verwijderd]

Nog een vraagje, ik wil behalve op postcode eerst selecteren op website. Er is een kolom website, waar het webadres staat. Als ik sorteer op deze kolom en daarna op postcode, dan worden natuurlijk eerst de websites op alfabetische volgorde gezet en daarin pas gesorteerd op postcode. Ik ben dus opzoek naar een statement die de waarde true of false teruggeeft, zodat ik daar op kan sorteren? Tenminste dat denk ik, tips????

[sjroorda]

Ehhh... gewoon een 'WHERE website = 'blaat.com'' om alleen de juiste website te pakken?

[Verwijderd]

Nee, alle records met een website moeten bovenaan komen te staan, sorteervolgorde is dus als volgt:
alle records waarin een webadres staat bovenaan, gesorteerd op postcode (dichtsbijzijnde postcode eerst)
daarna alle andere records, gesorteerd op postcode (dichtsbijzijnde postcode eerst).

Nu is dit wel op te lossen met twee queries, maar ik doe het liever met eentje. Ik had zelf ongeveer het volgende, maar dat werkte niet:

SELECT * FROM tabel
WHERE ABS(postcodeOmNaarTeZoeken - postcodeTable.postcode) < range
ORDER BY {fn ISEMPTY(webadres)}, ABS(postcodeOmNaarTeZoeken - postcodeTable.postcode)