# Finding Intersection Points between Line and Polygon

Today, someone emailed me and asked:

I’m using sql developer and oracle spatial to develop my prototype database (I’m really a beginner in SQL), one of my task now is to query parcels (polygon) which crossed by utility pipeline (line) [… and …] to query the intersection point between polygon and line…

Can you give me advice and tip how I can accomplish this task?

Below is one method for achieving this (note that you need an Enterprise Spatial license to do this).

``` With pipelines as (
select rownum as pipeid, MDSYS.SDO_GEOMETRY(2002, 82469, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(455759.220, 6455577.890, 455618.508, 6455354.691, 455577.264, 6455432.325, 455545.725, 6455364.395, 455482.647, 6455553.629)) as geom from dual
)
select rownum as intersection_id, sdo_geometry(2001,p.geom.sdo_srid,sdo_point_type(c.x,c.y,null),null,null) as geom
from lm_parcel p,
table(select sdo_util.getVertices(sdo_geom.sdo_intersection(p.geom,l.geom,0.005)) as easement
from pipelines l,
lm_parcel p
where sdo_anyinteract(p.geom,l.geom) = 'TRUE'
and p.fid = 568470) c
where p.fid = 568470
and sdo_geom.relate(p.geom,'DETERMINE',sdo_geometry(2001,p.geom.sdo_srid,sdo_point_type(c.x,c.y,null),null,null),0.005) = 'TOUCH';

INTERSECTION_ID GEOM
--------------- -----------------------------------------------------------------------------------------------------
1               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455515.315221591,6455455.62433523,NULL),NULL,NULL)
2               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455539.783310342,6455382.22006897,NULL),NULL,NULL)
3               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455553.065512693,6455380.20529922,NULL),NULL,NULL)
4               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455609.490204436,6455371.66528816,NULL),NULL,NULL)
5               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455627.496129769,6455368.94807528,NULL),NULL,NULL)
6               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455675.025317434,6455444.33942184,NULL),NULL,NULL)

6 rows selected
```

This is what it looks like graphically:

I hope this is of help to someone.