Lege foutmelding DB-manager na join

[Marielle]

Ik probeer de covid-19 data van het RIVM (cumulatief) te joinen met het cbs bestand met gemeentedata 2019 welke ik verkregen heb met PDOK. Ik wil dit doen met behulp van DB-manager. Beide bestanden heb ik ingeladen in mijn database en voorzien van primary key en indices op de kolommen die ik gebruiken wil. Ook heb ik er voor gezorgd dat de velden die ik koppel van hetzelfde type zijn. Ik wil de geometrie en het aantal inwoners uit het gemeente-bestand. Ik heb daarvoor onderstaande query gebruikt.

select c.*, g.geom, g.inwoners
from covid19 as c
join gemeenten_bewerkt as g
on c.municipality_code=g.code;

Na lang wachten krijg ik de melding “Database-fout” “Er trad een fout op”. Daaronder verwacht ik vervolgens een foutmelding…Alleen is het vak leeg.

Wanneer ik hetzelfde doe via PG-Admin wordt de koppeling goed tot stand gebracht binnen een kleine 10 seconde.
Ik gebruik QGIS 3.14.1

Ik heb uiteindelijk wel via PG-Admin de tabel kunnen maken die ik nodig heb maar het zit me niet lekker. Iemand enig idee wat er aan de hand is met DB-manager?

[raymondnijssen]

Hoi Marielle,

Ik ken de bestanden inhoudelijk niet zo goed maar als je query in pgadmin wel werkt zal die wel kloppen. Een paar ideetjes:

1. Werkt een simpele query wel? Bijvoorbeeld
   SELECT * FROM gemeenten_bewerkt
   Hiermee kun je testen of de connectie tussen QGIS en PostgreSQL correct werkt.

2. Als je toch in pgadmin werkt, is het dan niet handiger om een VIEW te maken van je query en die in QGIS als layer te laden? Dan is je join toch dynamisch.

Groet,
Raymond

[Marielle]

Hoi Raymond,
Bedankt voor je antwoord.

Een simpele query werkt wel. Sterker wanneer ik onderstaande query uitvoer werkt het ook gewoon.
select c.*,g.inwoners
from covid19 as c
join gemeenten_bewerkt as g
on c.municipality_code=g.code;

De geom kolom lijkt het probleem.
Daarom ook geprobeerd om de tabel covid19 via de tabel gemeenten2020 gegeneraliseerd (dus waar alleen de gemeentecode, naam en geometrie in staat) te koppelen en vervolgens het aantal inwoners uit de tabel gemeenten_bewerkt te koppelen. Dat werkt dus wel via db-manager…

Het probleem deed zich bij mij dus alleen voor wanneer ik zowel het aantal inwoners en geometrie uit de tabel gemeenten_bewerkt te koppelen.

Inderdaad is het handiger om een view te maken zodat de join dynamisch is maar in dit geval was dat niet perse nodig.

Ik ben benieuwd of iemand het herkent.

Groeten
Marielle

[fsteggink]

Ik heb geen ervaring met de DB-manager in QGIS, maar het zou kunnen dat er invalide geometrieën in de gemeentetabel zit. Aan de naam “gemeenten_bewerkt” te zien, heb je de gemeenten op de een of andere manier bewerkt. Lukt het wel om die tabel (zonder join met de RIVM-data) rechtstreeks in QGIS te tonen?

Je kunt ook proberen of je met ST_IsValid erachter kunt komen bij welke records er problemen zijn: select st_isvalid(geom) from gemeenten_bewerkt.

[fsteggink]

@raymondnijssen

Als je toch in pgadmin werkt, is het dan niet handiger om een VIEW te maken van je query en die in QGIS als layer te laden? Dan is je join toch dynamisch.

Dan moet je wel de tabel bij iedere verversing leegmaken (delete from xxx) en vullen met bijv. \copy. Als je de tabel weggooit en opnieuw aanmaakt, raak je de view ook kwijt.

[raymondnijssen]

Hoi Frank,

Als deze wel werkt: SELECT * FROM gemeenten_bewerkt dan zijn de geometrieen goed toch?

Maar goed, stap voor stap afpellen tot je het probleem te pakken hebt lijkt me het handigst hier. En aangezien alleen Marielle de data heeft kunnen anderen alleen maar suggesties doen.

Dus nog maar een suggestie van mij: Hebben je tabellen allebei even veel records? Of kan het zijn dat er in de join extra records ontstaan met gemeenten zonder geometrie en/of dubbele “unieke” id’s?

[Marielle]

Mijn idee was ook dat het aan de geometrie lag dus daarom de geometrie eerst geprobeerd te repareren. Daarna nog steeds hetzelfde probleem.
De data van de gemeenten heb ik via pdok verkregen. De COVID data staat op de site van rivm.
Met de data die ik daar rechtstreeks gehaald heb kreeg ik dezelfde lege foutmelding. Ik heb Willem Hofmans het probleem ook voorgelegd en bij hem gebeurde hetzelfde. Hij heeft zelf de data gedownload dus data zonder eventuele fouten die ik aangebracht zou hebben. Het zekere voor het onzekere genomen.

@ Raymond De tabellen hebben niet evenveel records. De COVID tabel heeft er 55000 de tabel met gemeenten 355. De join ging wel goed toen ik geen geometrie meevroeg. Ik dacht dat ik dan uit zou kunnen sluiten dat het aan de join ligt

[Marielle]

Mocht iemand het zelf willen proberen, hier zijn de links naar de data die ik gebruikt heb.
https://geodata.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.csv
https://geodata.nationaalgeoregister.nl/wijkenbuurten2019/wfs?service=WFS

[raymondnijssen]

Ah, je wil iets met de time manager gaan doen?

• Ik zie ook provincies staan in de tabel, die hebben (natuurlijk) geen gemeentecode en krijgen dus sowieso geen geometrie. Die zou ik voor de zekerheid even wegfilteren.
• Ook zou ik proberen om maar 1 datum te selecteren, om te zien of dat wel werkt
• En ik zou een query maken om te checken of alle gemeentecodes wel in beide tabellen voorkomen. Elk jaar worden er wel gemeenten samengevoegd en ontstaan er nieuwe codes.

Je krijgt dan zoiets:

select c.*, g.geom, g.inwoners
from covid19 as c
join gemeenten_bewerkt as g
on c.municipality_code=g.code

WHERE c.municipality_code IS NOT NULL
AND c.date_of_report = ‘2020-04-11 10:00:00’
;

(uit mijn hoofd, niet getest)

[Jonna]

Hoi Mariëlle en Raymond,

Klinkt inderdaad als het Temporal Manager gebeuren dat Kurt Menke beschreef, leuk. Ik heb dat ook net gedaan, maar in een geopackage, dus kwam je probleem niet tegen.

Het is een 1-n relatie, daar kan het probleem wel eens liggen. Je koppelt de gemeenten aan de covid-tabel, waarbij inderdaad NULL-waarden zitten in de kolom municipality code. Dus een wat Raymond zegt lijkt me de oplossing:

WHERE c.municipality_code IS NOT NULL

Ik heb de Join uit de processing toolbox van qgis gebruikt, dan koppel je precies andersom, dus tabel aan gemeente. Hierbij loop je niet tegen het NULL probleem aan.

Edoch, als dezelfde query wel werkt in pgadmin, dan is dat toch raar. Misschien heeft pgadmin al een trigger ingebouwd om NULL-waarden in de hoofdtbel bij een join te negeren? De werking van pgadmin en DB manager is toch net iets anders…

[raymondnijssen]

@Jonna

Edoch, als dezelfde query wel werkt in pgadmin, dan is dat toch raar. Misschien heeft pgadmin al een trigger ingebouwd om NULL-waarden in de hoofdtbel bij een join te negeren? De werking van pgadmin en DB manager is toch net iets anders…

De query wordt in beide gevallen door PostgreSQL uitgevoerd, die het resultaat terugstuurt naar PGadmin of QGIS. Dat bevat dus altijd dezelfde data. Het gaat erom hoe deze applicaties daarmee omgaan.
PGadmin zal de provincie records tonen met lege cellen voor de geometrie en inwoners. QGIS wil graag unieke waarden per polygoon, zodat je ze kunt selecteren (en editten) in je kaart of tabel. Daar gaat het denk ik mis.

En vergeet niet dat je nogal wat data genereert met de query, er ontstaat een kopie van elke gemeente-polygoon voor elke datum in de dataset. Dus 55k polygonen! Vandaar mijn suggestie om 1 datum te selecteren.

[Marielle]

Beste Jonna en Raymond

De lege codes had ik er ook al uitgefilterd idd.
Ik wilde idd de temperal controller gaan toepassen zoals Kurt Menke gedaan had…

“Ook zou ik proberen om maar 1 datum te selecteren, om te zien of dat wel werkt
En ik zou een query maken om te checken of alle gemeentecodes wel in beide tabellen voorkomen. Elk jaar worden er wel gemeenten samengevoegd en ontstaan er nieuwe codes.”
@ Raymond: Wanneer bovenstaand het probleem zou zijn dan was de koppeling in PG-admin toch ook fout gegaan of niet?

[willemhoffmans]

Nog even een paar testjes gedaan, met de volgende conclusies:

• Alleen één datum selecteren: gaat goed

• Alleen één gemeentecode selecteren: gaat goed. Zelfs als je een gemeente met een Invalide geometrie kiest (GM_0744 = Baarle Nassau).

• Wat voor filter je er verder ook op zet: QGIS crasht. Lege codes, of alle gemeenten selecteren behalve GM_0744.

Het lijkt erop dat het een performance / geheugen kwestie in QGIS / DB Manager is. In PgAdmin gaan alle queries sowieso goed.
Verder weet ik wel dat er issues waren met DB Manager vanaf QGIS versie 3.10, maar die leken in QGIS 3.14 opgelost. Blijkbaar is dit een nieuwe …

[fsteggink]

@raymondnijssen

Als deze wel werkt: SELECT * FROM gemeenten_bewerkt dan zijn de geometrieen goed toch?

Helaas niet. Dat komt omdat PostgreSQL dan niks met de geometrie doet, maar de binaire data intact laat. De afnemende client, zoals QGIS, doet wel iets met de data, maar als de geometrie invalide is, kan het mis gaan. Als je SELECT ST_AsText(geom) FROM gemeenten_bewerkt doet, kan dit in sommige gevallen wel een fout geven (bijv. als de binaire data niet juist is) en met ST_IsValid(geom) worden alle invalide geometrieën eruit gefilterd.

Helaas geeft de logging in QGIS lang niet altijd duidelijk aan wat de fout is, maar mocht er bij het laden van een laag vanuit PostgreSQL een fout optreden zonder dat duidelijk waarom de data fout is, dan ligt dat in veel gevallen aan ongeldige geometrieën in je database. Mocht ik weer een keer een dergelijke situatie tegenkomen, dan zal ik je meer informatie sturen.

In het verleden heb ik ongeldige geometrieën in zowel brondata aangetroffen, als data die ik zelf heb gegenereerd dmv bufferen, mergen en andere bewerkingen van data in PostgreSQL. Met name dat laatste kan veel edge cases opleveren. Ik verwacht niet dat QGIS de ongeldige geometrieën maar moet accepteren, want ze zijn niet voor niets ongeldig, maar wellicht kan wel de foutafhandeling worden verbeterd.

[raymondnijssen]

Sorry, misschien was ik niet helemaal duidelijk. Ik bedoelde dat als QGIS alle gemeente-polygonen kan tonen met die eenvoudige query, het probleem niet in ongeldige geometrieen gezocht met worden. Die andere queries veranderen namelijk ook niest aan de geometrie zelf.

En of een geometrie geldig/ongeldig is, daar heeft elke software zijn eigen “mening” over. Dus geldig in de ene applicatie kan ongeldig in de andere applicatie zijn.

Ben het met je eens dat QGIS hier een (betere) foutmelding zou moeten geven. Dat gaat nog wel eens mis als er externe libraries worden gebruikt, bijvoorbeeld PROJ, GDAL, GRASS of SAGA. De fout ontstaat dan in die software en moet netjes doorgegeven worden aan QGIS zodat de gebruiker het te zien krijgt.
In dit geval zit de fout waarschijnlijk in de time-manager (dus in QGIS zelf) die splinternieuw is en wellicht in de combi met een postgresql join niet is getest. Zoals @Jonna al meldde werkt hetzelfde met een qgis-join op een gpkg wel.

[JeroenGB]

Ik heb dit zojuist geprobeerd met DB-manager in QGIS 3.10.4 (64bit windows) maar wel met een gemeentelaag van een andere bron (dus niet de link die Marielle gaf). Daar ging alles prima.
SELECT c.*, g.inw_t, g.geom
FROM covid AS c
JOIN gemeente2020 as G
ON c.gmcode = g.gemcode

resultaat 57510 rijen in 19,109 seconden

Zit het probleem dan in qgis3.14?
Of is er iets aan de hand met de gemeente data van het nationaalgeoregister?

[raymondnijssen]

Ik heb de indruk dat het te maken heeft met de enorme hoeveelheid data die wordt gegenereerd in de query. Misschien dat de database, of QGIS, of een tussenlaag heel snel bepaalt dat de dataset te groot wordt (ofzo), en de uitvoering van de query dan afbreekt? Is de geometrie van die andere gemeentelaag misschien veel eenvoudiger?

[JeroenGB]

Data hoeveelheid zou kunnen. De .shp file van de gemeentelaag die ik heb gebruikt is ongeveer 5,5 Mb. Als ik dan de selectie als nieuwe laag opsla wordt dat vast heel groot (5,5Mb voor 355 polygonen is misschien wel 891 Mb voor 57510 polygonen). Als ik dat in QGIS laadt, dan levert dat vast ook een probleem op, dat heb ik dan weer niet getest.
Ik las de vraag van Marielle dan ook als puur het niet kunnen draaien van de query in DBManager en dat gaat bij mij dus wel goed.
Of toch iets met versie 3.14?

[Marielle]

Ik heb het ook geprobeerd met een ander gemeentebestand (gemeenten gegeneraliseerd) dan lukt het wel. Wanneer ik het grote gemeentebestand uitkleed tot gemeente code, aantal inwoners en geometrie werkt het niet.
Zie ook de testjes die Willem hierboven beschrijft.

[JeroenGB]

Zojuist geprobeerd met de gemeentelaag uit de link van Marielle. Ik heb de data van die laag na filter water=NEE opgeslagen als shp.file, die wordt dan 23Mb, dus een stuk groter dan de laag die ik eerder gebruikte. Dit zou uit kunnen komen op dataset van bijna 4GB.

Overigens lukt het met deze laag wel qgis3.10. Ik heb de query zojuist gedraaid, resultaat 57510 rijen in 177 seconden.
Geen foutmelding dus.