8 Replies Latest reply on Apr 4, 2012 11:33 AM by Simon Greener

# Line Constructor

Hi

I have point geometries with id and i want to construct line geometry from this. If there are only two points for a line, i can pass start X, start Y, end X, end Y to sdo_ordinate_array.

But if i have intermediate vertices for a given id, how to do this as count of intermediate vertices is uncertain.

id x y
---------------------------------------
1 x1 y1
1 x2 y2
1 x3 y3
1 x4 y4
1 x5 y5
2 x1 y1
2 x2 y2
2 x3 y3

• ###### 1. Re: Line Constructor
Hi

I have tried keeping this into a select statement using analytical functions.

What you do not seem to have is a sequence number of the points within the line in your data. Hence we will generate one using rownum over rowid.
I am not 100% sure but this I believe will use the sequence in which the recs were inserted as the sequence nr.

Might sure be other and maybe better approaches but this seemed to work.

select id, SDO_AGGR_CONCAT_LINES( segment)
from
(
select id,
lsn,
max(lsn) OVER (PARTITION BY ID ORDER BY id ASC) MaxLSN,
segment
from
(
select id,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY id,rn ASC) LSN,
sdo_geometry(2002, null, null, SDO_ELEM_INFO_ARRAY (1,2,1), SDO_ORDINATE_ARRAY ( x, y, LEAD(x, 1, 0) OVER (PARTITION BY ID ORDER BY id, rn ASC), LEAD(y, 1, 0) OVER (ORDER BY id, rn ASC) )) segment
from
(
SELECT
ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY rowid ASC) RN,
x, y,
X || ',' || Y as coord,
sdo_geometry(2001, null, SDO_POINT_TYPE(x, y, null), null, null)
FROM
linepoints
)
))
where LSN < maxlsn
group by ID;

I Hope this helps.

Luc
• ###### 2. Re: Line Constructor
Here is another way to do this.

The With CTE is only there to generate some test data. You can get rid of the whole block and replace the last SELECT's from points c with your table name that contains the following data:
1 x1 y1
1 x2 y2
1 x3 y3
1 x4 y4
1 x5 y5
2 x1 y1
2 x2 y2
2 x3 y3
With points As (
SELECT trunc(level / 5) as geom_id,
row_number() over (partition by trunc(level / 5) order by rownum) as point_id,
round(dbms_random.value(1,10000),3) as X,
round(dbms_random.value(1,10000),3) as Y
FROM dual
CONNECT BY LEVEL < 30
)
SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM points a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.geom_id = c.geom_id order by a.point_id) as mdsys.sdo_ordinate_array)) as geom
FROM points c
GROUP BY c.geom_id;
-- Results
--
GEOM
----
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(5144.458,3199.919,2251.939,6849.938,6919.348,1134.661,565.804,4384.515,1628.731,6092.055))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(122.862,3006.545,9581.443,9379.387,7659.869,6859.322,7936.498,151.507,926.478,4598.965))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6373.324,759.294,44.012,9814.083,8125.855,612.249,2699.456,8318.277,1715,7349.652))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(8923.524,6996.764,6518.515,3501.614,6053.453,8277.119,7652.991,9403.059,8728.289,9505.907))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6828.892,7137.751,3420.856,8590.158,1346.581,6701.412,9105.74,951.904,951.429,1062.246))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1467.562,8577.782,8580.429,8866.923,3676.022,6519.497,8639.309,5164.398))
Isn't SQL grand!

regards
S

Edited by: Simon Greener on Mar 31, 2012 1:42 PM
• ###### 3. Re: Line Constructor
Thanks to both..

Simon,

I am bit confused with the table alias in your sql, can you rewrite sql according to my table structure.

table name with point geometries=LINEPOINTS
geometry column in table=GEOMETRY
POINTID is serial number for point geometry, FEATURE_ID to be used for grouping point geometries into line geometry.

FEATURE_ID POINTID
===============
398639 11675
398639 11676
398639 11677
398639 11678
398639 11679
398639 11680
398639 11681
398639 11682
2215595 11683
2215595 11684
2215595 11685
2215595 11686

Thanks again
• ###### 4. Re: Line Constructor

What alias are you taking about? The "points" in "With points as" is the virtual table name constructed by the Common Table Expression (CTE) - it is NOT an alias. The only alias is "c". Not that confusing.

So, I am confused by your question.

In the initial request you gave us a bunch of points "id,x,y" but no table name.

Now you give me a table name - linepoints - with a geometry column - geometry - which I suppose is the destination for the created linestrings.

But a problem springs to mind: Why are the IDs in the original posting 1 and 2 - which I assume is some sort of aggregate - different from the feature_id/pointids in this new post (11675 etc)? What is the link between the original id,x,y data (what is the table name?) and the data identified by feature_id/pointid.

Now, if I assume that you id,x,y is actually supposed to be pointid,x,y in a table called rawpoints then the SQL could be written as something like this (not sure if this is correct as I have no real table names and data to run with):
SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM rawpoints a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.pointid = c.pointid order by a.pointid) as mdsys.sdo_ordinate_array)) as geom
FROM linepoints c
GROUP BY c.feature_id;
Note: For those of us who endeavour to help posters on this forum it does help to have properly defined data sources.

regards
Simon
• ###### 5. Re: Line Constructor
Simon,

sorry for the confusion, please ignore first posting...

i have point geometry table, i need to create new line geometry table by using point geometries.

point geometry table name: linepoints
geometry column: geometry
points serial (sequence) number: pointid
column to group points as line: feature_id

regards
syed masood
• ###### 6. Re: Line Constructor
Syed,

OK, here is an example that I hope reflects your situation.

Firstly, I will create a table called linepoints that I think contains data like yours:
create table linepoints as
SELECT case when trunc(level / 7) = 0 then 398639 else 2215595 end as feature_id,
row_number() over (partition by 1 order by rownum) as point_id,
sdo_geometry(2001,null,
sdo_point_type(round(dbms_random.value(1,10000),3),
round(dbms_random.value(1,10000),3),
NULL),NULL,NULL) as pointGeom
FROM dual
CONNECT BY LEVEL < 13;
-- Result
table LINEPOINTS created.
This is what linepoints contains:
select * from linepoints;
-- Results
FEATURE_ID POINT_ID POINTGEOM
---------- -------- ------------------------------------------------------------------------------------
398639     1        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(512.505,9909.19,NULL),NULL,NULL)
398639     2        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(701.488,8150.579,NULL),NULL,NULL)
398639     3        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(753.581,2449.058,NULL),NULL,NULL)
398639     4        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7160.47,6238.475,NULL),NULL,NULL)
398639     5        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4550.681,1422.242,NULL),NULL,NULL)
398639     6        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1972.044,6790.043,NULL),NULL,NULL)
2215595    7        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1204.709,1973.255,NULL),NULL,NULL)
2215595    8        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7485.635,2439.01,NULL),NULL,NULL)
2215595    9        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(5005.396,5022.274,NULL),NULL,NULL)
2215595    10       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1180.856,6256.96,NULL),NULL,NULL)
2215595    11       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(6425.442,7360.927,NULL),NULL,NULL)
2215595    12       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4200.348,5369.366,NULL),NULL,NULL)

12 rows selected
Secondly, given this table linepoints here is how I would create the linear (2002) sdo_geometry objects:
SELECT c.feature_id,
mdsys.sdo_geometry(2002,NULL,NULL,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET(SELECT b.COLUMN_VALUE FROM linepoints a,table(mdsys.sdo_ordinate_array(a.pointGeom.sdo_point.x,a.pointGeom.sdo_point.y)) b where a.feature_id = c.feature_id order by a.point_id) as mdsys.sdo_ordinate_array))
as geom
FROM linepoints c
GROUP BY c.feature_id;
-- Results
FEATURE_ID GEOM
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2215595    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
398639     MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
If this is accurate and gives you the result you want, please mark my answer as being correct.

regard
Simon
• ###### 7. Re: Line Constructor
Hi Simon,

Thanks a lot for the help..I am marking this as correct answer. I declared a cursor by using your SQL and generated line geometry.

(1) In your SQL, are you casting SDO_POINT as SDO_ORDINATE_ARRAY ??

(2) Can I use sdo_util.getvertices and convert as SDO_ORDINATE_ARRAY ??

thanks
syed
• ###### 8. Re: Line Constructor
Syed,
(1) In your SQL, are you casting SDO_POINT as SDO_ORDINATE_ARRAY ??
No, I am casting the X and Y ordinates of the SDO_POINT type as an SDO_ORDINATE_ARRAY so that the CAST(COLLECT()) operates on SDO_ORDINATE_ARRAYs and "appends" them together.

If you look at my earlier posting I don't use the sdo_geometry's sdo_point type rather I just use two X and Y columns from a CTW table.
(2) Can I use sdo_util.getvertices and convert as SDO_ORDINATE_ARRAY ??
Yes, of course you can. Here is the last SELECT in my answer redone...
SELECT c.feature_id,
mdsys.sdo_geometry(2002,NULL,NULL,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET(SELECT b.COLUMN_VALUE
FROM linepoints a,
table(sdo_util.getVertices(a.pointGeom)) v,
table(mdsys.sdo_ordinate_array(v.x,v.y)) b
WHERE a.feature_id = c.feature_id
ORDER BY a.point_id)
as mdsys.sdo_ordinate_array))
as geom
FROM linepoints c
GROUP BY c.feature_id;
-- Results
FEATURE_ID GEOM
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2215595    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
398639     MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
HTH

regards
S