Skip to main content
  • EN

Hello :)

Here is what I am doing: I am working with FME-Flow and FME Workbench 2023.1.1. I have geodata in the form of .csv-files and am using a simple CSV-Reader ➡️ MSSQL-Writer to create my datatable in my MSSQL-database (see attached image for FME-script). The datasets are saved to the ‘dbo’-schema and work without problem in GIS (QGIS in this case). This is done for a number of CSV-files. As an example I have shown the summary annotation for the dataset ‘ov_boende_aldre’.

What’s the issue? When FME writes to the MSSQL database, the resulting datatable(s) is/are not recognized by the systems view ‘geometry_columns’ (se image below). Why not? When creating a dataset directly in MSSQL or in GIS (QGIS, for example), the dataset appears in said systems view. What can be the issue here? Any secret sauce to make FME create the dataset correctly that I am just missing?

All help and hints are appreciated. Also, if you are aware that there is a similar post with a solution then do please direct me to the thread :)

Regards

Nicholas

 

As far as I am aware, the dbo.geometry_columns table is a QGis/GDAL/PostGis specific table, it is not an MS SQL system table (otherwise you probably wouldn’t see it in the dbo-schema). So FME wouln’t know to add an entry in there. You’ll have to do that yourself, for as far as I know.

Similar comments, the geometry_columns table is not required by SQL Server for storing spatial data, and is instead used by some external applications to speed up the search for spatial tables Eg. Rather than have to test the entire database schema for the presence of geometry/geography fields, then perform a simpler query on the geometry_columns index table.  I believe QGIS does not explictly require an entry in this table, it can be pointed to, and search for, spatial tables not registered in geometry_columns.

To populate it is through a separate UPSERT write with the table metadata row(s).  An example is given in the QGIS documentation https://docs.qgis.org/3.4/en/docs/training_manual/spatial_databases/simple_feature_model.html

insert into geometry_columns values
  ('','public','people','the_geom',2,4326,'POINT');

 

Reply


Community Stats

30,351
Posts
115,341
Replies
38,080
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.