Qgis: connecting a Geomedia mdb warehouse

...an open-source driver for Geomedia has been released for gvSIG. It's the extMDB extension and works pretty well. Anyway, let's talk about Qgis...

Some tricky ways to display the content of a Geomedia Access Mdb in a Windows installed Qgis.
First of all, you need Gdal driver to be >= 1.9.0 release, so if you have an old install of Qgis, please download Osgeo4W setup and install or update Qgis.

When ready,
  1. Try to load your data
    Add a regular Vector Layer and leave File as source type (do not switch to database), browse for your Geomedia mdb file and select it.

    If everything is ok, Qgis will ask you which layers to load and their spatial reference, then you'll get the feature classes on the map.

If Qgis fails to access to your file, follow next steps.
This implies to modify the metadata structure, so backup your file first. Unfortunately, you're going to see most of (but not all) the database tables.
  1. First workaround
    Open Geomedia mdb with Microsoft Access
    • delete feature classes with no records (pay attention not to delete the Geomedia metadata tables)
    • delete those records in table GeometryProperties where GeometryType = 5
    • delete those records in table GFeatures where PrimaryGeometryFieldName is empty

Not enough? Go forward
  1. Second workaround
    Again, Open Geomedia mdb with Microsoft Access and perform those Sql queries in this order, deleting all the resulting records
    • SELECT GFeatures.* from
      GFeatures WHERE GFeatures.FeatureName in
      (SELECT GFeatures.FeatureName
      FROM (SELECT FieldLookup.FeatureName
      FROM FieldLookup INNER JOIN GeometryProperties ON FieldLookup.IndexID = GeometryProperties.IndexID
      GROUP BY FeatureName HAVING Count(FeatureName)>1) AS QUERY INNER JOIN GFeatures ON QUERY.FeatureName = GFeatures.FeatureName);


    • Select GeometryProperties.* from
      GeometryProperties WHERE GeometryProperties.IndexID IN
      (SELECT GeometryProperties.IndexID
      FROM ((SELECT FieldLookup.FeatureName
      FROM FieldLookup INNER JOIN GeometryProperties ON FieldLookup.IndexID = GeometryProperties.IndexID
      GROUP BY FeatureName HAVING Count(FeatureName)>1) AS QUERY INNER JOIN FieldLookup ON QUERY.FeatureName = FieldLookup.FeatureName) INNER JOIN GeometryProperties ON FieldLookup.IndexID = GeometryProperties.IndexID);


    • SELECT GeometryProperties.*
      FROM (SELECT FieldLookup.*, GFeatures.FeatureName
      FROM FieldLookup LEFT JOIN GFeatures ON FieldLookup.FeatureName = GFeatures.FeatureName
      WHERE (((GFeatures.FeatureName) Is Null))) AS Query1 INNER JOIN GeometryProperties ON Query1.IndexID = GeometryProperties.IndexID;


Now, you should be out of troubles.

1 comment:

  1. Hello Toni!

    I was able to open [b]one[/b] smaller(ish) GeoMedia .mdb, but other than that one lucky shot I am usually in trouble -> QGIS cannot load the [b]cleaned[/b] .mdb flietype database.

    Would You be able to help me in this topic? If You will have some free time, please send me a reply / message and I will throw' You an .mdb example, that I would have to open somehow...

    Thank You in advance!

    Cheers, ZG

    ps: Your blog is awesome, keep it up the gr8 work!

    ReplyDelete