Database gemeenten/plaatsen, meedenken gevraagd!

[SvMp]

Ik ben een database aan het ontwerpen voor een project. Er is één tabel waar ik niet uitkom, welk ontwerp ik ook maak. Het gaat om de opslag van plaatsen, en de ligging er van.

Voorbeeld: De Nederlandse plaats Akkrum ligt in de Nederlandse gemeente Boarnsterhim. Deze gemeente ligt in de Nederlandse provincie Friesland. Friesland ligt in het land Nederland. Nederland ligt in Europa.

Laat ik er maar meteen bij zeggen dat het internationaal moet werken. Elk land heeft een eigen indeling, zo werkt Duitsland met deelstaten, Kreise en gemeentes. De database hoeft geen informatie over loop van de hierarchie (Nederland heeft provincies, Duitsland deelstaten, Nederlandse gemeente ligt in provincie, Europa bestaat uit landen dus een gemeente kan nooit rechtstreeks onder Europa vallen, enz..) te bevatten. Dat ondersteunt de software. Per land wordt ondersteuning geprogrammeerd.

Mogelijke toepassing:

code:

; TABLE plaats
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        soort INTEGER UNSIGNED NOT NULL,

Soort is een belangrijke variabele. Deze geeft het type plaats aan. Om praktische redenen heb ik elk land zijn eigen soort gegeven. Een plaats met soort "Land Nederland" komt natuurlijk slechts 1 keer voor want er is slechts 1 land Nederland.

Voorbeeld:

code:

 100   Land Nederland
 120   Nederlandse provincie
 180   Nederlandse gemeente
 195   Nederlandse plaats

 400   Land Duitsland
 410   Duitse deelstaat
 440   Duitse kreis/district
 480   Duitse gemeente
 495   Duitse plaats

 500   Land Frankrijk
 520   Franse Regio
 530   Departement (departement)
 560   Arrondissement (arrondissement)
 570   Kanton (canton)
 580   Gemeente (commune)
 595   Plaats

code:

; TABLE plaatsnaam
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        plaats INTEGER UNSIGNED NOT NULL REFERENCES plaats(id),
        taal INTEGER UNSIGNED NOT NULL,
        naam VARCHAR(255) NOT NULL,
        status SMALLINT UNSIGNED NOT NULL,

Voor elke taal heb ik een contante. Aan soort in de tabel plaatsnaam is er exoniem (officiële naam in de lokale taal), endoniem (officiële naam gebruikt in andere talen), niet-officiële naam en oude niet meer gebruikte naam (bijvoorbeeld Karl-Marx Stadt, het huidige Chemnitz in Duitsland).

Tot nu toe niet moeilijk. Hiermee kun je elke plaats invoeren.
Plaatsen moeten echter ook gekoppeld worden. Geregistreerd moet worden bijvoorbeeld dat Akkrum in gem. Boarnsterhim ligt, deze gemeente ligt in provincie Friesland, etc..

Daarbij speelt een aantal problemen:
- Er hierarchie ligt niet overal zo mooi vast als in Nederland. In Duitsland bijvoorbeeld bestaan Kreisfreie steden, die liggen dus niet in een Kreis. Je hebt zelfs steden die op zich zelf een deelstaat vormen.
- Niet elke plaats ligt mooi binnen één andere (grotere) plaats. Voorbeeld: In Frankrijk zijn er gemeentes die verdeeld liggen over twee kantons. Dus sommige gemeentes bestaan uit twee delen.
- Het verleden: Ik wil ook de historie en toekomst er in hebben van plaatsen en hun verhoudingen. En daar begint de ellende pas echt. Verder dan 150 jaar terug is gelukkig niet nodig. Maar dat maakt het fenomeen gemeentelijke herindelingen niet minder rampzalig.

Voorbeeld: Boarnsterhim is in 1984 ontstaan uit drie gemeenten. Twee daarvan zijn in zijn geheel in het nieuwe Boarnsterhim opgegaan. Eén daarvan is deels opgegaan in Boarnsterhim en deels in een andere gemeente.


Aanvankelijk dacht ik aan de volgende oplossing: Tabel met plaatselen, met een jaar_van en een jaar_tot, en de parent. Maar dat betekent dat je bij de eerstvolgende gemeentelijke herindeling alle plaatsen afzonderlijk moet wijzigen. Daarnaast worden gegevens dubbel opgeslagen, veel plaatsen hebben een identieke geschiedenis.

Dit is mijn huidige oplossing:

code:

; TABLE plaatsdeel
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        plaats INTEGER UNSIGNED NOT NULL REFERENCES plaats(id),
        parent INTEGER UNSIGNED NOT NULL REFERENCES plaatsdeel(id),
        tot INTEGER UNSIGNED NOT NULL,
        opgegaan_in INTEGER UNSIGNED NOT NULL REFERENCES plaatsdeel(id),

Elke plaats kan meerdere delen hebben. Parent geeft aan waar het deel ligt. Bij alle Nederlandse plaatsen hebben de delen dezelfde parent, maar in het eerder genoemde voorbeeld Frankrijk hoeft dit niet altijd zo te zijn. Ander voorbeeld is Turkije, dat deels in Azië, deels in Europa ligt. Alle andere tabellen in de database verwijzen dan ook naar een plaatsdeel, nooit naar de plaatstabel zelf.

Tot slot de variabelen "tot" en "opgegaan_in": Deze regelen veranderingen zoals herindelingen.
tot en opgegaan_in beide 0? -> Plaats bestaat nog
tot>0 en opgegaan_in 0? -> Plaats is opgeheven (komt zelden voor)
tot>0 en opgegaan>0 -> Plaats is opgegaan in andere plaats, recentelijk bijvoorbeeld gemeentes Moordrecht, Nieuwerkerk aan den IJssel en Zevenhuizen-Moerkapelle die per 1 januari 2010 opgaan in gemeente Zuidplas.


Tot nu toe nog steeds niet al te moeilijk. Als concreet voorbeeld gebruik ik de gemeente Boarnsterhim, omdat deze is ontstaan uit 3 gemeentes en naar verwachting in de toekomst weer wordt opgedeeld, bijvoorbeeld tussen Leeuwarden en Heerenveen.

Deze praktijksituatie levert op:
Boarnsterhim, 1 deel, nog bestaand.
Idaarderadeel 1 deel, opgegaan in Boarnsterhim sinds 1984
Rauwerderhem 1 deel, opgegaan in Boarnsterhim sinds 1984
Utingeradeel 2 delen, deel 1 opgegaan in Boarnsterhim sinds 1984, de andere in Skarsterlân.


Voorbeeld: De plaats Akkrum heeft als parent Idaarderadeel. Met een query is de achterhalen dat Akkrum momenteel in Boarnsterhim ligt.
Akkrum kan ook gekoppeld worden aan Boarnsterhim, maar dan is niet de achterhalen dat Akkrum oorspronkelijk in Idaarderadeel lag. Het eerste nadeel aan dit ontwerp: Potentiele fouten in de database. Software moet goed in de gaten worden dat plaatsen echt aan de oudste oorspronkelijke gemeentes worden gekoppeld, anders is de geschiedenis niet te achterhalen.

Met dit nadeel valt nog te leven.

Het wordt pas echt lastig als Boarnsterhim gaat splitsen. Twee delen, een deel Heerenveen en een deel Leeuwarden. In de database moeten minimaal 6 (!) delen Boarnsterhim komen.

Want:

Idaarderadeel -> Boarnsterhim -> Heerenveen
Idaarderadeel -> Boarnsterhim -> Leeuwarden
Rauwerderhem -> Boarnsterhim -> Heerenveen
Rauwerderhem -> Boarnsterhim -> Leeuwarden
Utingeradeel -> Boarnsterhim -> Heerenveen
Utingeradeel -> Boarnsterhim -> Leeuwarden
Utingeradeel -> Skarsterlân

Het wordt alleen eenvoudiger als deze mogelijke herindeling plaatsvindt over de oude gemeentegrenzen van 1984, maar die kans is niet zo groot. Ik zeg overigens niet dat die herindeling plaats gaat vinden, maar ik gebruik het als voorbeeld voor mijn ontwerp en de belemmeringen.

Het wordt dus behoorlijk complex. Stel dat over een x aantal jaren ineens bijvoorbeeld de provincies worden vervangen door gewesten met compleet nieuwe grenzen, dan wordt het helemaal een chaos.

Ik ben dan ook op zoek naar suggesties voor iets beters, met de volgende eisen:
- Internationaal, elk mogelijke land moet ondersteund kunnen worden
- Goed werkend met het verleden (ca. 150 jaar), heden en de toekomst (ook extreme situaties).
- Zo min mogelijk mogelijkheden om foute links te leggen waardoor informatie niet meer volledig uit de query's naar voren komt.
- Werkbaar. Er moet een userinterface omheen gebouwd kunnen worden zonder al te idioot veel programmeerwerk. Het huidige ontwerp voldoet niet aan deze eis, het wordt knap ingewikkeld als een gemeente als Boarnsterhim al in 6 delen wordt geknipt voor een niet ondenkbaar toekomstscenario.

[Soggney]

normaliseren! (ten eerste)
Ik vermoed dat 3 tabellen hiervoor een oplossing kunnen bieden
1 tabel met de plaatsen, 2de tabel met de parent-child relatie van de gemeente, met timestamp
3de tabel die het begin en einde van plaatsen bijhoud. -> dus timestamp begin, timestamp einde, opgegaan in id van andere plaats, dit zie ik net kan ook in tabel 1 worden bijgehouden.

Utingeradeel -> Boarnsterhim -> Heerenveen : Deze relatie is nutteloos, want Utingeradeel is opgegaan in Boarnserhim (bestaat niet meer), maar Boarnserhim is een deel van Heerenveen, terwijl deze beide nog bestaan. Is een totaal andere relatie, dus is dit de grootste verwarring.

[Kalentum]

Ik denk dat dit zeer lastig wordt om in een relationele database op te slaan. Misschien moet je eens kijken of CouchDB of MongoDB wat voor je is. Deze laten het toe om data semi-gestructureerd op te slaan.

Je kan dan data opslaan als:

code:

Plaats: Heerenveen
Provincie: Friesland
Alternatief: ['It Herrenfean','Herenveen']

of

code:

Plaats: Duitsdorp
Kreis: Kleve

Regels per land leiden tot een andere structuur maar dat kun je daar prima in kwijt.

[SvMp]

Soggney schreef op dinsdag 08 december 2009 @ 16:31:
normaliseren! (ten eerste)
Ik vermoed dat 3 tabellen hiervoor een oplossing kunnen bieden
1 tabel met de plaatsen, 2de tabel met de parent-child relatie van de gemeente, met timestamp
3de tabel die het begin en einde van plaatsen bijhoud. -> dus timestamp begin, timestamp einde, opgegaan in id van andere plaats, dit zie ik net kan ook in tabel 1 worden bijgehouden.

Utingeradeel -> Boarnsterhim -> Heerenveen : Deze relatie is nutteloos, want Utingeradeel is opgegaan in Boarnserhim (bestaat niet meer), maar Boarnserhim is een deel van Heerenveen, terwijl deze beide nog bestaan. Is een totaal andere relatie, dus is dit de grootste verwarring.

\
Dit is een goed idee. Eens dat de relatie Utingeradeel -> Boarnsterhim -> Heerenveen zinloos is, maar de software moet daar uiteraard iets zinvols van bakken. In PHP kan ik functies schrijven die bij elk jaartal de juiste parent zoeken bijvoorbeeld.

Tabel nummer 3 kan zelfs achterwege gelaten worden. Hoe minder invoerwerk, hoe beter namelijk.
Voorbeeld: Aangezien alle plaatsen van Utingeradeel vanaf 1984 in Boarnsterhim liggen, kan de software concluderen dat Utingeradeel in zijn geheel is opgegaan in Boarnsterhim.

Wel aan nadeel: Van elke plaats dus een lijstje parents. Veel dubbele opslag. In Idaarderadeel lagen bijvoorbeeld 8 plaatsen, die nu alle 8 in Boarnsterhim liggen. Toch moet dan bij elk van die 8 plaatsen afzonderlijk geregistreerd worden dat deze tot 1984 in Idaarderadeel lag, en vanaf 1984 in Boarnsterhim.
Voordeel: Het ontwerp is simpel. Het moet ook niet moeilijk zijn om wizards te programmeren die zo'n gemeentelijke herindeling automatiseren, dus een groep plaatsen tegelijk verplaatsen naar een nieuwe gemeente ipv. afzonderlijk wijzigen per plaats (=veel werk).


Dit kan ook weer opgelost worden, door plaatsen te groeperen. Elke plaats hoort bij een plaatsgroep. Een plaatsgroep is een groep plaatsen met dezelfde geschiedenis. De 8 plaatsen die vroeger in Idaarderadeel lagen hebben bijvoorbeeld dezelfde geschiedenis. Stel dat in de toekomst 3 van deze plaatsen onder Heerenveen gaat vallen door een nieuwe indeling, dan wordt deze groep geplist in 2 nieuwe groepen plaatsen met een overeenkomstige geschiedenis, een groep van 5 en een groep van 3. Per groep wordt een tijdje events bijgehouden, bijvoorbeeld overgang naar een andere parent.

[ Voor 13% gewijzigd door SvMp op 08-12-2009 16:47 ]

[HuHu]

Meestal heb je genoeg aan:

naam
adres_1
adres_2
adres_3
postcode
plaats
regio (staat, provincie, gewest, enz...)
land

Dat is ook de informatie die je in kunt geven bij de zakelijke software van TNT (post en express) of UPS.

[SvMp]

@HuHu: Ik ben iets totaal anders aan het maken dan een gemiddeld zakelijk systeem. Het gaat over oa. geschiedenenis, vandaar ook dat historische verhoudingen vastgelegd moeten worden.

[defcon84]

download deze database eens: http://db.hostip.info/mirror/hostip_current.sql.gz
geeft je mss wat informatie..
en je hebt er meteen een lijst van landen/cities bij ^_^

[Varienaja]

Ik heb zoiets al eens aan de hand gehad. Mijn idee destijds:

Tabel 'plaatsen'
ID, NAAM, ...

Tabel 'plaatsrelatie'
ID, PLAATS_ID, DATUM_BEGIN, DATUM_EIND, LIGTIN_PLAATS_ID

Nu kan je elke plaats (gemeente, provincie, land, ...) in elke andere plaats (gemeente, provincie, land, ...) laten liggen. Je kunt de relaties historisch juist opslaan (datum_begin en datum_eind). En je weet altijd de op-dit-moment geldige relatie, omdat je daar de einddatum leeglaat. Je kunt zelfs een plaats in twee of meer gemeenten laten liggen: gewoon twee relatie-records creëren.

[Alex]

Vrijwel volledig eens zoals Varienaja het beschrijft, zo zul je het in vrijwel elk CMS systeem kunnen opslaan. De relatie an sich is een entiteit waar eventueel nog op beschreven kan worden wat deze entiteit is. Zoals bijvoorbeeld een type zoals je het beschrijft. Een plaats heeft namelijk zelf meerdere relaties:
Utrecht(stad) ligt in Utrecht(provincie)
Utrecht(stad) ligt in Nederland(land)
Utrecht(provincie) ligt in Nederland(land)
Dit maakt zoekpatronen ook makkelijker(alle steden in Nederland). Ook los je hiermee op dat bijvoorbeeld gehele provincies en steden bepaalde perioden in andere landen hebben gelegen.

Ik zou uitkomen op de volgende tabellen:
Location:
ID, Name
(1, 'Utrecht')
(2, 'Nederland')

Relation_Type:
ID, Name
(1, 'Provincie')
(2, 'Land')

Relation:
ID, Child_Location_ID, Parent_Location_ID, StartDate, EndDate, Relation_Type_ID
(1, 1, 1, TIMESTAMP, TIMESTAMP, 1) Utrecht -> 'Provincie' -> Utrecht
(2, 1, 2, TIMESTAMP, TIMESTAMP, 2) Utrecht -> 'Land' -> Nederland

In dit geval is het zover genormaliseerd dat je Utrecht(omdat het dezelfde naam heeft als de Provincie) zelfs dubbel hergebruikt.
De vraag is of dit handig is omdat je geen onderscheid meer maakt tussen het type Locatie.

Een andere optie is:
Location:
ID, Name, Location_type
(1, 'Utrecht',1)
(2, 'Utrecht',2)
(3, 'Nederland', 3)

Location_Type:
ID, Name
(1, 'Stad')
(2, 'Provincie')
(3, 'Land')

Relation:
ID, Child_Location_ID, Parent_Location_ID, StartDate, EndDate
(1, 1, 2, TIMESTAMP, TIMESTAMP) Utrecht (type: Stad) -> Utrecht (type: Provincie)
(2, 1, 3, TIMESTAMP, TIMESTAMP) Utrecht (type: Stad) -> Nederland (type: Land)
(3, 2, 3, TIMESTAMP, TIMESTAMP) Utrecht (type: Provincie) -> Nederland (type: Land)

Nu kun je argumentern dat relatie 2 in theorie overbodig is, maar dit is afhankelijk of bijvoorbeeld een bepaalde locatie eventueel bij meerdere parents zou kunnen horen. Het terugherleiden vanaf de diepste node terug wordt anders heel lastig.

[BCC]

Of nog een abstractielaag hoger:

code:

 id, name, alt_name, parent_id

Waarbij de parent_id naar dezelfde tabel verwijst. Waarschijnlijk wil je dan wel iets van een nested set implementeren.

[dik_voormekaar]

Geonames.org heeft een complete, gratis te gebruiken database.
Kijk vooral onder "Download server" en "Webservices overview".

De tabel die ze gebruiken is:

SQL:

CREATE TABLE geoname ( 
geonameid int PRIMARY KEY, 
name varchar(200), 
asciiname varchar(200), 
alternatenames varchar(4000), 
latitude decimal(10,7), 
longitude decimal(10,7), 
fclass char(1), 
fcode varchar(10), 
country varchar(2), 
cc2 varchar(60), 
admin1 varchar(20), 
admin2 varchar(80), 
admin3 varchar(20), 
admin4 varchar(20), 
population int, 
elevation int, 
gtopo30 int, 
timezone varchar(40), 
moddate date 
) CHARACTER SET utf8; 

[SvMp]

dik_voormekaar schreef op dinsdag 08 december 2009 @ 20:49:
Geonames.org heeft een complete, gratis te gebruiken database.
Kijk vooral onder "Download server" en "Webservices overview".

Super! Leuk dat ik dit nu weet, ondanks dat ik niet rechtstreeks naar toe iets vroeg omdat ik het bestaan niet wist. De structuur vind ik niet zo geschikt, maar zo'n database, compleet met coördinaten, is wel geweldig om te importeren.

[tecsman]

Varienaja schreef op dinsdag 08 december 2009 @ 18:37:
Ik heb zoiets al eens aan de hand gehad. Mijn idee destijds:

Tabel 'plaatsen'
ID, NAAM, ...

Tabel 'plaatsrelatie'
ID, PLAATS_ID, DATUM_BEGIN, DATUM_EIND, LIGTIN_PLAATS_ID

Nu kan je elke plaats (gemeente, provincie, land, ...) in elke andere plaats (gemeente, provincie, land, ...) laten liggen. Je kunt de relaties historisch juist opslaan (datum_begin en datum_eind). En je weet altijd de op-dit-moment geldige relatie, omdat je daar de einddatum leeglaat. Je kunt zelfs een plaats in twee of meer gemeenten laten liggen: gewoon twee relatie-records creëren.

Is het geen bezwaar dat je het inzicht tussen plaatsen en gemeenten op die manier kwijt raakt?

Voorbeeld: de gemeente Dalfsen wordt samengevoegd met de gemeente Nieuwleusen, de 'nieuwe' gemeente heet Dalfsen. De twee plaatsen Dalfsen en Nieuwleusen blijven echter wel apart (grote afstand).

In jouw tabel zou het lijken alsof de plaats Nieuwleusen is opgegaan in de plaats Dalfsen en dat is niet zo.