Square Buffer

I was asked the other day how to create a square buffer around a linestring (or set of linestrings).

 select sdo_geom.validate_geometry(c.geom,0.005) as validPoly, c.geom
   from (select sdo_geometry(2003,null,null,sdo_elem_info_array(1,1003,1),b.geom.sdo_ordinates) as geom
           from (select sdo_util.append(geom,
                                         sdo_geometry(2001,null,sdo_point_type(t.x,t.y,null),null,null)) as geom
                   from (select sdo_util.append(sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(10,0,10,100)),
                                                 sdo_util.reverse_linestring(
                                                 sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(-10,0,-10,100)))) 
                                as geom
                           from dual ) a,
                        table(sdo_util.getVertices(a.geom)) t
                  where t.id = 1
                 ) b
       ) c;

If you used my parallel function, then everything could be achieved as follows:

 select sdo_geom.validate_geometry(c.geom,0.005) as validPoly, c.geom
   from (select sdo_geometry(2003,null,null,sdo_elem_info_array(1,1003,1),b.geom.sdo_ordinates) as geom
           from (select mdsys.sdo_util.append(geom,
                                               sdo_geometry(2001,null,sdo_point_type(t.x,t.y,null),null,null)) as geom
                   from (select sdo_util.append(codesys.geom.parallel(a.geom,10,0.05),
                                                 mdsys.sdo_util.reverse_linestring(
                                                 codesys.geom.parallel(a.geom,-10,0.05))) 
                                  as geom
                           from my_linestrings l
                        ) a,
                        table(mdsys.sdo_util.getVertices(a.geom)) t
                  where t.id = 1
                 ) b
       ) c;

Putting a PL/SQL function wrapper around this is trivial.

I hope this helps someone.

Leave a Reply

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