CENTROID package use with ESRI’s sde.st_geometry

I have been emailed by someone who is using ESRI’s sde.st_geometry in preference to Oracle’s SDO_GEOMETRY or ST_GEOMETRY for their spatial data storage in Oracle, wanting a version of my CENTROID pl/sql package that would allow for its use with ESRI’s spatial data type.

This is what I was asked:

First off love your blog, just discovered it and can’t wait to read through your stuff. The centroid package caught my eye and I was about to dig in but I noticed in the package header that it’s set up for SDO_GEOMETRY. We’re a regional govt in XXXXXXX, XX and we’re just now migrating to ESRI’s ST_Geometry from old SDELOB format. Any thoughts on the complexity of my converting the centroid package to use ST_GEOMETRY?

This is how I answered (all comments about migrating to SDO_GEOMETRY rather than ESRI’s ST_GEOMETRY have been removed).

Thank you for the king words as very, very few people ever let me know if any of the stuff I post or create is of use. I have lots of articles in draft and not exposed because it is hard to keep up the “desire” when confronted with silence.

All my development of Oracle/SQL Server/PostgreSQL-PostGIS is done with freely downloadable versions of those databases from their vendors. This is not possible with ESRI.

But here is a suggested way forward.

Given that SDE.ST_GEOMETRY supports WKB and that every Oracle database has Locator (ie SDO_GEOMETRY – ESRI can’t remove it from the Oracle database), why not install my CENTROID package and then call the relevant function with SQL like this:

 SELECT sde.st_point(a.cpoint.sdo_point.x, a.cpoint.sdo_point.y, a.srid) AS centroid
    FROM (SELECT codesys.CENTROID.sdo_centroid(SDO_GEOMETRY(sde.st_asbinary(m.geometry),sde.st_srid(m.geometry) ),
                                                0.005,1,1) AS cpoint,
                 sde.st_srid(m.geometry) AS srid
            FROM myTable m) a;

In short, this will work. And, in fact, anytime you want to use one of my functions just do the same conversion.

If you did this as part of a trigger to synchronise a polygon/line and its centroid via storing the centroid in its own column, or via a materialized view, this would not be so painful.

But if you were constantly finding yourself using my functions in native SQL it will become tedious.

So, you could, of course, modify any of my packages adding in an overloaded function call that internally did the conversion behind the scenes as some of the Oracle ST_Geometry functions in the GEOM package show….

   FUNCTION ST_RemovePoint(p_geometry  IN MDSYS.ST_Geometry,
                           p_position  IN NUMBER)
     RETURN MDSYS.ST_Geometry
   IS
   BEGIN
     RETURN MDSYS.ST_Geometry.FROM_SDO_GEOM(
                     SDO_RemovePoint( p_geometry.GET_SDO_GEOM(),
                                      p_position ));
   END ST_RemovePoint;

So, for sdo_centroid this might look like:

   FUNCTION ST_Centroid(
     p_geometry     IN sde.ST_GEOMETRY,
     p_tolerance    IN NUMBER,
     p_area         IN NUMBER := 1,
     p_line_explode IN NUMBER := 1)
     RETURN sde.ST_GEOMETRY
   IS
     v_centroid mdsys.sdo_geometry;
   BEGIN
     IF ( p_geometry IS NULL ) THEN
        RETURN NULL;
     END IF;
     v_centroid := Do_Centroid(MDSYS.SDO_GEOMETRY(sde.st_asbinary(p_geometry),sde.st_srid(p_geometry),
                               p_dimarray,p_area,p_line_explode);
     RETURN CASE WHEN v_centroid IS NULL THEN NULL ELSE sde.st_point(v_centroid.sdo_point.x,centroid.sdo_point.y, sde.ST_Srid(p_geometry)) END;
     EXCEPTION
        WHEN OTHERS THEN
             RETURN NULL:
   END ST_Centroid;

In summary:

1. Install packages as is and call them via conversions to/from sdo_geometry within the calling SQL.
or
2. Modify the existing packages adding in overloaded functions for sde.st_geometry as shown in the ST_Centroid function above.
or
3. Re-write existing packages that you wish to use (don’t have to do all) so that they only use sde.ST_Geometry API (same/similar to SQL Server/Oracle ST_GEOMETRY/PostgreSQL – though looking at the sde.ST_GEOMETRY API the implementation is akin to PostgreSQL and not SQL/Oracle viz “dot” notation. I cannot do this for you because I do not have access to sde.ST_GEOMETRY. I could do it if you could provide me with VPN access to your database after accepting a quote to re-write those functions you require.
or
4. Consider going back to SDO_GEOMETRY as this gives you better Oracle integration, allows all your existing ESRI technology to continue to work and allows you to use any other commercial GIS vendor or FOSS4G software product (like my SQL Developer GeoRaptor) to solve your business problems. (Also gives you access to cheaper IT and GIS consultants).

Still, here are lots of excellent suggestions that will work for you. I’ve done all the hard work, implementing wrappers over that work is trivial.

I hope this is helpful to others in the same situation.