You keep interchanging line and polygon as if they are the same, they are not. I agree that what you have looks like a polygon, and you say the start and end nodes are the same. So I would start by moving the lines that can be made polygons, polygons and placing them in a separate column from the lines?

When you do make some of them true polygons, you can use SDO_RELATE with 'mask=inside+touch' to find all the lines that are inside or touch the outside. As long as they remain lines, at best you can go row-by-row and convert them to polygons and then test to see if they match (much slower).

I've been looking at your questions and explanations a couple of times now and I still do not understand what your problem is or what it is you are trying to achieve.

So in order to help us help you, can you give us the following:

1. A description of what you are trying to do in real-world terms. For example: "I'm trying to find all the roads within a county" or "I need all powerlines which are not connected to the mains" or something like that?

2. create table scripts and a couple of insert-statements, so that we can duplicate your data? It does not need to be your whole dataset, just a few rec0ords that show your problem.

Looks like your problem is already answered by Bryan Hall using SDO_RELATE with 'mask=inside+touch'Re: how to find connecting line. Did you try it? If it is correct, let us all know. You may able to connect these missing line segments.

Note that I have NOT tested this, you'll need to try it to see if it works. And, this will also give you lines that are on the outside of your collection of lines that together form a polygon (because you have no polygons, you only have lines). But I don't know if they are there. If you'll give me some insert statements, including the geometry, I can try to go a little bit further.

I would setriously reconsider my datamodel. This is not an easy way of storing information, and it is not necessary. Normalization of your data is good but if it requires this sort of difficulties to get your information it defetas the purpose. Store real polygons, instead of these link-tables. Makes life much easier, and the extra storage needed won't be that much. I am intrigued though: what kind of data is this? and what application needs it's data in this format? Just out of curiosity

I tried using SDO_RELATE with above query and SDO_INTERACT. Both had the same output. It displayed inner lines as well as outer lines. The green lines are outer lines and basically not expected in output.

INSIDE is not going to work here, it won't do anything because you do NOT have a real polygon. It's a collection of lines, and inside for a collection of lines means on the lines itself (check the Oracle nine-intersection model: Spatial Concepts). Using inside with this datamodel is only going to work if you actually take your lines and create a polygon from it, then use that to query. Since I just wrote down my query from the top of my head and didn't test it, I knew that it would also give you the outside connecting lines. And I said so in my post. This is also why I said that if possible you should rethink your datamodel. Again: you do NOT have a polygon, so do not try to use that as your query model.

Creating a polygon from your lines inside a query is going to require a bit of PL/SQL, you'll have to process all the lines coming in and adding them together to create a polygon. That could then be used to create a query with Inside. Better yet: store things that are polygons as polygons and things that are lines as lines, and then things will be much easier.

That being said: did my query give you the results that you needed, apart from returning too many lines on the outside? If so, you can work from there: create a function that takes a collection of lines and turns it into a polygon, then use that in your query. Be aware though that using a function in your query will slow it down.

That's because CONCAT_LINES does not create a polygon, it only concatenates lines into a new line. But still a line (even though the endpoint and the startpoint of that line may be the same).

It might be a good idea to start reading the Spatial Documentation: Contents, since it appears that there are some concepts in Oracle Spatial that are unclear to you. Basically: there is no easy simple function to create a polygon from a bunch of lines delivered with Oracle, so you'll have to create one yourself.

Concat_lines could be a start though, use that to create one line, then check the endpoint and the startpoint: if they are exactly the same, you can create your polygon. If they are not the same, you can copy the startpoint to the end. Then you can change the GTYPE and the SDO_ELEM_INFO so that the whole element becomes a polygon. With a polygon you can use inside effectively to get the results that you want. I do not have access to my development environment here, so can't help you build something right now. But maybe someone else has something already?

Oh, and as an afterthought: do not store your polygons in a temporary table, but change your datamodel and start using them properly. Temp tables will only confuse things more.