Skip to main content

Hello,

I have encountered some issues with a workspace that I have not seen addressed previously in the forum. The issue concerns exporting to an SQL server database where the file format is interpreted as GeometryCollection when it is essentially only MultiPolygon at most.

This is what my workflow has looked like. 

I have a workspace that retrieves line segments from an SQL server, which are then combined and the attributes processed. These lines are generalized and buffered to form areas. I then use Dissolver to merge overlapping areas with a Group By function based on a common ID (the field INSATSNR in my screenshot). 

However, there are several areas with the same ID that are located in different geographical locations and do not overlap. Therefore, I use an Aggregator with a Group By function on the same ID. This ensures that areas with the same ID are collected in one field in the attribute table—exactly as I want it.

However, when I want to export the areas to another SQL database, the correct number of features is exported, but when I then open the layer in QGIS, it is listed as the file type GeometryCollection and no features are displayed in the map image. I therefore believe that something seems to have gone wrong in the export.

I have troubleshot by

  • Removing all donuts and building new surfaces using AreaBuilder.
  • Including GeometryValidator after Aggregator. The result is that all surfaces are OK and none needed repairing.
  • I tried to extract the geometry and replace it, but it didn't work.
  • In Dissolver, I used both Deaggregate and Reject as Aggregate Handling. Areas with multiple regions were rejected when that setting was active, but I couldn't aggregate them without using Aggregator.
  • I have filtered the geometry in various ways, but I can only conclude that FME perceives the layer's attributes as fme_area but with the geometry type fme_aggregate.

And this is where I start to wonder if the problem is the geometry type fme_aggregate? Our database can handle layers with multiple surfaces, but then as the file type multi polygon. I would like to be able to determine the geometry type, as you can do when writing to geopackage, for example. As far as I know, this is not possible when writing to a database.

My question is therefore whether others have encountered this problem? I also wonder if there is a way to work around this problem without using Aggregator but still get a final result where surfaces with the same ID number are merged in the attribute table?

Yes, I’ve ran into this exact same problem as well (we’re using SQL Server, FME and QGis as well). If memory serves me right, I used a  GeometryCoercer to set the correct geometry type. But I’m not 100% sure, and at the moment not able to check.


Yes, I’ve ran into this exact same problem as well (we’re using SQL Server, FME and QGis as well). If memory serves me right, I used a  GeometryCoercer to set the correct geometry type. But I’m not 100% sure, and at the moment not able to check.

I just tested it, and unfortunately the surfaces remain untouched...!

I tried changing to fme_polygon, fme_multi, and fme_composite_surface in GeometryCoercer, but without any different results.

In FME Attributes on the relevant layer, it says that my fme_geometry is fme_aggregate and fme_type is fme_area.

I find it strange that FME does not convert at all?


It is a rather annoying problem, I know. Every now and then GeometryCollections turn up in our database as well. I’ve just found the workspace that mostly fixes them (back behind my desk now). I use a Deaggregator, an ArcStroker (we sometimes get polygons where part of the polygon is actually an Arc - that causes a collection as well, but is very difficult to spot), a Dissolver, an Aggregator, a GeometryRefiner and lastly a GeometryFilter to find the remaining collections that were not fixed (and a bunch of GeometryValidators as well before and after). Just in case (sometimes there are self-intersections which aren’t really self-intersections but touching vertices) a Bufferer with a very small value is also part of the whole process. This seems to fix most of our issues, but it’s taken some figuring out indeed.


It is a rather annoying problem, I know. Every now and then GeometryCollections turn up in our database as well. I’ve just found the workspace that mostly fixes them (back behind my desk now). I use a Deaggregator, an ArcStroker (we sometimes get polygons where part of the polygon is actually an Arc - that causes a collection as well, but is very difficult to spot), a Dissolver, an Aggregator, a GeometryRefiner and lastly a GeometryFilter to find the remaining collections that were not fixed (and a bunch of GeometryValidators as well before and after). Just in case (sometimes there are self-intersections which aren’t really self-intersections but touching vertices) a Bufferer with a very small value is also part of the whole process. This seems to fix most of our issues, but it’s taken some figuring out indeed.

Yes - this solved it! Thank you!