13 Replies Latest reply: Aug 5, 2014 6:14 AM by Stefan Jager RSS

    how to find connecting line

    Saaz Ena

      Hi,

       

      I have a spatial table with geom type as 2002.

      I have a set of line (for example lets say it is 5 rows) attach image.

       

      I need to find line that is inner connecting to these 5 rows. What I mean is a boundary line has inner line.

      I am unable to get inner lines of a boundary.

      If I query spatial table I get boundary line but i want inner lines.

       

       

      attach is snapshot for ref.example.jpg

       

      thanks in advance

      saaz

        • 1. Re: how to find connecting line
          B Hall

          So, basically you want to connect some of the points (not sure which ones) of the line (which sort of represents a polygon), and then test that they are "inside" the line?

          • 2. Re: how to find connecting line
            Saaz Ena

            Hi B Hall,

             

            Thanks for your initial investigation.

            I have a bunch of lines and they form a polygon by connecting each other by their start and end points. These lines are geom type as 2002 in spatial table.

             

            When I query these lines I can see a polygon in geo raptor using sql developer.

            These boundary line are represented in database using a unique id that makes it a bunch of boundary line.

             

            For example

            table polygon

            (

            boundary_id number(10)  -- it is primary key

            )

             

            table line

            (

            line_id number(10)   -- it is primary key

            boundary_id number(10)  --it can be null or  fk to polygon table

            geom mdsys.sdo_geometry    -- all geom type iss 2002 because it is a line

            )

             

            now if i query polygon table and select 100130 as boundary_id making a join to line table it will select all geometry for boundary line.

            and i am able to see these boundary lines.

             

            but there are some lines in table line that has no boundary_id value.

            so i m finding these lines that will be present inside a polygon.

             

            so if i select 100130 it will have boundary_id with its line and also should have line that are inside these boundary_line.

             

            thanks in advance

            saaz

            • 3. Re: how to find connecting line
              B Hall

              Saaz,

               

              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).

               

              Bryan

              • 4. Re: how to find connecting line
                Stefan Jager

                Hello Saaz,

                 

                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.

                 

                If you can give us that, we may be able to help you.

                 

                Stefan

                • 5. Re: how to find connecting line
                  Saaz Ena

                  Thanks B Hall and Stefan Jager @@for your inputs.

                   

                  Actually, in my database that has spatial geometry information has SDO_GTYPE is 2002 for ids that are connected to each other.

                  and I have a second table that has no spatial geometry by a polygon information (more than one line).

                   

                  so for example in my poly_tab and line_tab

                   

                  polyexample.jpg

                   

                   

                   

                  poly_tab

                  poly_id     line_id

                  --------------------------

                  46951     1001

                  46951     1005

                  46951     1009

                  46951     1032

                  46951     1036

                  56102     1072

                  56102     1079

                  56102     1082

                  39024     1050

                  39024     1045

                  ........ and so on (note no spatial geometry column here and line_id is fk to line_tab and this table has line geom)

                  also to be noted here that 1002 and 1003 has no record in poly_tab table.

                   

                  line_tab

                  line_id   mdsys.geometry(sdo_gtype

                  ---------------------------------------------------------------

                  1001        2002

                  1005        2002

                  1009        2002

                  1032        2002

                  1036        2002  

                  1072        2002 

                  1079        2002

                  1082        2002  

                  1050        2002  

                  1045        2002

                  1002        2002

                  1003        2002

                  1019        2002

                   

                   

                  lets says if I query poly_tab where poly_id = 46951

                  it should display

                  line_id

                  ---------------

                  1001       

                  1005       

                  1009       

                  1032       

                  1036

                  1002

                  1003     

                   

                  note 1002 and 1003 are inner lines for this polygon but in poly_tab this line does not exits. (in poly table only boundary line exists)

                   

                  @Stefan Jager

                   

                  thanks in advance

                  saaz

                  • 6. Re: how to find connecting line
                    navaneet

                    Saaz Ena,

                     

                    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.

                     

                    Best

                    Navaneet

                    • 7. Re: Re: how to find connecting line
                      Stefan Jager

                      Hi Saaz,

                       

                      Well, that didn't really answer my questions, and it's a rather strange datamodel (are you trying to prevent storing duplicate lines on parcels or something?), but something like this should work:

                       

                      with boundary as

                      (

                        select * from line_tab where id in (select line_id from poly_tab where poly_id=46951)

                      )

                      select l.line_id from boundary b, line_tab l where sdo_anyinteract(l.geometry, b.geometry = 'TRUE' and

                      l.line_id not in (select line_id from boundary)

                      union all

                      select line_id from boundary;

                       

                      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

                       

                      HTH,

                      Stefan

                      • 8. Re: how to find connecting line
                        Saaz Ena

                        Hi Stefan,

                         

                        Thanks for ur help and below code. I tired with this code and it displayed some outer lines that are attach to poly_id=46951

                         

                        with boundary as

                        (

                          select * from line_tab where id in (select line_id from poly_tab where poly_id=46951)

                        )

                        select l.line_id from boundary b, line_tab l where sdo_anyinteract(l.geometry, b.geometry = 'TRUE' and

                        l.line_id not in (select line_id from boundary)

                        union all

                        select line_id from boundary;

                        • 9. Re: how to find connecting line
                          Saaz Ena

                          Hi Navaneet,

                           

                          I have not tried  with SDO_RELATE but tried with SDO_ANYINTERACT and found outlines record also.

                           

                          Thanks

                          Saaz

                          • 10. Re: how to find connecting line
                            Saaz Ena

                            @navaneet

                             

                            Hello Stefan, and Navaneet,

                             

                            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.

                             

                            polyexample.jpg

                             

                            -- sdo_anyinteract query

                            with boundary as

                            (select *

                                from line_tab

                               where id in (select line_id from poly_tab where poly_id = 46951))

                            select l.line_id

                              from boundary b, line_tab l

                            where sdo_anyinteract(l.geometry, b.geometry) = 'TRUE'

                               and l.line_id not in (select line_id from boundary)

                            union all

                            select line_id from boundary;

                             

                             

                            -- sdo_relate query

                            with boundary as

                            (select *

                                from line_tab

                               where id in (select line_id from poly_tab where poly_id = 46951))

                            select l.line_id

                              from boundary b, line_tab l

                            where SDO_RELATE (l.geometry, b.geometry, 'mask=inside+touch') = 'TRUE'

                               and l.line_id not in (select line_id from boundary)

                            union all

                            select line_id from boundary;

                             

                            Thanks in advance

                            Saaz

                            • 11. Re: how to find connecting line
                              Stefan Jager

                              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.

                              • 12. Re: how to find connecting line
                                Saaz Ena

                                Hi Stefan,

                                 

                                could you please guide me on how do i join these lines to form a polygon in a temp table.

                                I tried using SDO_UTIL.CONCAT_LINES but he did not work for me.

                                 

                                Rgds

                                Saaz

                                • 13. Re: how to find connecting line
                                  Stefan Jager

                                  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.