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.

 

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 Dialog 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 Edit - Change Projection dialog.  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 have also created 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 have taken advantage of Manifold's ability to rapidly cast geometry data into WKB form.

 

It took just a few seconds to create that GeomWKB field.   We first added a field to the table by clicking the * asterisk column head to launch the New Field dialog:

 

eg_qgis_odbc01_02a.png

 

In that dialog we gave a name to the new field of GeomWKB and chose geomwkb as the data type.   Press Add Field and the field is created.

 

eg_qgis_odbc01_02b.png

 

Next, we used Edit - Transform to launch the Transform dialog, and then we used the Copy template to copy the values in the Geom field into the new GeomWKB field.    Press Update Field and the job is done.   Using Manifold features like the Transform dialog 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 radian_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.

 

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 radian_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.

 

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

 

QGIS next asks what coordinate system should be used for this drawing.   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

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 symbol in its own right.  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.

See Also

Transform Dialog

 

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: 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 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.