Go to content Go to navigation and search


Free Software Download

Download Code, Packages and Installers

Object Documentation

Package Documentation

Spatial Companion For Oracle (SC4O)


    New versions of LINEAR, CENTROID and GEOM etc packages
    TESSELATE and CENTROID package updates
    Applying and Extending Oracle Spatial: Source Code Problem
    CENTROID package use with ESRI's sde.st_geometry
    Changes to CENTROID and TESSELATE packages
    CENTROID Package now returns centroid of multi-linestring
    New versions of PL/SQL packages uploaded
    New Release of Free PL/SQL Packages
    New version of PL/SQL packages released
    New Version PL/SQL Spatial packages released
    New PL/SQL Package Upload
    PL/SQL Spatial Types and Packages
    Update to PL/SQL Packages
    New PL/SQL Packages
    VB6 Oracle Spatial or Locator OO4O code
    Spatial PL/SQL Packages by Example
    Catalog Registry Tool
    Image Catalog Tool
    Oracle Spatial PL/SQL and VB6 Source Code



RSS / Atom

Email me


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.

CENTROID package use with ESRI's sde.st_geometry

Thursday November 24 2011 at 01:46

Keywordscentroid 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:

  1. SELECT sde.st_point(a.cpoint.sdo_point.x, a.cpoint.sdo_point.y, a.srid) AS centroid
  2.    FROM (SELECT codesys.CENTROID.sdo_centroid(SDO_GEOMETRY(sde.st_asbinary(m.geometry),sde.st_srid(m.geometry) ),
  3.                                               0.005,1,1) AS cpoint,
  4.                 sde.st_srid(m.geometry) AS srid
  5.            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….

  1.   FUNCTION ST_RemovePoint(p_geometry  IN MDSYS.ST_Geometry,
  2.                           p_position  IN NUMBER)
  3.     RETURN MDSYS.ST_Geometry
  4.   IS
  5.   BEGIN
  7.                     SDO_RemovePoint( p_geometry.GET_SDO_GEOM(),
  8.                                      p_position ));
  9.   END ST_RemovePoint;

So, for sdo_centroid this might look like:

  1.   FUNCTION ST_Centroid(
  2.     p_geometry     IN sde.ST_GEOMETRY,
  3.     p_tolerance    IN NUMBER,
  4.     p_area         IN NUMBER := 1,
  5.     p_line_explode IN NUMBER := 1)
  7.   IS
  8.     v_centroid mdsys.sdo_geometry;
  9.   BEGIN
  10.     IF ( p_geometry IS NULL ) THEN
  11.        RETURN NULL;
  12.     END IF;
  13.     v_centroid := Do_Centroid(MDSYS.SDO_GEOMETRY(sde.st_asbinary(p_geometry),sde.st_srid(p_geometry),
  14.                               p_dimarray,p_area,p_line_explode);
  15.     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;
  16.     EXCEPTION
  17.        WHEN OTHERS THEN
  18.             RETURN NULL:
  19.   END ST_Centroid;

In summary:

1. Install packages as is and call them via conversions to/from sdo_geometry within the calling SQL.
2. Modify the existing packages adding in overloaded functions for sde.st_geometry as shown in the ST_Centroid function above.
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.
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.

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]

Wow! I hadn’t thought of this.

“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
…and, in fact, anytime you want to use one of my functions just do the same

This is a game changer for me. I’m actually not using your CENTROID package right
now. I’m just starting out with spatial databases, using sde.st_geometry, which
of course doesn’t have much in the way of linear referencing functions. Converting
my sde.st_geometry object to WKB, and then passing that to SDO_GEOMETRY will, I
think, turn it into a valid SDO_GEOMETRY object. Then I can use Oracle’s SDO_LRS
functions to do LRS operations.

Again, just starting out/learning. This will be a great help. Thanks again.

— Henry · 9 September 2016, 01:49 · #