I have this query
--this outputs interactions correctly. but does NOT show intersection
SELECT lines.pavement_key, poly.SUB_DISTRICT_NAME_UPPER ,
sdo\_geom.sdo\_length (lines.shape, .5) shape\_len,
sdo\_geom.sdo\_intersection (lines.shape, poly.shape, .5) **intesection**
FROM DW_RAH_PAVEMENT_SECTIONS_DIM lines join DW_SUBDISTRICT_MASTER_DIM poly
on MDSYS.SDO_ANYINTERACT(lines.shape, poly.shape)='TRUE'
where POLY.SUB_DISTRICT_NAME_UPPER='CRAWFORDSVILLE'
and LINES.PAVEMENT_KEY in (1003,1029);
notice the 2 records/shapes (pavement keys 1003, 1029 are completely inside the polygon)
is that behavior expected?
I thought those 2 lines would still have an intersection (since they are completely inside the polygon)
edit: I also ran this DETERMINE sql, but it shows disjoint, which is unexpected (see map view from sql developer above)
SELECT lines.pavement_key, poly.SUB_DISTRICT_NAME_UPPER ,
SDO_GEOM.RELATE(lines.shape, 'DETERMINE', poly.shape,.5)
FROM DW_RAH_PAVEMENT_SECTIONS_DIM lines join DW_SUBDISTRICT_MASTER_DIM poly
on MDSYS.SDO_ANYINTERACT(lines.shape, poly.shape)='TRUE'
where POLY.SUB_DISTRICT_NAME_UPPER='CRAWFORDSVILLE'
and LINES.PAVEMENT_KEY in (1003,1029);
I also ran this to compare the srid's
--both srid's are 4269
SELECT distinct 'DW_RAH_PAVEMENT_SECTIONS_DIM',lines.shape.sdo_srid FROM DW_RAH_PAVEMENT_SECTIONS_DIM lines --SRID 4269
union all
SELECT distinct 'DW_SUBDISTRICT_MASTER_DIM',poly.shape.sdo_srid FROM DW_SUBDISTRICT_MASTER_DIM poly; --SRID 4269
I also ran this to compare metadata
--sqldev -Prefs, Database, Advanced, "Display Struct Data In Grid" (to show SDO_DIM_ARRAY details)
SELECT * FROM USER_SDO_GEOM_METADATA where TABLE_NAME in ('DW_RAH_PAVEMENT_SECTIONS_DIM','DW_SUBDISTRICT_MASTER_DIM');
and
SELECT META.TABLE_NAME, META.COLUMN_NAME, META.SRID, DIM.*
FROM USER_SDO_GEOM_METADATA META, TABLE(META.DIMINFO) DIM
where TABLE_NAME in ('DW_RAH_PAVEMENT_SECTIONS_DIM','DW_SUBDISTRICT_MASTER_DIM');
also if this helps, I have the min/max x and y for the poly and one of the lines in question
--min/max x/y for each shape
SELECT 'DW_SUBDISTRICT_MASTER_DIM' tbl, to_char(min(POLY.SUB_DISTRICT_NAME_UPPER))||'_poly' shape_name,
min(poly.shape.sdo\_srid) srid,
min(verts.x) min\_x, max(verts.x) max\_x,
min(verts.y) min\_y, max(verts.y) max\_y
FROM DW_SUBDISTRICT_MASTER_DIM poly --SRID 4269
,TABLE(sdo_util.getvertices( shape )) verts
where POLY.SUB_DISTRICT_NAME_UPPER='CRAWFORDSVILLE'
union all
SELECT 'DW_RAH_PAVEMENT_SECTIONS_DIM', to_char(min(LINES.PAVEMENT_KEY))||'_line',
min(lines.shape.sdo\_srid) srid,
min(verts.x) min\_x, max(verts.x) max\_x,
min(verts.y) min\_y, max(verts.y) max\_y
FROM DW_RAH_PAVEMENT_SECTIONS_DIM lines --SRID 4269
,TABLE(sdo_util.getvertices( shape )) verts
where LINES.PAVEMENT_KEY in (1003);
can someone help me sort this out?