Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Creating linestrings (2002) from points (2001)

Saturday May 12 2012 at 10:00

Keywordscast collect points linestrings group
Summary

There are many methods for creating 2D linestrings from 2D points. Here are two of many.

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.

  1. DROP   TABLE linepoints;
  2. -- Result
  3. TABLE LINEPOINTS dropped.
  4. CREATE TABLE linepoints AS
  5. SELECT CASE WHEN trunc(level / 7) = 0 THEN 398639 ELSE 2215595 END AS feature_id,
  6.        ROW_NUMBER() OVER (partition BY 1 ORDER BY rownum) AS point_id,
  7.        sdo_geometry(2001,NULL,
  8.                     sdo_point_type(round(dbms_random.VALUE(10000,19999),3),
  9.                                    round(dbms_random.VALUE(20000,29999),3),
  10.                                    NULL),NULL,NULL) AS pointGeom
  11.   FROM dual
  12. CONNECT BY LEVEL < 13;
  13. -- Result
  14. TABLE LINEPOINTS created.
  15. -- Select one feature's worth...
  16. SELECT a.feature_id, a.point_id, a.pointGeom FROM linepoints a WHERE a.feature_id = 398639;
  17. -- Results
  18. FEATURE_ID POINT_ID POINTGEOM
  19. ---------- -------- --------------------------------------------------------------------------
  20. 398639     1        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(19674.833,22929.276,NULL),NULL,NULL)
  21. 398639     2        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15015.917,26297.82,NULL),NULL,NULL)
  22. 398639     3        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15917.343,28141.968,NULL),NULL,NULL)
  23. 398639     4        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16322.858,20066.442,NULL),NULL,NULL)
  24. 398639     5        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14752.7,26373.809,NULL),NULL,NULL)
  25. 398639     6        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16154.323,25280.283,NULL),NULL,NULL)
  26. .
  27.  6 ROWS selected

Secondly, given this table linepoints here is how I would create linear (2002) sdo_geometry objects:

  1. SELECT c.feature_id,
  2. SELECT c.feature_id,
  3.        mdsys.sdo_geometry(2002,NULL,NULL,
  4.                           mdsys.sdo_elem_info_array(1,2,1),
  5.                           CAST(MULTISET(SELECT b.COLUMN_VALUE
  6.                                           FROM linepoints a,
  7.                                                TABLE(mdsys.sdo_ordinate_array(a.pointGeom.sdo_point.x,
  8.                                                                               a.pointGeom.sdo_point.y)) b
  9.                                          WHERE a.feature_id = c.feature_id
  10.                                          ORDER BY a.point_id, rownum)
  11.                           AS mdsys.sdo_ordinate_array)) AS linestring
  12.   FROM linepoints c
  13.  GROUP BY c.feature_id
  14.  ORDER BY c.feature_id;
  15. -- Results
  16. FEATURE_ID LINESTRING
  17. ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  18. 398639     MSDO_GEOMETRY(2002,NULL,NULL,MSDO_ELEM_INFO_ARRAY(1,2,1),MSDO_ORDINATE_ARRAY(19674.833,22929.276,15015.917,26297.82,15917.343,28141.968,16322.858,20066.442,14752.7,26373.809,16154.323,25280.283))
  19. 2215595    MSDO_GEOMETRY(2002,NULL,NULL,MSDO_ELEM_INFO_ARRAY(1,2,1),MSDO_ORDINATE_ARRAY(13770.829,22972.616,17933.023,24110.378,11915.447,23018.84,12885.649,22338.293,13045.379,27735.156,12351.304,20813.077))

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.

  1. DROP TABLE line_points;
  2. -- Result
  3. TABLE LINE_POINTS dropped.
  4. CREATE TABLE line_points AS
  5. SELECT trunc(level/5)+1 AS feature_id,
  6.        ROW_NUMBER() OVER (partition BY 1 ORDER BY rownum) AS point_id,
  7.        round(dbms_random.VALUE(10000,20000),3) AS x,
  8.        round(dbms_random.VALUE(30000,40000),3) AS y,
  9.        round(dbms_random.VALUE(-100,100),1)    AS z
  10.   FROM dual
  11. CONNECT BY LEVEL < 100;
  12. -- Result
  13. TABLE LINE_POINTS created.
  14. -- Select one feature's worth...
  15. SELECT a.feature_id, a.point_id, a.x, a.y, a.z FROM line_points a WHERE a.feature_id = 1;
  16. -- Result
  17. FEATURE_ID POINT_ID X         Y         Z
  18. ---------- -------- --------- --------- -----
  19. 1          1        14245.355 39103.198 -56.4
  20. 1          2        13611.439 39075.327 57.2
  21. 1          3        19677.775 32157.407 91.2
  22. 1          4        19148.487 39934.174 -7.8

Now let’s build the 3D linestring.

  1. SELECT a.feature_id,
  2.        mdsys.sdo_geometry(3002,NULL,NULL,
  3.                           mdsys.sdo_elem_info_array(1,2,1),
  4.                           CAST(MULTISET(SELECT b.COLUMN_VALUE
  5.                                           FROM line_points b,
  6.                                                TABLE(mdsys.sdo_ordinate_array(b.x,b.y,b.z)) b
  7.                                          WHERE b.feature_id = a.feature_id
  8.                                          ORDER BY b.point_id, rownum)
  9.                               AS mdsys.sdo_ordinate_array)) AS LineString
  10.   FROM line_points a
  11.  GROUP BY a.feature_id
  12.  ORDER BY a.feature_id;
  13. -- Result
  14. FEATURE_ID LINESTRING
  15. ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  16. 1          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14245.355,39103.198,-56.4,13611.439,39075.327,57.2,19677.775,32157.407,91.2,19148.487,39934.174,-7.8))
  17. 2          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12358.793,34077.305,77.9,17138.234,36149.378,70.9,17188.704,36270.287,-4.8,10055.773,37772.824,9.1,16795.643,36493.962,12))
  18. 3          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(15270.684,35139.185,49.7,16746.656,36558.803,81.6,18368.607,37752.839,6,13564.588,34942.459,13.8,19079.632,35174.122,91.6))
  19. 4          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(17348.488,33919.907,-64,14127.571,32994.541,2.3,14087.358,32275.018,-73.1,11810.841,38937.622,42.6,11036.497,31040.998,11.9))
  20. 5          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(17782.802,38882.122,-15.7,15346.955,36424.432,74.3,18642.434,37517.094,53.7,19909.458,35923.381,80.4,14583.489,36217.866,-73.5))
  21. 6          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(18124.973,34537.192,-96.2,16558.773,34183.343,-84.7,10569.79,30883.001,75.4,16944.721,37081.525,-18.5,14614.933,37783.153,52))
  22. 7          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(14677.927,36151.409,32.7,19514.516,38911.543,-72.7,14406.92,37722.066,-46.2,17267.663,37257.946,96.9,10820.212,38711.059,84.2))
  23. 8          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(16437.454,32212.331,36.2,11534.016,38369.614,-37.6,17897.043,31606.287,-61.5,14366.139,34727.524,19.3,18291.32,39961.888,-15.8))
  24. 9          SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(16316.012,33320.361,-26.6,14321.974,39423.974,-3.3,10386.655,38990.409,-21.2,14014.716,35392.636,-64.1,10033.483,37378.985,-42.1))
  25. 10         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(19979.771,37007.244,-38.1,15581.189,38478.737,9,17197.773,32511.977,87.8,19523.8,39044.066,-82.4,12598.608,37805.836,77.5))
  26. 11         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(15574.476,30722.932,-79.2,16359.719,31589.637,33.6,14678.212,31206.867,54,13152.338,30452.17,52.1,18607.347,31567.499,15))
  27. 12         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(16015.368,37876.814,72.3,15019.331,39947.328,-35.9,11414.786,35489.093,-50.5,13597.025,35839.811,-52.7,14547.436,36920.582,44.6))
  28. 13         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13708.355,35961.131,94.3,14982.359,30374.435,39.6,18477.057,34711.695,71.7,10595.871,33132.109,49.2,10700.3,34188.536,-99.1))
  29. 14         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(11199.738,33213.141,-92.2,18002.971,34543.131,79.8,14085.194,34013.394,-34.1,11347.297,39171.501,31.3,13406.854,30907.155,70.2))
  30. 15         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13911.73,38386.567,43.4,17414.809,38134.698,-83.5,14587.241,37084.405,25.6,14334.452,36611.239,-79.8,16072.944,35719.631,-17.3))
  31. 16         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13252.497,32614.524,74,12862.657,31599.997,-92.3,11355.821,38632.629,-81.3,10524.74,35055.652,2.7,16276.083,38389.214,-74.4))
  32. 17         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(15492.492,32388.787,-38,15753.851,38010.68,-74.7,18286.018,35287.541,-74.5,16325.429,35534.19,96.5,11397.715,34075.915,-94.7))
  33. 18         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(13127.258,37337.691,32.6,11997.164,34209.953,-84.6,16945.685,34762.675,90.8,19444.194,34436.47,-31.2,12300.63,33690.892,30.5))
  34. 19         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(12108.195,30079.733,89.5,19377.455,30088.302,-7.6,12275.73,34358.962,-79.9,15408.805,39577.927,63,18526.615,34260.371,13.5))
  35. 20         SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10645.704,34483.079,19.4,17561.894,32521.904,85.3,15904.129,33221.769,-37,10821.374,34936.686,36.5,16540.48,38054.48,-66))
  36. .
  37.  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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [1]

Hello Simon,

using your above construction of linestrings from individual points I found faulty. I do convert some 10 to hundreds of points into a single linestring, depending on the sort the x and y values get intermingled, i.e. some points in the linestring have lat and long exchanged. Any ideas ?

Kai

— Kai Jancke · 11 May 2012, 23:27 · #