# creating line

**don123**Jun 25, 2012 11:43 PM

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.

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

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

- 102 Views
- Tags: none (add)