2

Why is FME imported data into SQL Server not being found by QGIS?

I've imported some MapInfo format data into SQL Server 2014 using FME.

In SQL Management Studio (SSMS), I can do a query and the spatial results tab shows the various polygons as expected

But if I switch to QGIS, create a connection to the database, and then open the list of tables in the dbo schema, the data set isn't showing

I have also loaded data using ogr2ogr and those data sets are showing, and can be displayed on a map in both QGIS and SSMS

So why aren't FME loaded data sets showing?

Additional Info

I deleted the old MSSQL connection, and when I created a new one, I noticed the the "Only look in the geometry_columns metadata table" and "Also list tables with no geometry" options. Unchecked first and checked the second, and now all the tables are showing, both the ones loaded using ogr2ogr and using FME.

Now when I try and add a FME layer, I get the error ...

"Layer is not valid: The layer dbname='GisData' host=(local) user='localdev' password='password' estimatedmetadata=true srid=27700 type=LineString table="dbo"."FmeFootpaths" (GEOM) sql= is not a valid layer and can not be added to the map"

The thing that caught my eye was the srid=27700 ... the ogr2ogr has srid 32768 (Should point out I don't understand srid's?)

Also I checked this geometry_columns, and it only has the tables loaded using ogr2ogr

  • Could you provide your FME workspace? Without additional info it's nearly impossible to find a solution for this. And consider modifying the headline of this issue. For me it suggests that QGIS generaly not finds data imported through FME, wich is definitly not true. – Jochen Schwarze Jun 2 '16 at 9:07
  • I've put a copy of the fmw file in my Dropbox at ... dropbox.com/s/irn69l7ntof9oyd/… – SteveC Jun 2 '16 at 9:59
  • 2
    Check your permissions. Are you using the same user in FME to connect as you are in QGIS? Often the reason a new layer doesn't show is because the connected user doesn't have the correct permission to view it. – Get Spatial Jun 2 '16 at 11:24
  • @GetSpatial I'm using the same user id and password for both the FME connection, and QGIS connection – SteveC Jun 2 '16 at 16:35
  • Alright, let's clarify something. When you say the data set isn't showing, does this mean that it doesn't show up in the list of tables to add in to QGIS? Or, when you add the table in QGIS, no features are showing? – Get Spatial Jun 2 '16 at 16:39
1

To me - since SQL Server doesn't seem to have a problem - the probable reason is a geometry that QGIS doesn't handle. The most likely of these is a GeometryCollection

So, is the incoming data in any sort of group, and is that group homogeneous (ie all the same geometry type)? If it does not form homogeneous groups of geometry then a GeometryCollection would be the result.

To resolve this you could try opening the SQL Server feature type properties in FME and trying out different values for the "Collection Type Handling" setting.

If that doesn't work, run the data through a Deaggregator transformer (deaggregate recursively) and see if you can see the results.

If that helps, but you need the data grouped, then add an Aggregator transformer to reaggregate the data. Set it to create "homogeneous" aggregates. You'll need to have an ID number for all features that belong together so you can do a 'group-by' in the Aggregator. A Counter transformer would create that ID for you

Hope this helps!

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.