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!
