ST_Densify for Object PLSQL

The Java Topology Suite (JTS) has a linestring densifier function that is available in my Spatial Companion for Oracle (SC4O) solution. See here for documentation.

For those who aren’t able to install SC4O (some DBAs don’t like it), there was a PL/SQL function in my the old GEOM Package implemetation on this website that allows for (multi)linestrings and (multi)polygons rings to have their coordinates “densified.

On Saturday I re-wrote that PLSQL version and added it to my new PL/SQL object based code.

   Member FUNCTION ST_Densify(p_distance IN NUMBER,
                              p_unit     IN Varchar2 DEFAULT NULL)
            RETURN T_GEOMETRY deterministic

The single page documentation of my object code is avialable here

Here is an example of how to use it.

    -- Simple Straight line.
   SELECT t_geometry(
            sdo_geometry(2002,NULL,NULL,
                         sdo_elem_info_array(1,2,1),
                         sdo_ordinate_array(100,100,900,900.0)),
            0.005,2,1)
            .ST_Densify(p_distance=>125.0,
                        p_unit=>NULL)
            .ST_Round(3,3,2,1)
            .geom AS geom
     FROM dual;
   GEOM
   -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(188.889,188.889,277.778,277.778,366.667,366.667,455.556,455.556,544.444,544.444,633.333,633.333,722.222,722.222,811.111,811.111))
   -- Simple Linestring with Z
   SELECT t_geometry(
            sdo_geometry(3002,NULL,NULL,
                         sdo_elem_info_array(1,2,1),
                         sdo_ordinate_array(100,100,1.0, 900,900.0,9.0)),
            0.005,2,1)
            .ST_Densify(p_distance=>125.0,
                        p_unit=>NULL)
            .ST_Round(3,3,2,1)
            .geom AS geom
     FROM dual;
   GEOM
   -------------------------------------------------------------------------------------------------------------------------------
   SDO_GEOMETRY(3002,NULL,NULL,
                SDO_ELEM_INFO_ARRAY(1,2,1),
                SDO_ORDINATE_ARRAY(100,100,1,188.889,188.889,1.89,277.778,277.778,2.78,366.667,366.667,3.67,455.556,455.556,4.56,
                                   544.444,544.444,5.44,633.333,633.333,6.33,722.222,722.222,7.22,811.111,811.111,8.11,900,900,9))
   -- Simple LineString with Z and Measures
   SELECT t_geometry(
            sdo_geometry(4402,NULL,NULL,
                         sdo_elem_info_array(1,2,1),
                         sdo_ordinate_array(100,100,-4.56,0.99, 900,900.0,-6.73,1131.2)),
            0.005,2,1)
            .ST_Densify(p_distance=>125.0,
                        p_unit=>NULL)
            .ST_Round(3,3,2,1)
            .geom AS geom
     FROM dual;
   GEOM
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------
   SDO_GEOMETRY(4402,NULL,NULL,
                SDO_ELEM_INFO_ARRAY(1,2,1),
                SDO_ORDINATE_ARRAY(100,100,-4.56,-4.6,188.889,188.889,-4.8,-4.8,277.778,277.778,-5.04,-5,366.667,366.667,-5.28,-5.3,455.556,455.556,-5.52,-5.5,
                                   544.444,544.444,-5.77,-5.8,633.333,633.333,-6.01,-6,722.222,722.222,-6.25,-6.2,811.111,811.111,-6.49,-6.5,900,900,-6.73,1131.2))
 WITH DATA AS (
   SELECT t_geometry(
            SDO_GEOMETRY(2002,NULL,NULL,
                         SDO_ELEM_INFO_ARRAY(1,2,1),
                         SDO_ORDINATE_ARRAY(
                           1100.765,964.286, 1161.99,739.796, 963.01,596.939, 677.296,775.51,
                           460.459,880.102, 253.827,793.367, 174.745,630.102, 228.316,497.449,
                           455.357,528.061, 718.112,446.429, 713.01,290.816, 598.214,125.0,
                           373.724,81.633, 67.602,267.857)),
            0.05,2,1)
             AS "Original Geometry"
   FROM dual
 )
 SELECT a."Original Geometry".ST_Densify(p_distance=>25.0).ST_Round(2).geom AS "Densified Geometry" FROM DATA a;
 Densified Geometry
 --------------------------------------------------------------------------------------------------------------------------------------------------------------
 SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1100.77,964.29,1107.57,939.34,1114.37,914.4,1121.17,889.46,1127.98,864.51,
              1134.78,839.57,1141.58,814.63,1148.38,789.68,1155.19,764.74,1161.99,739.8,1139.88,723.92,1117.77,708.05,1095.66,692.18,1073.55,676.3
              1051.45,660.43,1029.34,644.56,1007.23,628.69,985.12,612.81,963.01,596.94,941.03,610.68,919.05,624.41,897.08,638.15,875.1,651.88,853.12,
               665.62,831.14,679.36,809.16,693.09,787.19,706.83,765.21,720.57,743.23,734.3,721.25,748.04,699.27,761.77,677.3,775.51,653.2,787.13,629.11,
               798.75,605.02,810.37,580.92,822,556.83,833.62,532.74,845.24,508.65,856.86,484.55,868.48,460.46,880.1,434.63,869.26,408.8,858.42,382.97,
               847.58,357.14,836.73,331.31,825.89,305.49,815.05,279.66,804.21,253.83,793.37,242.53,770.04,231.23,746.72,219.93,723.4,208.64,700.07,197.34,
               676.75,186.04,653.43,174.75,630.1,185.46,603.57,196.17,577.04,206.89,550.51,217.6,523.98,228.32,497.45,253.54,500.85,278.77,504.25,304,507.65,
               329.22,511.05,354.45,514.46,379.68,517.86,404.9,521.26,430.13,524.66,455.36,528.06,479.24,520.64,503.13,513.22,527.02,505.8,550.9,498.38,574.79,
               490.96,598.68,483.53,622.56,476.11,646.45,468.69,670.34,461.27,694.23,453.85,718.11,446.43,717.26,420.49,716.41,394.56,715.56,368.62,714.71,
               342.69,713.86,316.75,713.01,290.82,698.66,270.09,684.31,249.36,669.96,228.64,655.61,207.91,641.26,187.18,626.91,166.45,612.56,145.73,598.21,125,
               573.27,120.18,548.33,115.36,523.38,110.54,498.44,105.73,473.5,100.91,448.55,96.09,423.61,91.27,398.67,86.45,373.72,81.63,351.86,94.93,329.99,
               108.24,308.13,121.54,286.26,134.84,264.39,148.14,242.53,161.44,220.66,174.75,198.8,188.05,176.93,201.35,155.07,214.65,133.2,227.95,111.33,241.25,
               89.47,254.56,67.6,267.86))

The last example looks like this:

Before

Densify Before

After

Densify After

PS There is a version of this for SQL Server Spatial (with MySQL to come).

I hope this function are of interest to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *