Skip to main content
Solved

Spatial query with SQLExecutor

P

I am trying to make a spatial query with SQLExecutor and having some issues. My filter is some point buffer features in a ESRI geodatabase and the feature points I want to filter are in an Oracle database. I am extracting the geometry and using SDO_RELATE as shown bellow. Using FME 2016.

My SQL query:

select * from gds.emb a

 

where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')

 

and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')

 

and sdo_RELATE(a.GEOM,@Value(_geometry),'mask=anyinteract')='TRUE'

Best answer by erik_jan

Do you need to use the SQLExecutor?

I would use a reader for the ESRI feature and feed that to a FeatureReader transformer, reading the Oracle table and defining the spatial filter in the FeatureReader.

That is a lot easier.

View original
Did this help you find an answer to your question?

10 replies

P

 

select * from gds.emb a

 

where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')

 

and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')

 

and sdo_RELATE(a.GEOM,@Value(_geometry),'mask=anyinteract')='TRUE'

 

erik_jan
Contributor
Forum|alt.badge.img+18
  • erik_janContributor
  • Contributor
  • Best Answer
  • August 2, 2017

Do you need to use the SQLExecutor?

I would use a reader for the ESRI feature and feed that to a FeatureReader transformer, reading the Oracle table and defining the spatial filter in the FeatureReader.

That is a lot easier.

fmelizard
Safer
Forum|alt.badge.img+18
  • fmelizardSafer
  • Safer
  • August 2, 2017
Hi @patiaserica, would you be able to elaborate on the issues you're experiencing? Can you share any of the error messages?
david_r
Celebrity
  • david_rCelebrity
  • August 3, 2017

I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:

select * from gds.emb a 
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'

Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.

P
david_r wrote:

I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:

select * from gds.emb a 
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'

Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.

Yes, I am using OGC WKT and already tried that one, but having this kind of error:

 

ORA-06512: em "MDSYS.MD", line 1723
ORA-06512: em "MDSYS.MDERR", line 17

 


ORA-06512: em "MDSYS.SDO_CS", line 3019

 


ORA-06512: em "MDSYS.SDO_3GL", line 205'

 

 

P
david_r wrote:

I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:

select * from gds.emb a 
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'

Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.

Maybe the problem is lack of spatial index?

 

 

david_r
Celebrity
  • david_rCelebrity
  • August 4, 2017
patiaserica wrote:
Maybe the problem is lack of spatial index?

 

 

Not sure, but that ORA-message is purely Oracle not not directly related to FME. Try executing the same query in Toad or SQLDeveloper and see if you get more information.
P
erik_jan wrote:

Do you need to use the SQLExecutor?

I would use a reader for the ESRI feature and feed that to a FeatureReader transformer, reading the Oracle table and defining the spatial filter in the FeatureReader.

That is a lot easier.

Worked perfectly!

 

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • August 7, 2017

It is possible to do that.

You have to built the SDO string. I made a workbench to do that (after quite some fiddling). Explanation on ORACLE site is used to create it.

(it works as long as the geometry fits in a varchar as I pass it trough an fme attribute, after which you need to go for a PLSQL which I have not managed to do yet)

The WKT/WKB I tried as well, but you cant pass the SRID with the version fme uses. That is where oracle yields an error. You need updated wkt aka WKT2 for that.

To pass the srid you need

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • August 8, 2017

creating the sdo geometry string and executing the spatial query using a sql executor is faster then using a feature reader.

Reply


Helpful Members This Week

Community Stats

31,341
Posts
118,859
Replies
38,931
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.