Go to content Go to navigation and search

Home

Straws in the Wind Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

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.

Support for ESRI's ST_GEOMETRY

Wednesday March 21 2018 at 11:56

The following article results from customers downloading the free source code (eg for Oracle SDO_GEOMETRY) on this site without fully understanding that the code is not for ESRI’s ST_GEOMETRY spatial type and then requesting changes to the source code to support that foreign type.

So, to be clear, all of the source code, products and examples that are made available on this website only deal with the spatial types that are provided by the main database vendors and their products.

In summary, these are supported:

  • MySQL SPATIAL data type;
  • PostGIS’s GEOMETRY and GEOGRAPHY types for PostgreSQL;
  • SQL Server’s GEOMETRY and GEOGRAPHY types.
  • Oracle’s SDO_GEOMETRY, ST_GEOMETRY and other objects.

At no stage has this site ever attempted to provide free code or product for ESRI’s ST_GEOMETRY implementation for these databases.

The reasons I do not support ESRI’s spatial type are as follows:

  1. I cannot afford an ESRI license that would allow me to use and develop solutions and tools for it;
  2. Access to the main database vendors’ database products is covered by free development licenses.
  3. All the main vendor spatial types are already supported in the ESRI software stack;
  4. Given my computer science training and expertise in databases, I view their extensible data types as being approximate implementations of relational domains. As such, IMHO, it is the responsibility of the database vendor to provide such extensibility in their products.
  5. Hence, IMO, ESRI’s data type is, in effect, for ESRI’S software stack and users and not for universal, ubiquitous access by any other software;
  6. The current database implementations IMHO all provide a better implementation of a spatial type system, which are tightly integrated into the architecture of the host database. For example, Oracle’s and SQL Server’s spatial types are better integrated into the Oracle kernel than any external procedure-based system.
  7. Customer and technical support is split between database vendor and ESRI (blame game);

These points are my opinion based on experience and education.

However, if one wants to use some of my code then it is possible via use of the Open GIS (OGC) WKT and WKB export/import functions. One example will suffice:

  1. -- Assume shape is an SDE.ST_GEOMETRY object...
  2. SELECT rownum AS fid,
  3.         SDE.ST_GeomFromText(
  4.            CODESYS.ST_Translate(
  5.                p_geometry=> SDO_GEOMETRY(SDE.ST_AsText(a.shape),ST_SRID(a.shape))
  6.                p_tolerance => 0.005,
  7.                p_deltaX => 200.0,
  8.                p_deltaY => 100.0
  9.                p_deltaZ => NULL,
  10.                p_mbr => NULL
  11.            ),
  12.            SDE.ST_SRID(a.shape)
  13.         ) AS movedShape
  14.   FROM <my Table> a;

Be careful with SRID values as ESRI’s and Oracle’s may not be the same.

  1. -- Oracle
  2. SELECT *
  3.   FROM cs_srs
  4.  WHERE srid = <value>;
  5. -- ESRI
  6. SELECT *
  7.   FROM sde.spatial_references
  8.  WHERE srid = <value>;

I hope this is helpful to those who find themselves in this 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