Example: Connect to Manifold from QGIS

This topic provides a step by step procedure to connect from QGIS 2.8.9 to a drawing in a Manifold .map file using ODBC.

 

ico_nb_arrow_blue.png  To use GDAL or OGR we must install GDAL in Windows, and then we can use it from Manifold.  If we do not install GDAL, we will not be able to use the capabilities described in this topic. See the the GDAL / OGR topic for installation instructions. The Manifold GDAL dataport allows working with GDAL 2.0.x, 2.1.x or 2.2.x and automatically selects the latest version available with automatic adjustments for the call interface.

 

To connect to an ODBC data source QGIS utilizes OGR, a vector input/output library.   OGR in turn can connect to an ODBC data source that is created using some other program or tool (OGR cannot create an ODBC DSN).   Configuring an ODBC connection for QGIS therefore requires configuring an OGR connection to an ODBC data source.

 

To utilize data from an ODBC connection in QGIS we must keep four factors in mind:

 

 

 

 

 

Configure the Project for OGR

Some spatial applications that use ODBC can connect through ODBC to whatever is in the ODBC data source.   OGR cannot do that and requires that a spatial data source be configured in advance with OpenGIS specific content to enable use by OGR.   To do that we must make some additions to our Manifold project.  These may seem complicated at first but in reality are very easy to do.  

 

The authoritative guide to OGR requirements is currently at http://www.gdal.org/drv_odbc.html - users of QGIS and OGR should read that document carefully to gain a clear understanding of OGR requirements.  By carefully reading that page and then using our Internet search skills to gather any supplemental information we can see that to use a spatial project with OGR we must include a table in the project called GEOMETRY_COLUMNS and that table must contain at least three fields called F_TABLE_NAME, F_GEOMETRY_COLUMN and GEOMETRY_TYPE.  

 

The GEOMETRY_COLUMNS table tells OGR what to consider as a layer in the ODBC data source.  Each row in the GEOMETRY_COLUMNS table provides information for a layer.  For each layer the table contains fields which give the name of a table within the project, what field in that project contains geometry, and what type of geometry is contained within that geometry field.  OGR requires that the geometry be in either WKB or WKT form, and it requires that the GEOMETRY_TYPE column contains one of the OGR specifiers for geometry type.   These specifiers are not covered in the above link but they can be discovered with some Internet detective work or by consultation of the source code.

 

Let us see how all this comes together in real life.   Consider a Manifold project that has a drawing showing regions in France, similar to what is used in many of the other examples in this documentation, such as the Example: Format a Drawing using the Style Panel topic.

 

 

eg_qgis_odbc01_01.png

 

The drawing has a squashed look typical of Latitude / Longitude projection in middle European latitudes because the drawing has been re-projected into Latitude / Longitude projection using the Change Coordinate System command.  The drawing has been re-projected into Latitude / Longitude to make it easier to specify the projection in QGIS.

 

This drawing has only area objects in it, so it does not need to be split up into three separate (all points, all lines and all areas) drawings for use by QGIS. All of the areas are also simple polygons and are not branched objects, that is "multipolygons" in OGR nomenclature.  

 

Opening the drawing's table we can see the fields it contains.

 

eg_qgis_odbc01_02.png

 

As usual, this drawing takes geometry from the Geom field in the form of Manifold's native geom type.   In addition to the Geom field, to  prepare for consumption by OGR we must create a second geometry field called GeomWKB that stores the equivalent geometry for each object using WKB format.    OGR requires geometry to be in WKB format so we will take advantage of Manifold's ability to rapidly cast geometry data into WKB form.

 

It takes just a few seconds to create a  GeomWKB field.   With the focus on the open table, we choose Edit - Schema to launch the Schema dialog:

 

eg_qgis_odbc01_02a.png

 

In the Schema dialog we click the <new field> option, gave a name to the new field of GeomWKB and chose geomwkb as the data type.   Press Add and then OK and the field is created.

 

eg_qgis_odbc01_02b.png

 

The new GeomWKB field initially is populated with NULLs.   We will now populate it with the drawing's geometry, automatically converting it from Manifold geom data type into geomwkb data type.

 

eg_qgis_odbc01_02c.png

 

Open the Contents pane and choose the Transform panel.  We use the Copy template to copy the values in the Geom field into the new GeomWKB field.    

 

eg_qgis_odbc01_02d.png

 

The Transform panel shows a preview in the table using blue preview color.  Press the Update Field button in the Transform panel and the job is done.  During the copy, Manifold knows to automatically convert from geom data type to geomwkb data type.   

 

eg_qgis_odbc01_02e.png

 

Using Manifold features like the Transform panel we can create a new GeomWKB field and copy the Geom contents into that field for use by OGR more quickly than it takes to read the preceding few paragraphs.

 

We also must create a GEOMETRY_COLUMNS table as required by OGR.     As detailed in the Example: Create a Table and Add a Record topic, we have created a new table called GEOMETRY_COLUMNS and then we added three new fields to it called F_TABLE_NAME, F_GEOMETRY_COLUMN and GEOMETRY_TYPE, all three of which are created as data type varchar.    We can then edit the table to place the text values required by OGR into the fields.

 

eg_qgis_odbc01_03.png

 

As described by the OGR documentation at http://www.gdal.org/drv_odbc.html the F_TABLE_NAME field must contain the name of the drawing's table, in this case regions_table.   The F_GEOMETRY_COLUMN field must contain the name of the field within that table that contains geometry, in this case GeomWKB.   

 

What to put in the GEOMETRY_TYPE requires knowledge of OpenGIS / OGR, obtainable by Internet search or by having a programmer review the source code, to learn that the word GEOMETRY should work by default.  We could have put the word POLYGON into the field since the drawing contains only non-branched areas (known as polygons in OGR) and that would work as well.  If the drawing had all points we could have put the word POINT into that field.   We use GEOMETRY as a catch-all term.

 

Having made the above additions to the project, the project is now ready for use by OGR and then in turn by QGIS.  None of this is a requirement for ODBC but it is required by OGR and thus QGIS.

Creating an ODBC Data Source

For Manifold people the easiest way to create an ODBC Data Source for connecting to the above project is to use Manifold System Release 8 or Radian-based Manifold System, both of which have full ODBC capabilities built into the product.    Alternatively, we can use the standard Microsoft Windows ODBC Data Source Administrator dialog that is in the Windows Control Panel - Administrative Tools collection as described in the Example: Create an ODBC Data Source with Windows topic.

 

 

eg_qgis_odbc01_04.png

 

For this example, we have used the Windows ODBC Data Source Administrator dialog to create an ODBC User DSN called manifold_regionswkb that is an ODBC data source connection to the project shown above in this topic.

Connecting QGIS through OGR

After preparing the project and creating an ODBC data source we can now connect to the data source using QGIS, which calls OGR to connect to the ODBC data source and to pull spatial data from that source.    QGIS works with ODBC data by adding a vector layer.

 

In this example, we use QGIS Desktop 2.8.9.   We begin by launching QGIS Desktop and then in the main QGIS menu we choose Layer - Add Layer - Add Vector Layer.

 

eg_qgis_odbc01_05.png

 

We check the Database radio button, choose ODBC as the Type of Database and then click New.

 

 

eg_qgis_odbc01_06.png

 

That launches a dialog to create a new OGR database connection, which in this means to create a new OGR connection that connects to the ODBC data source previously created using Windows.    

 

There is no browsing of available ODBC data sources in this dialog so we must know the name of the ODBC data source we created using Windows.   If we forget, we can pop open the Windows dialog to allow browsing of ODBC data sources.

 

Some OGR values required for the Connection Information fields are more oriented towards connecting to a database server than to an ODBC data source.   That is OK, as with this step-by-step example to lead us we know which of those values are required and which can be ignored.

 

Choose ODBC as the type, and enter a sensible Name that will help us remember what this connection is.  The Name is the name of the OGR database connection we are creating, a different thing than the ODBC data source name (DSN) previously created in Windows.    

 

For Host we use localhost and for the name of the Database we use the name of the ODBC connection as shown in the Windows ODBC Data Source Administrator dialog, in this case manifold_regionswkb.   

 

We leave the Port, Username and Password fields blank.   We check the Save Password box even though that seems to have no effect subsequently.    Press the Test Connect box to see if OGR can connect to the ODBC data source we created in Windows.

 

eg_qgis_odbc01_07.png

 

Ahh... great news!  We press OK, and then OK again.

 

eg_qgis_odbc01_08.png

 

Back in the QGIS Add vector layer dialog, the new OGR database connection we have named appears.   We press the Open button.

 

eg_qgis_odbc01_09.png

 

QGIS asks for a password, which we ignore since there isn't any.  We press OK.

 

eg_qgis_odbc01_10.png

 

Depending on your configurations, QGIS may next ask what coordinate system should be used for this drawing, using a dialog such as the above.   WGS 84 is not a coordinate system (it is a base, that is an ellipsoid or datum), but if we click the WGS 84 entry then QGIS will understand that what we mean is that Latitude / Longitude using a base of WGS 84 should be used.   Earlier in Manifold we deliberately re-projected the drawing into Latitude / Longitude as part of getting the project ready so that this step would be very simple, taking advantage of how QGIS provides WGS and Latitude / Longitude as a default coordinate system to choose.

 

Press OK and we see the drawing linked into QGIS.   The data is still resident within the Manifold project but the ODBC driver is providing it through OGR to QGIS.  Cool!

 

eg_qgis_odbc01_11.png

 

Notes

Double quotes are OK - The Manifold ODBC driver allows enclosing identifiers in double quotes, to work around a limitation in newer versions of QGIS.

 

More sophisticated approaches - This example uses the simple case of a drawing that is all areas, where the drawing's data comes from a table.  A more sophisticated approach in Manifold is to create a drawing from a query.  We might want to do that when our drawings contain a mix of areas, lines and points that QGIS cannot handle.  In that case we can write queries that for each such drawing create three tables, each containing only areas, lines or points in a WKB geom field, and then write a GEOMETRY_COLUMNS table that points at those three tables as three layers.   This approach uses Manifold's ability to on-the-fly manipulate data via queries to create what in effect are virtual tables that can be consumed as spatial data by OGR and thus as layers that QGIS can consume.

 

What does GDAL stand for?  GDAL - Geospatial Data Abstraction Library.   

 

What does OGR stand for?  OGR.  OGR is not a direct acronym but a strange, three letter word of its own.  According to a FAQ apparently written by the author of OGR, "OGR used to stand for OpenGIS Simple Features Reference Implementation. However, since OGR is not fully compliant with the OpenGIS Simple Feature specification and is not approved as a reference implementation of the spec the name was changed to OGR Simple Features Library. The only meaning of OGR in this name is historical."

 

What is the relationship between GDAL and OGR?   In a nutshell, OGR is a vector library while GDAL is a raster library.   From the FAQ: OGR is "a vector IO library inspired by ​OpenGIS Simple Features" the source code to which is "somewhat entangled" with GDAL sources.  

 

When picking a coordinate system, doesn't EPSG:4326 require YX ordering of coordinate axes?   Sure does.  See the That YX Thing topic.

 

Historical regions - The illustrations in this topic use data from the US government, which show the regions of France as they were before 1 January 2016, when regions in France were reduced from 22 to 13.  For illustrations using the current regions, see the Example: Style Overrides topic.

See Also

Contents - Transform

 

GDAL / OGR

 

Example: Create a Table and Add a Record - Create a table with required fields and then add a record with value for those fields.  Creates the OGR-required table to prepare a Manifold project for use by OGR as detailed in this topic.

 

Example: Import ERDAS GIS with GDAL and Assign Coordinate System  - A multistep example that covers import of an unwieldy format using GDAL, followed by use of Assign Initial Coordinate System and Repair Initial Coordinate System to deal quickly with real-life complications.

 

Example: Create an ODBC Data Source with Windows - How to create an ODBC data source (a DSN) using dialogs built into Windows 10.

 

Example: Connect to Manifold from Release 8  - Step by step procedure to connect from Manifold System Release 8 to a Manifold .map file using Manifold's ODBC driver.

 

Example: Connect to an ESRI GDB File Geodatabase - Connect Manifold to an ESRI GDB file geodatabase, display the contents, make a selection in the GDB and overlay in a map.

 

Example: Connect to an ESRI GDB usng GDAL/OGR  -  Instead of using Manifold's built-in ability to connect to modern ESRI GDB file geodatabases, use the Manifold GDAL/OGR dataport to take advantage of the GDAL library's ability to connect to deprecated GDB formats.

 

Example: Connect Through Manifold ODBC to a Third Party  - With Release 8, use an ODBC connection to a Manifold .map to connect through the .map project to a third party, external data source, an ESRI GDB file geodatabase.  We use Manifold facilities as an intermediary to give Release 8 capabilities it does not have on its own, to link into data stored within an ESRI file geodatabase.

 

Example: Connect LibreOffice Through Manifold to an ESRI GDB - A companion example topic to the Example: Connect Through Manifold ODBC to a Third Party  topic.  Shows how to connect LibreOffice Base, the database part of LIbreOffice, through Manifold to link an ESRI GDB file geodatabase table into LibreOffice.