3

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.

5
  • 1
    To do what? You will need to give more information then that. – Nathan W Jan 14 '15 at 1:36
  • It's pretty clear - "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." – Kevin Jan 14 '15 at 16:46
  • Never used FileMaker, but i think that enabling ODBC there is only enabling the program to fetch data from other databases. To connect QGIS with FileMaker database, you have to set up an ODBC connection (in Windows I assume?) to which you can then connect from QGIS ... did such stuff once and hated it, so better read more about setting up ODBC connections. But even if you have that connection, I do not know if your database holds geometry data that QGIS can digest? – Bernd V. Jan 14 '15 at 23:48
  • Have you find any answer to this ? We have the same problem here. We do succeed with localhost (with these instructions : academia.edu/5400956/Connexion_ODBC_Filemaker_-_SIG) but nothing with an external IP hosted database. Thanks, Tomy – user50219 Apr 8 '15 at 14:54
  • Thank you for this, will check it out ! – B Alexander Aug 3 '20 at 2:06
3

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

  1. 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).

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

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

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

1
  • QGIS 3.14 seems to have introduced a couple of hurdles: An ODBC layer created in this way has no CRS and this needs to be set manually. There also seems to be issues with the layer's definitions being lost, and when a project is re-opened the layer appears to be only an ODBC linked table, with no recognized geometry. – Albert Aug 20 '20 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.