Regionale search: hoe map ik postcodes op produkten?

[Anoniem: 42791]

Hallo,

Ik ben bezig met een webapplicatie waarin ik gebruikers de mogelijkheid wil geven om een produkt X te zoeken. Ik wil dat gebruikers een produkt en de eerste 4 cijfers van hun postcode kunnen invoeren. De webapplicatie moet dan de produkten laten zien die in dat postcodegebied geleverd worden. Nu weet ik niet hoe ik het beste de produkten op postcodegebieden kan mappen in de database.

Eerste plan was om alleen een tabel produkt_postcode(produkt_id, postcode) te maken. Echter, stel dat er 500 produkten uit de catalogus in heel Nederland geleverd worden dan levert dit al 500 * 4750 = 2.4 miljoen records op! (Er zijn zo'n 4750 postcodes in Nederland volgens deze pagina)

Het alternatief:

Ik maak een aparte tabel met 1 kolom waarin ik produkten zet die in heel Nederland geleverd worden: heel_nl(produkt_id). Dan hoef ik die produkten niet in de produkt_postcode tabel op te nemen en dat scheelt elke keer 4750 records in produkt_postcode. Ik zou dit idee verder door kunnen voeren en provincies afsplitsen: produkt_provincie(produkt_id, provincie). Met de laatste oplossing komen alleen produkten die in een gebied kleiner dan 1 provincie geleverd worden in de produkt_postcode tabel. Bovendien wordt de tabel heel_nl dan overbodig, omdat ik een produkt op alle 12 provincies kan mappen.

Voor de invoer van de produkten in het admin-gedeelte gebruik ik de 4 positie postcodetabel, zodat je gelijk per provincie en/of gemeente en/of plaats kan invoeren.

Wat is wijsheid? Mijn gevoel zegt dat simpelweg elk produkt op alle postcodes waar het geleverd wordt mappen in 1 tabel niet slim is. De eerste vraag lijkt me dus duidelijk: is het afsplitsen van provincies de juiste manier? Hoe doet bijvoorbeeld adslwinkel.nl dit?

Ik gebruik overigens Mysql 4.1, maar ik zoek een database-onafhankelijke oplossing.

[ Voor 12% gewijzigd door Anoniem: 42791 op 26-11-2005 01:35 ]

[T-MOB]

Het postcodesysteem is toch redelijk georganiseerd. Postcodes die weinig van elkaar verschillen liggen doorgaans dicht bij elkaar. Je kunt dit gegeven gebruiken door ranges van postcodes te koppelen aan een product. Voor een product dat alleen in Apeldoorn en Enschede te krijgen is sla je dan twee ranges op (7300-7399 en 7500-7548 ongeveer). Voor een product dat landelijk te krijgen is sla je 0000-9999 op. Voor producten met eenvoudige dekking kost dit je slechts een of enkele rijen in je koppeltabel. In het ingewikkeldste geval krijg je 2375 records.

[chris]

Opzich is product_postcode de 'mooiste' oplossing. Ik heb zelf geen ervaring met tabellen van 2.4 miljoen records, maar dat zal inderdaad niet optimaal zijn qua snelheid.

Misschien zou je ook nog aan één tabel kunnen denken, dat kan dan zoiets zijn:

code:

product_id | type | value

waarbij type een enum is 'lokaal', 'provinciaal', 'nationaal'.

Ik heb verder geen idee van je data, maar je zou eventueel nog een veld/tabel 'exclusive' oid kunnen toevoegen, zodat als je bijvoorbeeld 2 rows hebt, b.v.

code:

product_id exclusive type     value
1          false     national '1'
1          true      local    '3515'

Zodat je kan zeggen dat een product overal voorkomt, behalve in de postcode 3515.

[MSalters]

Logischerwijs hebben we het over 4750*500 bits. Dat is peanuts, en als je die bits opslaat per postcode is de search supersnel: vind record, lees de 500 bits, kijk welke gezet zijn => produkten bekend.

Nu is het probleem dus of je zoiets efficient in een database kunt stoppen. Ik denk dat ik de theorie de theorie zou laten, en het op zou lossen met 16 productgroepkolommen van 32 bits elk. De waardes 0 en 0xFFFFFFFF komen ongetwijfeld vaak voor, dus dat zou ik hardcoden, maar voor de rest is het per productgroep alle 32 producten aflopen.

Natuurlijk is het makkelijker als een database een datatype bitvector<N> heeft, wat bovendien te upgraden is, maar DBs zijn niet echt gebouwd op UDTs.

[T-MOB]

MSalters schreef op zaterdag 26 november 2005 @ 16:09:
Logischerwijs hebben we het over 4750*500 bits. Dat is peanuts, en als je die bits opslaat per postcode is de search supersnel: vind record, lees de 500 bits, kijk welke gezet zijn => produkten bekend.

Als ik je goed begrijp wil je per postcode in een serialized veld opslaan welke producten er te krijgen zijn? Dat lijkt me niet zo'n strak plan aangezien je daarmee je database structuur moet omgooien als je meer dan 512 (of wat de hardcoded limiet ook wordt) producten krijgt. Bovendien moet je een complete tablescan uit voeren als je vanuit een product-id wil bepalen in welke regio het te verkijgen is.

Daarbij kun je (waarschijnlijk) efficienter opslaan door ranges postcodes te gebruiken. Daarmee behoud je tevens de mogelijkheid om indexen in te zetten om snel te zoeken.
Als je koppeltabel er ongeveer zo uitziet:

code:

productid | rangestart | rangeend
  1       | 7500       | 7548
  1       | 7300       | 7398
  2       | 7500       | 7532
  3       | 0          | 9999

Dan gebruik je per product per range 32 bits (als je 16bits int / MySQL smallint gebruikt). Dat betekent dat je tot gemiddeld 148 ranges aan postcodes per product minder kwijt bent dan een situatie waar je een bitwise product-postcode koppeling aanbrengt (4750 bits per product).

Gezien de systematische opzet van het postcodesysteem is een scenario waarin veel ranges moeten worden opgegeven niet zo waarschijnlijk. Aangrenzende postcodes liggen doorgaans ook in werkelijkheid bij elkaar in de buurt. Gegeven dat een product leverbaar is in 7500, is de kans dat er geleverd kan worden in 7501 significant hoger dan de kans dat er geleverd kan worden in 2206.

[Emmeau]

Volgens mij zit er iets struktureel fout in je data model.

Producten worden verkocht door een bedrijf, het bedrijf heeft een postcode

En dan hoef je maar een paar postcodes op te slaan.

[Anoniem: 42791]

@T-MOB: Ik heb even de postcodekaart zitten bekijken en ik ben bang dat dit in veel gevallen niet zo heel netjes gaat werken. Iemand in postcodegebied 12 zou best maar 500 meter van postcodegebied 37 kunnen wonen, maar de applicatie zal de produkten die in 37 geleverd worden niet teruggeven. Jammer, want het was een goede oplossing geweest.

@Chris: Ik zie van jouw idee niet zo goed het voordeel. Er moet sowieso een mapping komen tussen produkten en postcodes, of die nou letterlijk in een produkt_postcode tabel komt of impliciet en een produkt_provincie tabel.

@Emmeau: het gaat erom dat mensen produkten kunnen zoeken die geleverd worden in hun regio. Dit produkt is een type cursus en dat wordt in-company gegeven of thuis, op een cursuslokatie, alleen regionaal, per stad, per provincie, etc. De postcodes van de bedrijven sla ik ook op, maar gewoon in een soort company_specs tabelletje.

@Msalters: Dit gaat boven mijn pet We hebben het over 4750*500 4 cijferige postcodes, toch niet over bits?

Ik heb nog even over het idee nagedacht om provincies af te splitsen en volgens mij is dat wel een aardige oplossing die redelijk schaalbaar is, maar ik weet niet of het performance-wise slim is.



(1) Gebruiker typt een produktnaam en postcode 1234 in
(2) Zoeken in produkt-tabel (op naam, omschrijving) naar overeenkomende produkten -> levert lijst met produkt_id's op.
(3) Zoeken welke provincie bij postcode 1234 hoort (postcodetabel). Stel dit is "Zeeland".
Ik heb nu een lijst van produkt_id's, de postcode en de provincie bij de postcode.
(4) Per $produkt_id zoeken in produkt_provincie of het ($produkt_id, "zeeland") koppel bestaat. Als het bestaat wordt het $produkt in heel Zeeland geleverd en gaan we naar (6) Als het niet bestaat gaan we naar (5)
(5) Zoeken naar het koppel ($produkt_id, 1234) in de produkt_postcode tabel. Bestaat dit? --> ga naar (6) Bestaat het niet? --> Ga naar (7)

(6) Produkt wordt geleverd in regio 1234 en dus weergegeven in de resultaten
(7) Produkt wordt niet geleverd in de regio 1234 en dus niet weergegeven

Voordeel van deze methode: met een kleine search op de produkt_provincie tabel pak ik al alle produkten die landelijk en in de hele betreffende provincie geleverd worden. Ik hoef dan alleen nog de postcode_tabel te doorzoeken, waarin zich alleen regionale cursussen bevinden. Dat zal dus ook aardig snel gaan.

Bij stap (3) zou het kunnen dat er twee provincies terugkomen, omdat postcodegrenzen en provinciegrenzen niet parallel lopen. Dit is echter geen drama. Als een produkt in de hele provincie Zuid-Holland geleverd wordt, dan wordt het ook wel een half postcodegebied verder in de aangrenzende provincie geleverd. Ik check bij twee provincies dus of het produkt in een van beide provincies geleverd wordt.

[ Voor 7% gewijzigd door Anoniem: 42791 op 26-11-2005 18:19 ]

[T-MOB]

Anoniem: 42791 schreef op zaterdag 26 november 2005 @ 18:15:
@T-MOB: Ik heb even de postcodekaart zitten bekijken en ik ben bang dat dit in veel gevallen niet zo heel netjes gaat werken. Iemand in postcodegebied 12 zou best maar 500 meter van postcodegebied 37 kunnen wonen, maar de applicatie zal de produkten die in 37 geleverd worden niet teruggeven. Jammer, want het was een goede oplossing geweest.

Het probleem van aangrenzende postcodes hou je altijd, ook als je eerst op provincie gaat selecteren. Dat is een probleem dat je moet oplossen bij het invoeren. Als je bij het invoeren aangeeft dat de dienst beschikbaar is in bijvoorbeeld gebied 1200-1225, 1250-1275 en gebied 3700-3727. Dan kun je door product-id's selecteren waar range-start kleiner en range-end groter is dan de postcode heel makkelijk bepalen welke diensten leverbaar zijn op een specifieke postcode.

[NetForce1]

T-MOB schreef op zaterdag 26 november 2005 @ 17:26:
[...]

Als ik je goed begrijp wil je per postcode in een serialized veld opslaan welke producten er te krijgen zijn? Dat lijkt me niet zo'n strak plan aangezien je daarmee je database structuur moet omgooien als je meer dan 512 (of wat de hardcoded limiet ook wordt) producten krijgt. Bovendien moet je een complete tablescan uit voeren als je vanuit een product-id wil bepalen in welke regio het te verkijgen is.

Ik denk dat MSalters het precies andersom bedoelt, nl per product opslaan in welke postcodes het te krijgen is. Dat kan prima in een bitmask, want het lijkt me sterk dat er postcodegebieden bijkomen. Alleen hoe het hele zaakje dan gaat performen weet ik niet.


Nu ik het nog eens teruglees bedoelde hij dat dus toch niet

[ Voor 9% gewijzigd door NetForce1 op 26-11-2005 19:09 ]

[P_de_B]

Je hebt alleen in het geval dat de producten in _alle_ postcodes geleverd worden 4750 records voor in de koppeltabel voor dat product. Immers, je hoeft geen record weg te schrijven in de koppeltabel als het product niet in de postcode geleverd wordt. Ik weet niet of dit al veel scheelt?

Daarnaast moet je denk ik in de koppeltabe zoals al gezegd is werken met een startrange en een eindrange dat scheelt ook al heel veel. Ik denk dat je dan op een paar honderduizend records uitkomt, met de juiste indexen moet dat peanuts zijn voor een database. Je kunt gewoon een JOIN gebruiken tussen de postcodekoppeltabel en de producttabel, dat moet mysql wel redelijk snel kunnen afhandelen.

[Anoniem: 14829]

Beetje offtopic, maar o.a. hier is een postcode tabel te vinden met de bijbehorende geografische locatie. Aan de hand hiervan moet het vrij simpel zijn om te berekenen welke postcodes bv. binnen een straal van 50 km vallen.

[Skaah]

Anoniem: 42791 schreef op zaterdag 26 november 2005 @ 01:28:
Eerste plan was om alleen een tabel produkt_postcode(produkt_id, postcode) te maken. Echter, stel dat er 500 produkten uit de catalogus in heel Nederland geleverd worden dan levert dit al 500 * 4750 = 2.4 miljoen records op! (Er zijn zo'n 4750 postcodes in Nederland volgens deze pagina)

Pff, maak het jezelf niet zo moeilijk. Zo'n kruistabel met een paar miljoen records is een eitje voor elke DBMS. Het enige wat je nog hoeft te doen is een indexje leggen. Zeker met alleen maar integers heb je supersnelle joins.

Dus verspil je eigen tijd niet, en ga voor de meest genormaliseerde oplossing.

[Anoniem: 42791]

Hm, zo te lezen is de simpelste oplossing nog wel het proberen waard. Ik splits desondanks sowieso heel_nl af om de produkt_postcocde tabel niet onnodig groot te maken en alle andere cursussen prop ik in die produkt_postcode tabel. Ik krijg dan 2 searches, 1 op een numerieke kolom met produkt_id's die in heel NL geleverd worden en een op de produkt_postcode tabel. Moet snel gaan Op deze manier kom ik ook niet in problemen met postcode- en provinciegrenzen e.d. Bedankt voor het meedenken

[ Voor 21% gewijzigd door Anoniem: 42791 op 27-11-2005 15:48 ]

[Anoniem: 21726]

Skaah schreef op zondag 27 november 2005 @ 12:16:
[...]

Pff, maak het jezelf niet zo moeilijk. Zo'n kruistabel met een paar miljoen records is een eitje voor elke DBMS. Het enige wat je nog hoeft te doen is een indexje leggen. Zeker met alleen maar integers heb je supersnelle joins.

Dus verspil je eigen tijd niet, en ga voor de meest genormaliseerde oplossing.

Die 2.4 miljoen rijen is voor een RDBMS inderdaad niet zo'n probleem. Maar afhankelijk van de applicatie kan wel het onderhoud op zo'n tabel gigantisch zijn (weet niet hoeveel gegevens er over een periode wijzigen...).

[Anoniem: 42791]

Postcodegegevens zullen niet zo snel wijzigen en bovendien mogen de deelnemers deze wijzigingen zelf doorvoeren via een admin panel. De invoerkant wordt nog een potje ingewikkelde forms bouwen, maar da's meer iets voor W&G

[Anoniem: 16460]

Maak gewoon ranges aan van hoogstens 100. Van 1000 tot 1100 enz. Je ziet op de postcodekaart dat die postcodegebieden niet zo heel groot zijn. Dat moet wel lukken voor het bezorgen? Of is het zo specifiek?

Het is in mijn ogen compleet zinloos al die postcodes op te slaan. Wil je er per product ook 100 000 opgeven waar dat product mogelijk bezorgd kan worden? Lijkt me een ondoenlijke zaak.

[Anoniem: 42791]

De invoer gaat wel wat flexibeler dan het invoeren van individuele postcodes natuurlijk. Met die postcodetabel kun je per plaats, gemeente of provincie in laten voeren en de postcodes zoek je er gewoon bij in die tabel Ranges ben ik gewoon geen fan van. Als letterlijk opslaan performance-wise werkt dan ga ik daar gewoon voor. Ik splits trouwens wel cursussen af die in heel NL gegeven worden. Dat zal al een grote slok op een borrel schelen.

[ Voor 7% gewijzigd door Anoniem: 42791 op 28-11-2005 14:59 ]