• EN
Question

Joining Oracle Tables and Outputting a KML that Contains Attributes from Both Tables

J
Good morning.

 

  I am trying to join two Oracle tables, TableA and TableB, to output a KML file that contains attributes from both tables.  I am able to do this presently by using a temporary table created within Oracle and then simply reading this table within FME and then using the KML writer to output the KML.  On production, I will not be allowed to use temporary tables, so I will need to handle the join within FME. 

 

 

In Oracle, my join condition to create the temporary table is very simple: a.state = b.state.  How would I accomplish this join between TableA and TableB and keep the attributes a.col1, a.col2, a.col3, b.col1 in my output KML?  I have tried various methods, but all without success.  I am always only able, when I view the KML, to see attributes a.col1, a.col2, a.col3 or b.col1--never all in the same KML. 

 

 

Thanks very much.

 

 

JR 

 

 

 

 

14 replies

fmelizard
Rank
Userlevel 4
Badge +13
Hi,

 

 

Did you try the joiner? or the feature merger (with a list)? that should get you the attributes from both sources.

 

Its just a question of mapping them toward the kml attributes.

 

Hope this helps.
david_r
Rank
Userlevel 5
Hi,

 

 

if you already have a select statement to populate your temporary table, you can probably just copy the select statement into an SQLCreator. That way you should be able to get exactly the same results with a minimum of change in your workspace. It is also very efficient since it joins the data at the database level rather than pulling everything into memory in FME.

 

 

Just remember to expose the attributes introduced by the SQLCreator so that they appear in the Workbench GUI.

 

 

If, for some reason, that does not work, you have the FeatureMerger (as Itay says) but also the InlineQuerier and the Joiner that might help you along.

 

 

David
J
A quick follow-up: I have to use ST_GEOMETRY as my feature type and ARCSde readers as my readers, so I am unable to simply use the SQLExecutor transformer to help with the above. 

 

 

Thanks.

 

 

Joe
david_r
Rank
Userlevel 5
Hi Joe,

 

 

thanks for the clarification. In that case the easiest solution is probably to use a FeatureMerger, as Itay suggested. Mind the "merge type", though, if you have two sets of geometries to merge.

 

 

The InlineQuerier might also be an option, but it is bit more complex to set up. On the other hand it is sometimes quite a bit faster. There is a nice walk-through here if you're ready to go down that path.

 

 

The Joiner might not work for you in this case as it can't be used to retrieve geometries (attribute only).

 

 

David
J
Thanks, guys (and wow, you two are fast--thank you!). 

 

 

Yes, I have tried the Joiner and FeatureMerger transformers without sucess.  Perhaps I need to use two of these transformers, one per each layer.  I have tried that but will try again in various combinations.

 

 

Quick slightly off-topic follow-up question:

 

 

I am having a great deal of difficulty working with the ESRI data type ST_GEOMETRY (in part because I am new to it and in part, I believe, because of its highly proprietary nature).  If the SQLCreator could accept the ARCSDE reader, I think all of my issues would be immediately resolved.  At this point, I would be willing to pay out of pocket for this to be added to the SQLCreator transformer.  Would it be an enormous undertaking to make the ARCSDE reader available to the SQLCreator transformer?  

 

 

Thanks much, guys.

 

 

JR 
fmelizard
Rank
Userlevel 4
Badge +13
Hi Joe,

 

 

For not too complex queries, the where clause in the SDE reader can do a lot.

 

Have you tried joining on the SDE reader, via the where clause?
david_r
Rank
Userlevel 5
Hi Joe,

 

 

it is in fact possible to get the ST_Geometry objects via the SQLCreator / SQLExecutor. What you need to do is to ask SDE to convert it to text (WKT) for you before it sends it to FME. The function to do this is called ST_AsText(). Here is an example: 
 SELECT id, name, sde.st_astext(shape) as my_geometry FROM my_feature_class_table;
 

 

When you have the geometry as text, it is simply a matter of using a GeometryReplacer with "OGC Well Known Text" as the encoding type to get your geometry into FME again.

 

 

Be aware though, that this technique might not work very well for very complex geometries (huge polygons/donuts, etc), but for points and simple lines it can work very well.

 

 

David

 

 

J
Thanks, Itay.  The problem is that my WHERE clause will be fed in by the user (actually, the whole statement will be), and it seems in the SDE reader there is no way to link even just the WHERE clause to a published parameter (I have tried this without success using $(WhereClause); the reader will not create when I attempt this; the error is

Undefined macro `WhereClause' dereferenced in file

).

 

 

I have also tried creating the SDE reader without the WHERE clause initialized and then linking this reader to a Joiner (I tried FeatureMerger, too), but no luck.

 

 

Is it possible to update the SQLCreator to include the SDE reader?  My suspicion is that this would take care of my issues entirely.

 

 

Thanks very much.

 

 

Joe
J
Thanks, David.  I think you may have mentioned this option before and I had never fully tried it.  I will give that a shot now.  Thanks very much.

 

 

Joe
fmelizard
Rank
Userlevel 4
Badge +13
Hi Joe,

 

 

That should not  be a problem (see Mark's answer here ), creating parameters from the features to read and the where clause, will provide you with the option of inputting the query by the user.

 

As to upgrading the SQLCreator, that is something safe can comment about, but what David suggests is a nice work around.
J
David,

 

 

Thanks very much for your suggestion.  I think my main concern with the approach you wrote is that I may have very large amounts of data to send to the server, but for now, I won't worry too much about that.  If I can just get a small set to work, that would be great.  I want to make sure I get the workflow order correct.

 

 

Web application gets user selections

 

 ->Web application executes statement that uses st_astext

 

  ->Web application sends these data to FME

 

   ->FME receives data and uses GeometryReplacer to manipulate it for the KML writer.

 

 

Just a few questions about this, please.  What would my Source Geometry Attribute be?  What object would I use to feed my data into the GeometryReplacer (I tried without success using SQLCreator directly as the input source to GeometryReplacer)?

 

 

Thanks very much.

 

 

Joe

 

david_r
Rank
Userlevel 5
Hello,

 

 

I've connected my FMEserver with one active directory.

 

I can connect to my FMEserver and I can navigate between the differents menus except the security menu where i have an error :

 

 

 javax.servlet.ServletException: javax.servlet.ServletException: COM.safe.fmeserver.api.FMEServerException: Could not read FME Server response message. Connection may have been lost.Une connexion existante a dû être fermée par l’hôte distant 	COM.safe.web.servlet.PageServer2.doGet(PageServer2.java:107) 	javax.servlet.http.HttpServlet.service(HttpServlet.java:621) 	javax.servlet.http.HttpServlet.service(HttpServlet.java:722) 	COM.safe.fmeserver.webui.sitemap.SiteMapFilter.doFilter(SiteMapFilter.java:40) 	COM.safe.web.servlet.CharsetEncodingFilter.doFilter(CharsetEncodingFilter.java:538) 	COM.safe.webservices.security.FMEServerAuthFilter.doFilter(FMEServerAuthFilter.java:164) Do you have an idea ? Thanks 
J
David,

 

 

Thank you. 

 

 

I have a Word document I can send with a complete example.  I think that would help.  Let me dig around on here and see how to upload a file and get back to you.

 

 

Thanks.

 

 

Joe

 

 

 

J

Hi, all.  I have an update on this issue.  A colleague showed me how to attach an Inspector to my SpatialFilter transformer, and doing so revealed that the attribute values (from both tables) I need are in fact being returned by the transformer.  This narrows the issue down to the KML Writer, which is only writing out a KML with attributes for one layer (table), even though in the Dynamic Properties both source schemas are selected.  Is there perhaps a setting I can use within this Writer that will enable it to output attributes from both layers?

 

 

Thanks very much.

 

 

JR    

Reply


Community Stats

28,802
Posts
109,791
Replies
36,632
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.