st_geometry syntax for Oracle Sql

Question

I'm trying to pass in a geometry(polygon) collected as an ESRI.ArcGIS.Client.Geometry.Geometry type on a map and passing it as an IN parameter into an oracle sql query. I'm also trying to use st_intersects to find features that intersect the passed-in geometry.

I can't seem to get my syntax correct. Here is my sql query:

WITH cons
 AS (SELECT DISTINCT culvertgid , condition AS condition_code,
 value AS condition_literal
 FROM culvert_inspect
 LEFT OUTER JOIN (SELECT Extractvalue (CodedValues.column_value,
 'CodedValue/Code') AS Code,
 Extractvalue (CodedValues.column_value,
 'CodedValue/Name') AS VALUE
 FROM sde.gdb_items_vw items
 inner join sde.gdb_itemtypes itemtypes
 ON items.TYPE = itemtypes.uuid,
 TABLE (
 Xmlsequence (Xmltype (DEFINITION).EXTRACT (
 '/GPCodedValueDomain2/CodedValues/CodedValue')))
 CodedValues
 WHERE itemtypes.name = 'Coded Value Domain'
 AND items.name = 'OVERALL_CONDITION')
 coded_values
 ON culvert_inspect.condition = coded_values.code)
SELECT DISTINCT ci.condition_code, condition_literal
FROM culverts c
 INNER JOIN cons ci
 ON ( c.globalid_1 = ci.culvertgid )
WHERE sde.st_intersects(c.shape, sde.ST_Polygon (-10685820.278500814 5060357.6856116494, -10685820.278500814 5101327.9327725023, -10645461.527566245 5101327.9327725023, -10645461.527566245 5060357.6856116494, -10685820.278500814 5060357.6856116494)) = 1;
ORDER BY condition_code

The code before the WHERE statement gets the domain descriptions from a table called culvert_inspect. What I'm trying to do is return only the domain values that are in the features that are within the geometry that is getting passed in. Everything works up until the WHERE statement. I've tested this same query and instead of passing in geometry, I pass in a value from a field and return expected results.

Answer 1

I figured out the syntax.

WITH cons
 AS (SELECT DISTINCT
 culvertgid,
 condition AS condition_code,
 VALUE AS condition_literal
 FROM culvert_inspect
 LEFT OUTER JOIN
 (SELECT EXTRACTVALUE (CodedValues.COLUMN_VALUE,
 'CodedValue/Code')
 AS Code,
 EXTRACTVALUE (CodedValues.COLUMN_VALUE,
 'CodedValue/Name')
 AS VALUE
 FROM sde.gdb_items_vw items
 INNER JOIN sde.gdb_itemtypes itemtypes
 ON items.TYPE = itemtypes.uuid,
 TABLE (
 XMLSEQUENCE (
 Xmltype (DEFINITION).EXTRACT (
 '/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues
 WHERE itemtypes.name = 'Coded Value Domain'
 AND items.name = 'OVERALL_CONDITION') coded_values
 ON culvert_inspect.condition = coded_values.code)
SELECT DISTINCT ci.condition_code, condition_literal
FROM culverts c INNER JOIN cons ci ON (c.globalid_1 = ci.culvertgid)
WHERE sde.ST_Intersects (c.shape, sde.st_polygon ('polygon ((-10643359.509288402 5078836.3371996051, -10643359.509288402 5082008.4738734355, -10640378.465185285 5082008.4738734355, -10640378.465185285 5078836.3371996051, -10643359.509288402 5078836.3371996051))', SRID)) = 1
ORDER BY condition_code;