1

In QGIS, when I go to add a MSSQL table, it recognizes the geometry type and SRID of some tables and not others. When it doesn't recognize these I can set them manually and load the data without a problem.

What do I have to set within SQL Server to have QGIS recognize the geometry type and I have not selected the option "Only look in the geometry_columns_metadata table" for the MSSQL connection?

enter image description here

1

QGIS reads MSSQL with a native provider which reads geometrytypes and other metadata from a table named "geometry_columns" if such exists in the database. The source code of the provider is at https://github.com/qgis/QGIS/tree/57dc3c7eff2337411c163222a4e5b7562fffd457/src/providers/mssql

The part of code that takes care of metadata is /qgsmssqlsourceselect.cpp

For making QGIS to recognize the geometry type automatically you must register the table by adding the necessary information into the "geometry_columns table". Some more information can be found from http://planet.qgis.org/planet/tag/ms%20sql%20spatial/

  • Within our database, we have a table named "geometry_columns", however, QGIS does not recognize the geometry type of any of the tables listed in this table. Also, this tables does not list the tables which QGIS does recognise the geometry type from. For our system, the "geometry_columns" table is used for tables replicated in SQLite – Philip Whitten Aug 16 '17 at 0:52
  • In my QGIS installation for the MSSQL connection, if have not selected the option "Only look in the geometry_columns metadata table" – Philip Whitten Aug 16 '17 at 1:05
  • Does the QGIS user have select rights for geometry_columns? – user30184 Aug 16 '17 at 6:14
  • Tables which are registered in geometry_columns should be found. The SQL query that QGIS is using is "select f_geometry_column, coord_dimension, srid, geometry_type from geometry_columns where f_table_schema = '%1' and f_table_name = '%2'" . I am not sure about what QGIS is doing if table is missing from geometry_columns and how it can guess the geometry type. Perhaps it is scanning the whole table. – user30184 Aug 16 '17 at 13:33

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.