Creating linestrings from points
UPDATED: Fix in Order By and addition of second example.
Here are some examples of how to construct linestrings from points stored in tables in two different formats.
XYZ Values in SDO_POINT of SDO_GEOMETRY object
The following example creates SDO_GEOMETRY point objects (2D) and stores them in a table linepoints. These points described individual features (identified by the column feature_id). The points are correctly ordered by the point_id column.
DROP TABLE linepoints; -- Result TABLE LINEPOINTS dropped. 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(10000,19999),3), round(dbms_random.VALUE(20000,29999),3), NULL),NULL,NULL) AS pointGeom FROM dual CONNECT BY LEVEL < 13; -- Result TABLE LINEPOINTS created. -- Select one feature's worth... SELECT a.feature_id, a.point_id, a.pointGeom FROM linepoints a WHERE a.feature_id = 398639; -- Results FEATURE_ID POINT_ID POINTGEOM ---------- -------- -------------------------------------------------------------------------- 398639 1 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(19674.833,22929.276,NULL),NULL,NULL) 398639 2 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15015.917,26297.82,NULL),NULL,NULL) 398639 3 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15917.343,28141.968,NULL),NULL,NULL) 398639 4 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16322.858,20066.442,NULL),NULL,NULL) 398639 5 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14752.7,26373.809,NULL),NULL,NULL) 398639 6 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16154.323,25280.283,NULL),NULL,NULL) . 6 ROWS selected
Secondly, given this table linepoints here is how I would create linear (2002) sdo_geometry objects:
SELECT feature_id, substr(sdo_geom.validate_geometry(linestring,0.005),1,5) AS vLine, linestring FROM (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, rownum) AS mdsys.sdo_ordinate_array)) AS linestring FROM linepoints c GROUP BY c.feature_id ORDER BY c.feature_id ) f; -- Results FEATURE_ID VLINE LINESTRING ---------- ----- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 398639 TRUE SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(11381.777,29179.921,15533.654,22883.293,14257.442,22215.899,18098.61,29694.596,19875.69,29429.811,16936.329,23639.846)) 2215595 TRUE SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(19300.099,21279.124,14874.566,25400.551,11572.547,23158.262,15915.272,21011.269,19665.059,22354.97,16725.616,28165.866))
A good result.
Note that the ORDER BY a.point_id, rownum clause in the CAST(MULTISET(SELECT… are needed to ensure correct ordering of the points and their X,Y values when being collected into the SDO_ORDINATE_ARRAY of the resultant linestring.
XYZ Values in Individual Columns
The following example assumes the ordinate data are in separate columns in a table rather than in the SDO_POINT_TYPE SDO_GEOMETRY structure.
Firstly let’s create some test data.
DROP TABLE line_points; -- Result TABLE LINE_POINTS dropped. CREATE TABLE line_points AS SELECT trunc(level/5)+1 AS feature_id, ROW_NUMBER() OVER (partition BY 1 ORDER BY rownum) AS point_id, round(dbms_random.VALUE(10000,20000),3) AS x, round(dbms_random.VALUE(30000,40000),3) AS y, round(dbms_random.VALUE(-100,100),1) AS z FROM dual CONNECT BY LEVEL < 100; -- Result TABLE LINE_POINTS created. -- Select one feature's worth... SELECT a.feature_id, a.point_id, a.x, a.y, a.z FROM line_points a WHERE a.feature_id = 1; -- Result FEATURE_ID POINT_ID X Y Z ---------- -------- --------- --------- ----- 1 1 14245.355 39103.198 -56.4 1 2 13611.439 39075.327 57.2 1 3 19677.775 32157.407 91.2 1 4 19148.487 39934.174 -7.8
Now let’s build the 3D linestring.
SELECT feature_id, substr(sdo_geom.validate_geometry(linestring,0.005),1,10) AS vLine, linestring FROM (SELECT a.feature_id, mdsys.sdo_geometry(3002,NULL,NULL, mdsys.sdo_elem_info_array(1,2,1), CAST(MULTISET(SELECT b.COLUMN_VALUE FROM line_points b, TABLE(mdsys.sdo_ordinate_array(b.x,b.y,b.z)) b WHERE b.feature_id = a.feature_id ORDER BY b.point_id, rownum) AS mdsys.sdo_ordinate_array)) AS LineString FROM line_points a GROUP BY a.feature_id ORDER BY a.feature_id ) f; -- Result FEATURE_ID VLINE LINESTRING ---------- ----- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10834.967,30240.895,-15.2,11277.951,39088.68,-43.2,12186.848,38194.666,-83.7,16531.394,34999.423,38.7)) 2 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12762.782,36113.949,84.6,10840.308,36369.348,64.1,18131.782,30854.663,-62.3,16727.793,39623.237,83.7,17867.121,34401.128,-86.3)) 3 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(17002.407,39531.838,-27.1,15454.69,33234.52,84.9,14253.969,33251.42,-52.4,19832.06,35341.975,-66.8,15774.957,35213.841,-55.5)) 4 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12469.867,36990.592,-40.8,15568.675,33472.239,-32.8,10684.001,33537.21,0.2,19862.667,37460.655,90,12262.616,38702.088,-7.2)) 5 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14928.194,38280.358,72.4,16483.747,37641.538,-71.4,10060.866,30785.363,65,19311.863,32594.842,-70.9,10887.833,36615.265,-68.3)) 6 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10430.269,30601.649,56.9,14843.597,32296.468,-38.1,12545.397,38551.122,58.8,15699.728,36865.074,39.8,13140.555,37717.306,-61.3)) 7 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13887.14,39182.163,-69.4,10998.301,36315.807,-40.3,13088.562,35835.823,20,10476.982,31781.73,91.7,14414.514,36456.508,-98.2)) 8 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14945.188,31114.27,-17.2,12857.532,34706.264,95.1,12593.085,34808.013,45.1,12580.36,30627.921,2.9,15613.011,39624.302,7.2)) 9 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13365.794,33948.006,99.4,19782.622,30310.164,78.2,14439.438,33976.395,-51,17582.394,30907.25,-52.3,17430.688,39258.111,75.1)) 10 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13182.98,37490.586,93.3,12077.195,38557.26,-68.6,16749.617,35733.641,-49.5,19189.818,36467.5,96.2,16677.211,36611.312,18.8)) 11 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(18342.358,36454.356,95.7,15966.729,30259.604,-58.2,18496.083,38853.952,-91.3,12538.593,32527.582,-59.6,16522.237,30288.221,-20.7)) 12 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(18510.1,35776.065,-54,16918.603,34657.555,83.7,16716.77,32362.628,7.2,17884.163,32555.611,-91.7,19777.609,36459.833,38.4)) 13 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14850.759,32781.795,-91.4,12234.918,33549.123,-76.7,14475.272,35926.771,62.2,19185.88,35721.572,-99.6,11686.912,38599.432,76.9)) 14 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(15535.712,35259.717,-69.8,14823.918,36108.42,-77.5,18995.265,33393.98,79.5,11298.053,36226.521,80.2,10199.336,35744.338,34.4)) 15 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(19013.37,33346.841,-67.3,10863.654,34773.51,21.5,13188.374,30997.509,24.3,12076.438,37695.98,-70,12316.767,38605.515,-34.9)) 16 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(18030.662,32252.482,52.4,16867.649,32708.082,73.3,11540.562,38772.874,97.1,15895.416,36148.811,80.2,19440.718,32434.254,85.9)) 17 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(17886.291,33488.643,85.5,19747.81,30049.534,-90.1,16094.872,34875.287,91.9,15060.472,37612.15,-40.9,13007.343,37140.685,-5.7)) 18 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14382.043,37025.927,12.9,16596.284,38165.701,78.7,17673.999,31739.898,-9.7,16573.251,34908.787,89.9,10644.063,30304.372,44.2)) 19 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(15370.166,31074.666,-10.3,15488.603,31824.248,54.9,11992.719,38411.639,-30.4,12853.929,33091.134,97.3,10578.041,39874.991,97)) 20 TRUE SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13377.967,35922.605,16,15500.276,32491.701,-13.7,17942.919,37114.717,-16.5,13810.673,34459.542,67.9,18769.264,30391.873,-92.9)) -- 20 ROWS selected
Again, note that the ORDER BY a.point_id, rownum clause in the CAST(MULTISET(SELECT… are needed to ensure correct ordering of the points and their X,Y values when being collected into the SDO_ORDINATE_ARRAY of the resultant linestring.
Both these methods are simple, direct and effective. And, in my view, superior to writing PL/SQL.
I hope this is of use to someone.
Jose Sanchez
The following is my solution to Jose’s example.
WITH line_points AS ( SELECT 1 AS feature_id, 1 AS point_id,470532.39 AS X,145290.99 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 2 AS point_id,470532.39 AS X,145290.99 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 3 AS point_id,470532.38 AS X,145291.02 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 4 AS point_id,470532.38 AS X,145291.06 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 5 AS point_id,470532.43 AS X,145291.14 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 6 AS point_id,470532.51 AS X,145291.20 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 7 AS point_id,470532.59 AS X,145291.18 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 8 AS point_id,470532.67 AS X,145291.14 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 9 AS point_id,470532.68 AS X,145291.10 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 10 AS point_id,470532.68 AS X,145291.07 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 11 AS point_id,470532.56 AS X,145291.04 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 12 AS point_id,470531.41 AS X,145291.25 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 13 AS point_id,470238.55 AS X,145400.22 AS Y FROM dual UNION ALL SELECT 1 AS feature_id, 14 AS point_id,470014.12 AS X,145887.29 AS Y FROM dual ) SELECT feature_id, substr(sdo_geom.validate_geometry_with_context(linestring,0.005),1,10) AS vLine, linestring FROM (SELECT a.feature_id, sdo_util.Remove_Duplicate_Vertices( mdsys.sdo_geometry(2002,27700,NULL, mdsys.sdo_elem_info_array(1,2,1), CAST(MULTISET(SELECT b.COLUMN_VALUE FROM line_points b, TABLE(mdsys.sdo_ordinate_array(b.x,b.y)) b WHERE b.feature_id = a.feature_id ORDER BY b.point_id) AS mdsys.sdo_ordinate_array)), 0.005) AS LineString FROM line_points a GROUP BY a.feature_id ORDER BY a.feature_id ) f; -- Result FEATURE_ID VLINE LINESTRING ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 TRUE SDO_GEOMETRY(2002,27700,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(470532.39,145290.99,470532.38,145291.02,470532.38,145291.06,470532.43,145291.14,470532.51,145291.2,470532.59,145291.18,470532.67,145291.14,470532.68,145291.1,470532.68,145291.07,470532.56,145291.04,470531.41,145291.25,470238.55,145400.22,470014.12,145887.29))
Hopefully, that solves your problem.