Filemaker interface with QGIS

Question

I have created a map of features on an archaeological site. The artifact inventory is in FileMaker Pro 12. Rather than creating a CSV file and using that as the attribute table, I would rather, if possible, use a database as an interface to query the artifact inventory to map the occurrence of items in features to start with and then to work with QGIS analytical tools to see if I can isolate family level information.

QGIS archives said that in 2011 it was not possible, then Albert stated he'd found a way to connect FileMaker Pro to QGIS. I would rather use FileMaker as the interface as it cuts out the CSV state that requires a lot of fiddling.

So my question is can QGIS connect with a FileMaker Pro database through ODBC? If so, what are the steps to link them together so that they may be used? I have enabled ODBC in FileMaker and tried to set up the connection in QGIS but what goes into the blanks on the QGIS pulldown page isn't clear.

Answer 1

So my question is can QGIS connect with a FileMaker Pro database through ODBC?

Yes. It is possible to set up a connection between the two that allows QGIS to read spatial data from a Filemaker database, without the intermediate CSV step. When the filemaker data changes, so does the data in QGIS. Some warnings however:

• the link can be quite slow, even if the database and QGIS are running on the same local machine. If the two are separated by a slow network / internet connection and/or the FM database is big, with many links to other databases on the server or many joins within the same database, data refresh times are painful. This appears to be platform independant, and I get similar results on Windows and MAC OS (QGIS 3.6.1). There are some useful hints on how to structure a FM database to speed up ODBC access here.

• updated data in the database will mapped in QGIS only after your map is re-drawn. This can be triggered by panning or zooming.

• updated data in the database will only appear in the QGIS attributes table after you close and re-open the attributes table. Clicking the reload button on the toolbar of the attributes table will not trigger an update (bug?).

• the Filemaker database will need to be modified in order to set this up. You will need to have the appropriate access and client software. Once set up, QGIS users will not need to have the Filemaker application.

If so, what are the steps

I am neither a GIS nor a Filemaker expert, and discovered this route after a lot of persistence and trial & error. The same steps are valid for Windows and MAC OS.

1. The database must have the spatial data in a text field in WKT format. If your database has (for example) LAT and LONG fields for a data point in each record, you will need to create a new calculated field whose result is a text string in the expected WKT format. link .

2. The database will need a simple table that will tell the ODBC connection to how to connect. The table MUST be called GEOMETRY_COLUMNS, and must have one record, with at least the following fields:

F_TABLE_NAME This must contain the name of the table in the same FM database that has the WKT field created in step 1 above.

F_GEOMETRY_COLUMN This must contain the the name of the field with the WKT

GEOMETRY_TYPE This an integer which indicates the type of geometry that will be accessed via the ODBC link:     1 = Point     2 = Linestring     3 = Polygon 4 = MultiPoint 5 = MulitLinestring

Additional fields can also be included in the GEOMETRY_COLUMNS table, including: F_TABLE_CATALOGUE (name of the database); COORD_DIMENSION (2 or 3); XMAX; XMIN; YMAX; YMIN. These may may things run faster but should not be obligatory. More details on the structure of the GEOMETRY_COLUMNS table here

3. Obviously you need to ensure that the user name that will be used to connect to the FM database has a privilege set that allows access to the database via ODBC (menu item: file/manage/security....) and that ODBC is enabled (menu item: file/sharing/enable odbc).

4. You need to download and install the FileMaker ODBC driver on the client MAC/PC if you haven't already done so.

5. With the database open locally or on the server, set up the DSN for the ODBC data source using the ODBC Data Source Administrator on the client PC/MAC. At the end of the set up you can test the connection. If the test is successful you are almost there.

6. Open the QGIS project on the client PC/MAC and using the Data Source Manager (ctrl L) select Source type: Database, Database type: ODBC, and click Connections:'New'.

In the window that opens ("Create a new OGR Database connection"), select Type: ODBC, Name: Insert a name that will identify this ODBC connection in the future. Note that this field must not be left blank. Host: can be left blank, Database: Insert the name of the DSN you created in step 5 above, Port: 2399

In the same window, under Authentication click "Basic" and insert the username and password to access the FM database. Click to Test the connection. If all is good, click OK to return to the Data Source Manager and then click Add.

After some time (depending on database size, number and complexity of related tables, and connection speed) a window will open with a list of available tables from the FM database. Choose the table that contains the WKT field that you created in step 1, and click OK.

This should result in the creation of a layer in QGIS with the name

ODBC:UserName@DSNName TableName

The layer can be filtered, symbolized, joined, exported just like any other.

Good luck. Apologies if the above is long-winded.