Hello,
I often use SQL creator and SQL executor to extract data of my dB.
My select-queries always include the oracle geometry (stored in a SHAPE column).
However, I noticed only in some cases the geometry is extracted in my workflow.
I have, to this day, not been able to understand in which cases this works and in which it doesn't (I then add a feature merger to re-append the geometry to the features).
Can someone explain this to me?
Thank you
I don't know what the criteria is for FME to automatically recognize the geometry column, but my go-to workaround is to cast the geometry to WKB and unpack it manually in FME.
Example (for SDE in this case):
select attr1, attr2, sde.st_asbinary(shape) as wkb
from mytableYou can then join it to a GeometryReplacer set to parse the "wkb" column output as OGC Well-Known Binary.
Hi @tsirkuselab, I was chatting with our Developer on this and it is a strange space... "some formats can do it, others need help, others not so much.". That's the state of this workflow. If you find yourself having some issues getting a geometry extracted in this way let us know the format you are working with. There may be away to solve it and there may not be.
Unfortunately there is an inconsistency across formats for this scenario.
To the readers of this post: I'm not sure how much folks use these transformers for this and I'd be curious to learn if you are.
So to anyone reading this... please let us know if this is a workflow you make use of. Maybe we can look at improving the experience by ensuring it is clear what formats are supported and what formats are not.
If you have examples, like David's above from your own workflows please share.
Hi @tsirkuselab, I was chatting with our Developer on this and it is a strange space... "some formats can do it, others need help, others not so much.". That's the state of this workflow. If you find yourself having some issues getting a geometry extracted in this way let us know the format you are working with. There may be away to solve it and there may not be.
Unfortunately there is an inconsistency across formats for this scenario.
To the readers of this post: I'm not sure how much folks use these transformers for this and I'd be curious to learn if you are.
So to anyone reading this... please let us know if this is a workflow you make use of. Maybe we can look at improving the experience by ensuring it is clear what formats are supported and what formats are not.
If you have examples, like David's above from your own workflows please share.
hi Steve,
I came across this today.
I am using FME 2019 version and connecting to MS sql server using sql creator to access the table with geometry and then linking to another table with no geometry. The geometry then is lost in transition.
Are there any developments since this post? or perhaps the newer versions have fixed this?
thanks in advance.
@tsirkuselab I think in most cases the geometry will get picked up from the first table referenced in your SQL query. So if you're doing a join and the join table has you're geoemtry, then you'd have to CAST as @david_r suggests