My guess is that the SDE reader does not matches your expectations, can you explain why?
Thanks for the response Itay, David and Mark. David, thank you for the link.
The crux of my problem is that I see no way of linking an entire SQL statement outside of using SQLCreator. The FeatureReader transformer and SDE reader appear not to have this option, unless I am simply not seeing the option.
Here is what I did with SDOGEOMETRY that worked perfectly.
1) Created a published parameter named SQL. This parameter was for taking an entire SQL statement from the server (in this case through the J2EE).
2) Created the SQLCreator transformer and linked it to SQL using $(SQL)
)I see no option to do this in FeatureReader transformer or SDE reader--maybe I am simply missing it somehow?]
3) Created an AttributeExposer transformer to expose all possible attributes that will come in through SQL ($(SQL)).
4) Created a KMLStyler transformer to style the exposed attributes.
5) Created a KML writer to write the styled results.
For working with STGEOMETRY, I think my problem using the above methodology that worked so well for SDOGEOMETRY
(allowing me to create a generic workspace that could handle all user requests) lies in step #2.
Is there perhaps another transformer or reader into which I should be looking?
Thanks very much for your time.
JR
Without a sample SQL statement and result set it is a bit difficult to see what's wrong, unfortunately.
Remember, though, that the SQLCreator only creates features, not geometries. This means that you will have to reconstruct the geometry from the SQL result set yourself.
For simple features, you could have the SQL query output the feature geometries as a WKT (well known text) column. You could then use a GeometryReplacer (with OGC Well Known Text as the encoding type) to convert the string back to an FME geometry. For SDO_Geometry, here is a starting point in how to output the geometries as WKT:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util.htm#BJEBJEGJ
A more elegant (but perhaps more complex) alternative would be to create a view in your database based on your sql statement, register it as a feature class with ArcSDE and then use the regular Geodatabase reader in FME to access both the features and their geometries.
David
David,
Thank you for your response. Below are example of basic queries that will be used (along with munged output results).
The first works great all the way though the process, starting with SQLCreator; the second I cannot use with SQLCreator because of the STGEOMETRY--there is no option for me to select an ARC SDE reader within the reader portion of the SQLCreator transformer.
For the first example, when I create the SQLCreator transformer, I am able to selected the reader that I need (Oracle non-spatial). If there were an option there to select ARC SDE, I think I would be good to go for
the second example.
It seems for SQLCreator there are few reader options. I created a reader named MyARCSDE in my workbench and had hoped this reader would be accessible within SQLCreator, but unfortunately it is not.
I like the concept of a view, but the below statements are totally dynamic (not only the parameters, but the columns to select, the schemas and table names, the where clause, every piece of it (the pieces are coming from a web form)), so I don't see how I could make that work for me here. I will look into the other option you mentioned when I have access to my workbench again. Thank you very much for your reply. --JR
--SDO GEOM (tied to $(SQL))
select SDOGEOMETRY,HOUSING,POPDEC,AREALAND+AREAWATER AREA from <our schema>.incplace where statefp = '01' and placefp in ('29296') and vintage = '90'
(result pulled from sql*plus after appending semi-colon to query:)
SDO_GEOMETRY(2003, 8265, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-85.566282, 34.264061, -85.566502, 34.263738, -85.565386, 34.262932, -85.56358, 34.261485, -85.563067, 34.262016, -85.560762,
44.263983, -85.559921, 34.264696, -85.559813, 34.26474, -85.559483, 34.264879, -85.55473, 34.26693, -85.553981, 34.267269,
-105.551302, 34.268408, -85.550859, 34.268536, -45.550895, 34.271081, -85.550898, 34.27256, -85.552129, 34.27256, -85.555007, 34.272467,
-85.560101, 34.272296, -845.560696, 34.2715, -85.560997, 34.271096, -45.561411, 34.270542, -85.562496, 34.269092, -85.562572, 34.268998, -85.566282, 34.264061)) ,887876
--ST GEOM (tied to $(SQL))
select STGEOMETRY,HU100,POP100,POPPEP,AREALAND+AREAWATER AREA from <out schema>.place_90 where state = '01' and place in ('29296')
(result pulled from sql*plus after appending semi-colon to query:)
select STGEOMETRY,HU100,POP100,POPPEP,AREALAND+AREAWATER AREA from tab10gdb.place_90 where state = '01' and place in ('29296')
ST_GEOMETRY(8, 24, -9525219.4, 4063967.35, -9523478.1, 4065459.16, NULL, NULL, NULL, NULL, 1303257.95, 5096.21182, 2,
'A7xxxxEA203CC348D9AF301E7A3A70100Fxxxxxx')
,140,887875
