EJB QL - Intersections or Unions?

[Brian Smith]

I have a m:n relationship represented by two m:1 relationships. These correspond to intersections and streets. I have a street entity, an intersection entity and an intersectionStreet entity.

I am trying to find all intersections given two of the streets that participate in that intersection.

For example - Find the intersection where Main St. and Wall St. intersect

Finding an intersection based on one street is easy

Select ist.intersection From IntersectionStreet ist Where
ist.street = :street

But here I am looking for an intersection given two of the above queries given two different streets. Is such a thing possible using ejb ql? I think my last hope would be an Oracle Native query using their SQL Intersection functions or a Union. I don't want duplicate results.

Thanks
[ September 24, 2007: Message edited by: Brian Smith ]

[jeroen dijkmeijer]

Hm reminds me of GIS classes quite a while ago, things with points, lines areas and so on.

Point:
id Number
location xy(z) location
info.

Street (line):
id Number
firstPointId Number (FK point.id)
secondPointId Number (FK point.id)
leftAreaId Number (FK area.id)
rightAreaId Number (FK area.id)
info Varchar

Area:
id Number
info Varchar.

These are the basics of a Topology and rock solid. Conversion to an object Model is not that hard.
Of course your streets can have more intersections, but than a street should be divided into multiple lines. add an extra entity street which holds a set of all "street lines" (the streets between the intersections).

topology1
but google will definitely tell you more, hope this helps.
Jeroen.
[ September 25, 2007: Message edited by: jeroen dijkmeijer ]