8 Replies Latest reply: Jun 27, 2012 5:58 AM by don123 RSS

    creating line

    don123
      hi,

      i have points table and need to convert as lines, i am using 11g spatial EE, the line should form with following order

      SQL> select gid, gidn, pointid from polypoints where gid=1086 order by gid, gidn, pointid;

      GID GIDN POINTID
      ---------- ---------- ----------
      1086 451 1
      1086 451 9
      1086 451 10
      1086 1069 1
      1086 1069 11
      1086 1069 12

      6 rows selected.


      sample data

      SQL> select gid, gidn, pointid, geometry from polypoints where gid=1086 order by gid, gidn, pointid;

      GID GIDN POINTID
      ---------- ---------- ----------
      GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
      --------------------------------------------------------------------------------
      1086 451 1
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)

      1086 451 9
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935, NULL), NULL, NULL)

      1086 451 10
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)


      GID GIDN POINTID
      ---------- ---------- ----------
      GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
      --------------------------------------------------------------------------------
      1086 1069 1
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL)

      1086 1069 11
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543506, 49.019026, NULL), NULL, NULL)

      1086 1069 12
      SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL)


      6 rows selected.

      =====================================================================

      i am using following SQL, it executes with success, but output is not correct.

      SELECT c.gid, c.gidn, mdsys.sdo_geometry(2002,8307,NULL, mdsys.sdo_elem_info_array(1,2,1),
      CAST(MULTISET(SELECT b.COLUMN_VALUE FROM polypoints a,
      TABLE(mdsys.sdo_ordinate_array(a.geometry.sdo_point.x, a.geometry.sdo_point.y)) b
      WHERE a.gidn = c.gidn
      ORDER BY a.gid, a.gidn, a.pointid)
      AS mdsys.sdo_ordinate_array)) AS geom
      FROM polypoints c
      GROUP BY c.gid, c.gidn
      ORDER BY c.gid, c.gidn;

      ================================================================

      thanks in advance...
        • 1. Re: creating line
          Simon Greener
          908275 (thanks in advance).....

          (What is your name?)

          I hacked the data out of your posting and ran it using:
          WIth polypoints as ( 
           select 1086 as gid, 451  as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL) as geometry from dual union all
           select 1086 as gid, 451  as gidn,  9 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935,  NULL), NULL, NULL) as geometry from dual union all
           select 1086 as gid, 451  as gidn, 10 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL) as geometry from dual union all
           select 1086 as gid, 1069 as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL) as geometry from dual union all
           select 1086 as gid, 1069 as gidn, 11 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543506, 49.019026, NULL), NULL, NULL) as geometry from dual union all
           select 1086 as gid, 1069 as gidn, 12 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL) as geometry from dual 
          )
          SELECT c.gid, c.gidn, mdsys.sdo_geometry(2002,8307,NULL, 
                                                   mdsys.sdo_elem_info_array(1,2,1),
                                                   CAST(MULTISET(SELECT b.COLUMN_VALUE 
                                                                   FROM polypoints a,
                                                                        TABLE(mdsys.sdo_ordinate_array(a.geometry.sdo_point.x, a.geometry.sdo_point.y)) b
                                                                  WHERE a.gidn = c.gidn 
                                                                  ORDER BY a.gid, a.gidn, a.pointid) 
                                                        AS mdsys.sdo_ordinate_array)) AS geom
            FROM polypoints c
           GROUP BY c.gid, c.gidn
           ORDER BY c.gid, c.gidn;
          -- Results
          --
          GID GIDN GEOM
          --- ---- -------------------------------------------------------------------------------------------------------------------------------------
          1086  451 SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543362,49.019445,2.543037,49.01935,2.543362,49.019445))
          1086 1069 SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543157,49.019021,2.543506,49.019026,2.543157,49.019021))
          These results look correct to me (though I note that "1086 as gid, 451 as gidn, 10 as pointid" is the same as "1086 as gid, 451 as gidn, 1 as pointid"

          I assume this is a subset from the original.

          When you say:
          but output is not correct.
          What do you mean exactly?

          regards
          Simon
          • 2. Re: creating line
            don123
            simon, i am masood, i am using SQL from your earlier posts in this forum.

            i displayed the output lines on points, they dont match, i think problem is with grouping and ordering

            the SQL select statement should sort based on gid, gidn and pointid, but should group the points based on only gidn and pointid

            in this case line should form as below.

            gidn 451 with vertex order 1,9,10 and
            gidn 1069 with vertex order 1,11,12

            but it is necessary to sort by gid (in this case 1086), because there are records with gidn 451 and 1069 with gid other than 1086, please see below examples.

            SQL> select gid, gidn from polypoints where gidn=451 order by gidn ;

            GID GIDN
            ---------- ----------
            444 451
            444 451
            1086 451
            1086 451
            1086 451

            SQL> select gid, gidn from polypoints where gidn=1069 order by gidn ;

            GID GIDN
            ---------- ----------
            598 1069
            598 1069
            1086 1069
            1086 1069
            1086 1069


            ==========================================
            i should use gid in sorting gidn but line should form only with gidn

            i hope i explained the problem correctly

            thanks
            • 3. Re: creating line
              Simon Greener
              Masood,

              If I include the pointid as a Z in the output linestrings you will notice that the ordering is correct because in the CAST(MULTISET the selected points by gidn and gid are ordered also by pointid
              WIth polypoints as ( 
               select 1086 as gid, 451  as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL) as geometry from dual union all
               select 1086 as gid, 451  as gidn,  9 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935,  NULL), NULL, NULL) as geometry from dual union all
               select 1086 as gid, 451  as gidn, 10 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL) as geometry from dual union all
               select 1086 as gid, 1069 as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL) as geometry from dual union all
               select 1086 as gid, 1069 as gidn, 11 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543506, 49.019026, NULL), NULL, NULL) as geometry from dual union all
               select 1086 as gid, 1069 as gidn, 12 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL) as geometry from dual 
              )
              SELECT c.gid, 
                     c.gidn, 
                     mdsys.sdo_geometry(3002,8307,NULL, 
                                        mdsys.sdo_elem_info_array(1,2,1),
                                        CAST(MULTISET(SELECT b.COLUMN_VALUE 
                                                        FROM polypoints a,
                                                             TABLE(mdsys.sdo_ordinate_array(a.geometry.sdo_point.x, a.geometry.sdo_point.y,a.pointid)) b
                                                       WHERE a.gidn = c.gidn 
                                                       ORDER BY a.gid, a.gidn, a.pointid) 
                                            AS mdsys.sdo_ordinate_array)) AS geom
                FROM polypoints c
               GROUP BY c.gid, c.gidn
               ORDER BY c.gid, c.gidn;
              -- Results
              --
               GID GIDN GEOM
              ---- ---- ----------------------------------------------------------------
              1086  451 SDO_GEOMETRY(3002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543362,49.019445,1,2.543037,49.01935,9,2.543362,49.019445,10))
              1086 1069 SDO_GEOMETRY(3002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543157,49.019021,1,2.543506,49.019026,11,2.543157,49.019021,12))
              So, you want this order in the linestrings....
              gidn 451 with vertex order 1,9,10 and
              gidn 1069 with vertex order 1,11,12
              And, if your look at the linestring sdo_ordinate_array Z values for each line this is what you get.

              Sorry, I'm a bit thick. What is the problem?

              regards
              Simon
              • 4. Re: creating line
                don123
                Simon,

                the problem is linestring output is not matching with points when displayed.

                i checked some output from lines and points for a same id, please see below, the linestring is taking two additional coordinates.

                SQL> select geometry from polypoints where gid=1086 and gidn=451;

                GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                --------------------------------------------------------------------------------
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935, NULL), NULL, NULL)
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)

                SQL> select geometry from polylines where gid=1086 and gidn=451;

                GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                --------------------------------------------------------------------------------
                SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                2.542918, 49.019813, 2.542694, 49.019625, 2.543362, 49.019445, 2.543037, 49.0193
                5, 2.543362, 49.019445))


                SQL> select geometry from polypoints where gid=1086 and gidn=1069;

                GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                --------------------------------------------------------------------------------
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL)
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543506, 49.019026, NULL), NULL, NULL)
                SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL)

                SQL> select geometry from polylines where gid=1086 and gidn=1069;

                GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                --------------------------------------------------------------------------------
                SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                2.542906, 49.018541, 2.54319, 49.0184, 2.543157, 49.019021, 2.543506, 49.019026,
                2.543157, 49.019021))
                • 5. Re: creating line
                  Simon Greener
                  Masood,

                  It looks to me like your data or your SQL is screwed.

                  In this:
                  SQL> select geometry from polypoints where gid=1086 and gidn=451;
                  
                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)
                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935, NULL), NULL, NULL)
                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)
                  The first and last coordinate are repeated (as I pointed out in one of my responses).

                  You don't include the pointid as in your original query (next) so how can one know the order of the new data without it?
                  SQL> select gid, gidn, pointid, geometry from polypoints where gid=1086 order by gid, gidn, pointid;
                  
                   GID GIDN POINTID
                   ----------
                   GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                   1086 451 1  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)
                   1086 451 9  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935, NULL), NULL, NULL)
                   1086 451 10  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL)
                  This is the same for the other example.

                  So, as I have said before, I cannot see what is the problem. My query AFAICS is correct. Perhaps you need to give me
                  all the pointids of all the vertices in the two lines.

                  I modified the last point (10,12) value in each of your data so that it is not the same as the first:

                  We process this to form lines...
                  WIth polypoints as ( 
                   select 1086 as gid, 451  as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019445, NULL), NULL, NULL) as geometry from dual union all
                   select 1086 as gid, 451  as gidn,  9 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543037, 49.01935,  NULL), NULL, NULL) as geometry from dual union all
                   select 1086 as gid, 451  as gidn, 10 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543362, 49.019745, NULL), NULL, NULL) as geometry from dual union all
                   select 1086 as gid, 1069 as gidn,  1 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019021, NULL), NULL, NULL) as geometry from dual union all
                   select 1086 as gid, 1069 as gidn, 11 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543506, 49.019026, NULL), NULL, NULL) as geometry from dual union all
                   select 1086 as gid, 1069 as gidn, 12 as pointid, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.543157, 49.019321, NULL), NULL, NULL) as geometry from dual 
                  )
                  SELECT c.gid, c.gidn, mdsys.sdo_geometry(3002,8307,NULL, 
                                                           mdsys.sdo_elem_info_array(1,2,1),
                                                           CAST(MULTISET(SELECT b.COLUMN_VALUE 
                                                                           FROM polypoints a,
                                                                                TABLE(mdsys.sdo_ordinate_array(a.geometry.sdo_point.x, a.geometry.sdo_point.y,a.pointid)) b
                                                                          WHERE a.gidn = c.gidn 
                                                                          ORDER BY a.gid, a.gidn, a.pointid) 
                                                                AS mdsys.sdo_ordinate_array)) AS geom
                    FROM polypoints c
                   GROUP BY c.gid, c.gidn
                   ORDER BY c.gid, c.gidn;
                  -- Results
                  --
                  GID GIDN GEOM
                  --- ---- ---------------------------------------------------------
                  1086  451 SDO_GEOMETRY(3002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543362, 49.019445, 1.0, 2.543037, 49.01935, 9.0, 2.543362, 49.019745, 10.0))
                  1086 1069 SDO_GEOMETRY(3002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543157, 49.019021, 1.0, 2.543506, 49.019026, 11.0, 2.543157, 49.019321, 12.0))
                  And so we can visualise the resulting line as: http://www.spatialdbadvisor.com/files/LineFromPoints.png

                  Finally, Mansood, if you want me to work on any more data please present it within a WITH clause as above and not as a dump from a SQL Select statement. This will save me some time.

                  regards
                  Simon
                  • 6. Re: creating line
                    don123
                    Thanks simon,
                    as you said, there is a problem in data, there are same coordinates with different gid

                    SQL> select geometry from polypoints where gid=590;

                    GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                    --------------------------------------------------------------------------------
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.560516, 49.019488, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.561192, 49.019522, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.561504, 49.019002, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.56151, 49.019378, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.561504, 49.019002, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.560327, 49.018992, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.560275, 49.01932, NULL), NULL, NULL)


                    SQL> select geometry from polypoints where gid=591;

                    GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                    --------------------------------------------------------------------------------
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.56237, 49.019424, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.562585, 49.019089, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.56237, 49.019424, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.561504, 49.019002, NULL), NULL, NULL)
                    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(2.56151, 49.019378, NULL), NULL, NULL)


                    i have tried to remove duplicate vertices with sdo_util.remove_duplicate_vertices, but there is no use, i think it works for polygon geometry.
                    • 7. Re: creating line
                      Simon Greener
                      Mansood,

                      sdo_util.remove_duplicate_vertices can only remove duplicate vertices within a polygon or linestring where they are next to each other in the linestring eg
                      select sdo_util.remove_duplicate_vertices(SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(
                      2.543362, 49.019445, 
                      2.543362, 49.019445,
                      2.543037, 49.01935)),0.05) as line
                      from dual;
                      -- Result
                      --
                      SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.543362, 49.019445, 2.543037, 49.01935))
                      It cannot be used in the SQL to filter the a bunch of independent points before creating a linestring from them.

                      If the source data has duplicate points in it that are separated by other points perhaps that is what was observed or the observation method was wrong, or there is some sort of coordinate rounding (though 6 decimal places is around 11cm).

                      Anyway, sounds like all my work has answered your question or been helpful. Some points please!

                      regards
                      Simon
                      • 8. Re: creating line
                        don123
                        Thanks Simon