I use FME to extract polygons from an SDE Feature Class and store them to a MS SQL Non-Spatial Database. The database is used by external software.
There are information about some files in one table, which are connected to the polygons I process. There is one table (“polygons”) storing only the File-ID and the Polygon-IDs, and another table (“Vertices”) storing the polygons’ vertices (east and north coordinates). For each file there can be several polygons stored, and for each polygon there are of course several vertices stored.
When I process the polygons, this is how the attributes look like:
Please notice that there are two polygons belonging to file-ID 121051.
What I have to do now is to create entrys in the table “polygons” with the given File-ID; the Polygon-ID is assigned automatically (incrementing) by the DBMS. I use the FeatureWriter to do this. The result looks like this:
In the next step I need to store the vertices and the polygon-ID to the table “vertices”. So first I use the CoordinateExtractor and the ListExploder to extract the vertices from the polygons, and this is my result:
The yellow rows belong to one polygon, the red ones to another one, but both the yellow an the red rows describe polygons belonging to a certain file (with file-ID 121051).
When I store the vertices to the table “vertices” I need to know their polygon-ID, as this is the connection to the file (via the table “polygons”). To be more precise, I do not need to know “their” polygon-ID, but I need to know one of the Polygon-IDs stored to the table “Polygons” with the corresponding File-ID and I must ensure that I use the same Polygon-ID for all vertices belonging to a certain Polygon. For example, I could say the vertices in the red lines belong to Polygon-ID 116459 and the Polygon in the yellow lines belong to Polygon-ID 116460, or vice versa. It doesn’t matter which Polygon-ID I use for which polygon as long as the Polygon-ID belongs to the correct file…
And this is where I’m stuck! As I know the File-ID, I first thought about using the SQLExecutor with the SQL-Statement “SELECT polygon_id FROM polygons WHERE file_id =@Value(myFileId)”. This however only works if there’s one polygon stored to that file. In case there is more than one polygon stored, the SQL-Executor returns all the polygon-IDs for each vertex, so my vertices are doubled, tripled or whatever the number of polygons is, so the result looks like this (extract, sorted):
Any hint is appreciated :-) Thanks in advance!
