There is work going on by Regina Obe to create a concave hull function for PostGIS. In addition, there has been some great work by Glenn Hudson and Matt Duckham in creating an open source Java version. And, of course, you could also use the commercial version. And, finally, while not yet in JTS, there is an underlying Alpha Shape implementation that would form the basis of any implementation.

But what about Oracle?

Well, at 11gR2, there are two new functions in the SDO_GEOM package:

(The is a third, underlying function, SDO_GEOM.SDO_ALPHA_SHAPE, but I do not cover its use in this article.)

But how do I use them?

I grabbed the sample points for the letter S from the source code distributed by Hudson and Duckham and converted them to an Oracle SDO_GEOMETRY object. In the following example, I convert them via WKT as that provides a more accessible method for understanding what is going on than creating the SDO_GEOMETRY elements directly. This SDO_GEOMETRY object is then visualised using GeoRaptor for SQL Developer.

I then computed the CONCAVEHULL and CONCAVEHULL_BOUNDARY shapes and visualised them. Here is the complete code:

VARIABLE geom varchar2(2000);
begin
  :geom := 'MULTIPOINT(
(120 -224), (185 -219), (190 -234), (200 -246), (212 -256), (227 -261), (242 -264), (257 -265), (272 -264), (287 -263),
(302 -258), (315 -250), (323 -237), (321 -222), (308 -213), (293 -208), (278 -205), (263 -202), (248 -199), (233 -196), 
(218 -193), (203 -190), (188 -185), (173 -180), (160 -172), (148 -162), (138 -150), (133 -135), (132 -120), (136 -105), 
(146  -92), (158  -82), (171  -74), (186  -69), (201  -65), (216  -62), (231  -60), (246  -60), (261  -60), (276  -60), 
(291  -61), (306  -64), (321  -67), (336  -72), (349  -80), (362  -89), (372 -101), (379 -115), (382 -130), (314 -134), 
(309 -119), (298 -108), (284 -102), (269 -100), (254  -99), (239 -100), (224 -102), (209 -107), (197 -117), (200 -132), 
(213 -140), (228 -145), (243 -148), (258 -151), (273 -154), (288 -156), (303 -159), (318 -163), (333 -167), (347 -173), 
(361 -179), (373 -189), (383 -201), (389 -215), (391 -230), (390 -245), (384 -259), (374 -271), (363 -282), (349 -289), 
(335 -295), (320 -299), (305 -302), (290 -304), (275 -305), (259 -305), (243 -305), (228 -304), (213 -302), (198 -300), 
(183 -295), (169 -289), (155 -282), (143 -272), (133 -260), (126 -246), (136 -223), (152 -222), (168 -221), (365 -131), 
(348 -132), (331 -133), (251 -177), (183 -157), (342  -98), (247  -75), (274 -174), (360 -223), (192  -85), (330 -273), 
(210 -283), (326  -97), (177 -103), (315 -188), (175 -139), (366 -250), (321 -204), (344 -232), (331 -113), (162 -129), 
(272  -77), (292 -192), (144 -244), (196 -272), (212  -89), (166 -236), (238 -167), (289 -282), (333 -187), (341 -249), 
(164 -113), (238 -283), (344 -265), (176 -248), (312 -273), (299  -85), (154 -261), (265 -287), (359 -111), (160 -150), 
(212 -169), (351 -199), (160  -98), (228  -77), (376 -224), (148 -120), (174 -272), (194 -100), (292 -173), (341 -212), 
(369 -209), (189 -258), (198 -159), (275 -190), (322  -82))';
end;
/
select sdo_geometry(:geom,NULL) as geom from DUAL
union all
select sdo_geom.sdo_concavehull(sdo_geometry(:geom,NULL),0.01) as cgeom from dual
union all
select sdo_geom.sdo_concavehull_boundary(sdo_geometry(:geom,NULL),0.01) as cgeom from DUAL;

The images that display the results are.

The S SDO_GEOMETRY Shape

The SDO_CONCAVEHULL

The SDO_CONCAVEHULL_BOUNDARY

I hope this is of use to someone.

Projecting SDO_GEOM_METADATA DIMINFO XY ordinates

· Aug 11, 03:00 pm by Simon Greener

Just had need to turn project only the 2D (X/Y) ordinates of a DIMINFO value from USER_SDO_GEOM_METADATA into a project different from its current value.

The original values are:

SELECT b.srid,b.minx,b.miny,b.maxx,b.maxy
  FROM (SELECT a.rin, a.srid,
               case when a.rin = 1 then a.sdo_lb else null end as minx,
               case when a.rin = 1 then LEAD(a.sdo_lb,1) OVER (ORDER BY a.RIN) else null end as miny,
               case when a.rin = 1 then a.sdo_ub else null end as maxx,
               case when a.rin = 1 then LEAD(a.sdo_ub,1) OVER (ORDER BY a.RIN) else null end as maxy
         FROM ( SELECT rownum as rin, asgm.srid, dim.*
                  FROM all_sdo_geom_metadata asgm,
                       TABLE(asgm.diminfo) dim
                 WHERE asgm.owner       = UPPER(NVL(NULL,SYS_CONTEXT('USERENV','SESSION_USER'))) 
                   AND asgm.table_name  = UPPER('WW_LINE') 
                   AND asgm.column_name = UPPER('GEOM') ) a
        ) b
 WHERE b.rin = 1;
 
SRID  MINX   MINY    MAXX   MAXY
----- ------ ------- ------ -------
82469 325000 6370000 498600 6550000

Here’s the SQL I used to do it:

SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.GEOM,1) as minx,
       SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.GEOM,2) as miny,
       SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.GEOM,1) as minx,
       SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.GEOM,2) as miny
  FROM (SELECT SDO_CS.TRANSFORM(SDO_GEOMETRY(2003,b.srid,null,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(b.minx,b.miny,b.maxx,b.maxy)),41014) as geom
          FROM (SELECT a.rin, a.srid,
                       case when a.rin = 1 then a.sdo_lb else null end as minx,
                       case when a.rin = 1 then LEAD(a.sdo_lb,1) OVER (ORDER BY a.RIN) else null end as miny,
                       case when a.rin = 1 then a.sdo_ub else null end as maxx,
                       case when a.rin = 1 then LEAD(a.sdo_ub,1) OVER (ORDER BY a.RIN) else null end as maxy
                 FROM ( SELECT rownum as rin, asgm.srid, dim.*
                          FROM all_sdo_geom_metadata asgm,
                               TABLE(asgm.diminfo) dim
                         WHERE asgm.owner       = UPPER(NVL(NULL,SYS_CONTEXT('USERENV','SESSION_USER'))) 
                           AND asgm.table_name  = UPPER('WW_LINE') 
                           AND asgm.column_name = UPPER('GEOM') ) a
               ) b
         WHERE b.rin = 1
      ) c;
 
MINX             MINY            MINX             MINY
---------------- --------------- ---------------- ----------------
17341585.1098337 13841876.460748 17889863.1143863 14380561.3860746

Hope that helps someone out there.

(A lot of these ideas is being wrapped into GeoRaptor. Download and try it.)

Instantiating MDSYS.VERTEX_TYPE

· Jun 17, 08:03 pm by Simon Greener

Recently I was contacted about the fact that my free PL/SQL packages could not compile on 11gR2 due to changes in the MDSYS.VERTEX_TYPE structure.

This structure has given me lots of grief over the years. At 9i the structure only had four elements: X, Y, Z and W. But at 10g, the ID element was added. Now, at 11g the structure looks like this:

Create type vertex_type as object
(X  number,
 Y  number,
 Z  number,
 W  number,
 v5 number,
 v6 number,
 v7 number,
 v8 number,
 v9 number,
 v10 number,
 v11 number,
 ID number );

The problem I have had over the years is how to instantiating a MDSYS.VERTEX_TYPE structure within my own PL/SQL so that I could interchange structures with Oracle functions.

The approach I initially took was to instantiate (in 10g) this way:

v_vertex mdsys.vertex_type := mdsys.vertex_type(NULL,NULL,NULL,NULL,NULL);

Problem is that it doesn’t work in 9i (one less field) or in 11g!

My initial response was to create my own, more stable, structure, but I what I don’t want to do is compete with Oracle!

The change I made in response to my customer’s problem was an idea from left-field that I wish I had had a few years ago! That is, instantiate this way:

v_vertex mdsys.vertex_type := mdsys.sdo_util.getVertices(mdsys.sdo_geometry(2001,NULL,MDSYS.SDO_POINT_TYPE(NULL,NULL,NULL),NULL,NULL);

It doesn’t matter that I have given the Oracle utility a point, it wil still return a fully specified structure!

Cool.

Now, I can let the Oracle programmers handle the problems between versions via their programming of getVertices than me having to worry about it.

Happy PL/SQL programming.

GeoRaptor Development Team

· Apr 7, 08:29 pm by Simon Greener

I have joined the GeoRaptor development team.

This team is composed of:

Holger Läbe;
Olaf Iseger;
Simon Greener;
Matic Petek (un-contactable)

So far we have been refactoring the existing code, bringing it into alignment with the latest Oracle SDO API, and ensuring it it has the closest fit to the latest SQL Developer 2.1.x codebase.

We are progressing very well.

Soon, we will make interim releases as our refactoring progresses, but we do intend to add major new functionality as soon as we can.

>>Tips and Tricks