By David Crowther
Question:
What is the geometry_columns table and is it important?
Answer:
The geometry_columns table is a metadata catalog that QGIS uses to understand what tables are in your connected spatial database.
WHAT IS THE GEOMETRY_COLUMNS TABLE AND HOW IS IT USED:
When you connect to your source spatial database, for example SQL SERVER, the connection details allow you to choose if you wish to only looks for tables that are referenced in the geometry_columns metadata table.
If you untick this option…
… when you connect to your spatial database, QGIS will have to manually check the details of each table, one by one!.. checking the:
• Table name
• Which is the geometry column
• What the projection of that table is
• And what type of geometry is found in that table
This manual checking process can be very time consuming. So, when you connect, and open the database, dependent on the number of tables in your GeoStore, the table populating process can take a long time.
The SCHEMA (here DBO) and the Database Connection may have a spinning loading symbol as QGIS manually checks your database… and in some instances if you interact with QGIS at this point, you may cause it to crash. Many users therefore experience long wait times and become disillusioned with the connection speed.
However, if you edit the database connection details and choose to only look in the geometry_columns metadata table…
.. when we connect again, the list of tables in the source database is instantly created, as QGIS no longer needs to check through each table one by one!... instead, it simply lists the records as per the geometry_columns table.
However, in this case there are far less records in my geometry_columns catalog then there are actual tables in the database!
So, while QGIS will now connect to your spatial database more quickly, it is important that if you use the geometry_columns table, you should keep it up to date and accurately reflect the tables that are in your database.
Let’s explore what the settings are within the geometry_columns table:
• f_table_catalog = the source database name
• f_table_schema = the schema in your spatial database, for SQL this is usually the generic DBO schema
• f_table_name = the individual names of the tables in your database
• f_geometry_column = the column in the source table that stores the geometry of your records
• srid = the SRID (or projection) of the source table e.g., BNG is 27700
• geometry_type = the type of geometry stored in the source table, for example POINTS, POLYGON, MULITPOLYGON
MAINTAINING THE GEOMETRY_COLUMNS TABLE:
QGIS helps you maintain the integrity of the geometry_columns table, because each time you upload a GIS file into your spatial database, QGIS will create a new record in the catalog. For example, here we have a GIS file of UK Counties….
Dragging and dropping the GIS file into your spatial database, will upload the file to the database.
And if we now refresh the Database Connection in QGIS,….
…. we can see that this table is now listed as being within the database.
This is because QGIS has automatically written a new record into the geometry_columns table.
Here are some tips to ensure you keep the geometry_columns table up to date!
- If you rename a table in your source database, this will not auto update the associated geometry_columns table record
- If you delete a table in your source database, this will not auto update the associated geometry_columns table record
- If you change the SRID/Projection of a table in your source database, this will not auto update the associated geometry_columns table record
- If you change the field containing the geometry in your table in your source database, this will not auto update the associated geometry_columns table record
So, it is essential that you ensure you keep your geometry_columns table up to date, to accurately reflect the tables within your database including their name, structure, Srid and geometry types.
AUTO CREATING GEOMETRY_COLUMNS TABLE:
What if you haven’t maintained your geometry_columns table?....... well, here is some SQL that will help you rebuild the table automatically.
-- STEP 1 - BACKUP YOUR EXISTING GEOMETRY_COLUMNS TABLE
SELECT *
INTO YOUR_GEOSTORE.dbo.geometry_columns_backup
FROM YOUR_GEOSTORE.dbo.geometry_columns
NOTE – this creates a BACKUP of the existing geometry_columns table in case you wish to get it back again.
-- STEP 2 - DELETE RECORDS FROM YOUR EXISTING GEOMETRY_COLUMNS TABLE
Delete from [geometry_columns];
NOTE – this will delete all your current records in the geometry_columns table, which is why we made a backup above.
-- STEP 3 - POPULATE THE GEOMETRY_COLUMNS TABLE WITH A RECORD FOR EACH TABLE IN YOUR_GEOSTORE
Insert into [geometry_columns]
SELECT 'YOUR_GEOSTORE','dbo',table_name,'ogr_geometry','2','27700','POLYGON' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' order by Table_name;
-- NOTE – this looks through all the TABLE NAMES in your GeoStore, to then create a record for each one into the geometry_columns table.
It will update the following fields with the accurate values:
- table_catalog
- table_schema
- table_name
But uses a STATIC value to populate these columns:
- Geometry_Column – e.g., ogr_geometry
- Coord_dimension – e.g., 2
- Srid – e.g., 27700
- Geometry_type – e.g., POLYGON
NOTE - Having applied this script to my spatial database, the updated geometry_columns table now looks like the below, where the script inserted a record into the geometry_columns table for each individual table in my database.…
… and now back in QGIS, if we refresh the database connection,.. we now have a full list of tables, which now appears instantaneously as QGIS is using the updated meta catalog.
USING THE GEOMETRY_COLUMNS TABLE MOVING FORWARD:
It may be a good idea to check that the values in your updated geometry_columns table accurately reflect each table in your database. For example, check the field names of the GEOM fields in your individual tables, to ensure they match the value recorded in the geometry_columns.f_geometry_column.
In this example, the UK Countries layer that we imported, has the geometry field named as GEOM…this is because GEOM is the default field name used by the QGIS importer.
… but we have set this to be ogr_geometry in the catalog table (which is the default field name used by OGR_2OGR importers).
So, lets manually update the record with the correct geometry field name…. setting it to now be GEOM.
This ensures, that when we add the SQL table into QGIS, the settings in the geometry_columns table are correct, and the table opens as expected.
Finally, what if your source database table has multiple types of geometry?.... For example, you may have a Planning Application table, that has records with POINT, LINESTRING and POLYGON geometry. Because QGIS only opens layers with one type of Geometry, we can use the geometry_columns catalog table to choose which Geometry type to open.
Let’s update the geometry_columns table to set the GEOMETRY TYPE to be POLYGON for our Planning Apps table:
If we now connect to the database, the Planning Apps layer is recognised as being type POLYGON as shown with the SHAPE symbol in the browser window. And once added to the map, the Planning Apps which are type POLYGON are rendered.
Now, back in the source database, edit the geometry_columns table and change the GEOMETRY TYPE to now be POINT.
If we now connect again to the database, the Planning Apps layer is recognised as being type POINT as shown with the POINTS symbol in the browser window. And once added to the map, the Planning Apps which are type POINTS are now rendered.
I would then suggest that in the LAYERS Panel, you rename the layer names to reflect the GEOMETRY type being shown, for example Planning Apps POLYGON and Planning Apps POINTS. This means when you re-open your QGIS project, the Planning Apps table will be opened into QGIS, and automictically both the POLYGON and POINTS layers will be opened.
.. this then means that you can choose to show and hide the geometry types for your source tables as needed e.g., here we have hidden the POINT records.
… there is lots more that we could explore with regards the geometry_columns table and how to use it,.. but I hope this is a great starter and ensures that you can more seamlessly connect QGIS to your source spatial databases!