I have the following
ORACLE_SPATIAL --- Dissolver ------ GeometryExtractor ---- SQLExecutor
I'm reading some polygons and then dissolving to give the outline. Extracting the geometry and using the geometry in the SQLExecutor with SDO_RELATE.
It isn't returning any data. How do I use the geometry in SQLExecutor?
What do you currently use in the SQLExecutor?
You could first use a GeometryExtractor to convert the outline geometry to WKB (OGC Well-known binary) and then reference it inside the SQLExecutor with something like this, assuming that your WKB is stored in an attribute called "_geometry" and your geometry is stored in a field called "shape":
select *
from my_schema.my_table a
where sdo_relate(a.shape, sdo_util.from_wkbgeometry('@Value(_geometry)'), 'mask=anyinteract') = 'TRUE'
Be aware that the above might not work if the WKB is more than about 4000 characters long. In that case you might consider generalizing your outline a bit before converting it to WKB.
You might also want to look at the FeatureReader, which will let you do about the same thing, but with a much more user-friendly interface, and I believe, without the above limitation.
David
+59I don't know about using geometry in the SQLExecutor, but would the FeatureReader be a replacement for it? That will read data from a database and allow you to use a spatial relationship to decide what to read; and you won't need to write any SQL.
+59I don't know about using geometry in the SQLExecutor, but would the FeatureReader be a replacement for it? That will read data from a database and allow you to use a spatial relationship to decide what to read; and you won't need to write any SQL.
Which I just noticed David mentioned already! Still, I think he's correct that it does the same thing but much more simply.
+15FeatureReader is very limited in that respect.
Creating wkb in FME and using it in Oracle spatial did'nt work for me in fme 2015. Using wkt does.
However, doing that loses the SRID. Wich is nescesarry for spatialfunctions in Oracle.
So won't work afaik.
What does work however is using fme to build a SDO geometry string. (Like a charm and very fast)
Here is a circle sample:
...
and SDO_FILTER( g.geometrie,
SDO_GEOMETRY(
2003, -- two-dimensional polygon
28992,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle
SDO_ORDINATE_ARRAY (@Value(ordinate))),'querytype=WINDOW') = 'TRUE'
...
Circle in 3 bits then get its coordinates and use a concatenator to concatenate x,y coordinates into SDO string wich i named @Value(ordinate)). This way u can use a SRID ( in this case 28992).
You could create a attribute or parameter for each element in the SDO string and fully control it.
Or create a different one for lines, points, polygons etc.
To do this u are advised to read up on SDO geometry types.
WKT/WKB , to add SRIDi only seen while actually writing into the database. Not a an external geometry.
+26You definitely want to use the FeatureReader here as mentioned above.
And in that case you do not need the GeometryExtractor at all.
+15Fetaure reader only allows for simple where clauses.
SQL executor allows for execution of any SQL you can built and you can do this dynamically to boot.
Even if i do read with a simple where query, if at all, using sdo functions is much faster then using a featurereader.
I definitely do not want to use a featurereader.
