New versions of LINEAR, CENTROID and GEOM etc packages

——————————————————————————————-
NOTICE (2020):
All packages have been moved to my new SPDBA website
——————————————————————————————-

——————————————————————————————-
UPDATE (21 Mar 2018):
——————————————————————————————-

I have just released a new version of my GEOM etc PLSQL Package objects.

The changes include a re-write of the AFFINE package.
Conditional compilation has been removed which means any XE installations have now to be done manually.

I have also removed all references to MDSYS.SDO_3GL which means that the following functions have been removed:

• GEOM.SDO_MBR
• GEOM.SDO_LENGTH
• SDO_AREA

The GEOM package now only uses:

• SDO_GEOM.SDO_MBR.
• SDO_GEOM.SDO_LENGTH
• SDO_GEOM.SDO_AREA

Thus, any use of the GEOM package for versions of Oracle Locator that do not allow free access to these three functions will incur a break in the license agreement of the user.

However, an improved test has been implemented to detect the version of Oracle in order to protect the user from the incorrect use of SDO_GEOM package geoprocessing functions such as SDO_UNION in versions other than 12c. From 12cR1 onwards,, all SDO_GEOM functions are available to Locator users.

A number of errors have been corrected (eg AFFINE.Relate had an incorrect conversion of the supplied angle in degrees to radians) which are not listed.

The Windows installer has been updated and improved.

The EXPORTER package has undergone a major re-write and is being prepared for release.

——————————————————————————————-
— PREVIOUS RELEASE (8 Oct 2015):
——————————————————————————————-

I have just updated the GEOM package to contain a function called Oriented_Point_Angle

I also checked the installer and corrected the install (a new version of a check for database version was failing) and uninstall command tool scripts.

——————————————————————————————-
— PREVIOUS RELEASE (10 Dec 2009):
——————————————————————————————-
Today I uploaded a new release of my free PL/SQL packages.

The release includes:

• GML to SDO conversion for 10g users (involves need to compile and install java code and JAVAGEOM package);
• Ability to execute external applications (see this article for an example) from within the database ;
• New DEBUGPKG for use when developing new PL/SQL functions and procedures;
• Fixes to the KML package;
• Move of constants from GEOM package to the CONSTANTS package;
• Modification Windows installation script to automatically compile and load required java source and classes;
• Addition of UpdateSdoMetadata procedure to TOOLS package;
• Addition of ST_NumGeometries and ST_GeometryN functions to ST_Geom package;
• Fixed arc handling bug in ExtractElementsPiped function in GEOM package;
• Fixed geodetic SRID handling bug in Spit function in GEOM package.

Plus other changes.

The packages are available here

——————————————————————————————-
— PREVIOUS RELEASE (18 May 2009)
——————————————————————————————-

I have not reported on the numerous released of my PL/SQL packages that I have released since last time.

A while ago I decided that I would not longer support 9i based installs. But just last week (May 2009) someone contacted me about a failed install on 9.2. I received the install errors which were:

• MDSYS.ST_GEOMETRY does not exist (those functions that depend on this are moved to an new package called ST_GEOM);
• MDSYS.VERTEX_TYPE install problems related to the fact that at 9i MDSYS.VERTEX_TYPE was created (related to MDSYS.SDO_UTIL.GETVERTICES) but that it did not include the id field (introduced at 10g) and;
• At 9i the MDSYS prefix is required on SDO_GEOMETRY etc but not at 10g (public synonyms exist).

So, I responded to the reported issues by re-casting the packages such that they would compiled on 9i.

The new packages are available here

I have downloaded, installed and tested the scripts against a fresh 9.2.0.1 Enterprise Edition database. All tests passed which was very pleasing.

I develop on 10g Enterprise Edition. The code should recognise when the host is Standard Edition and thus only use Locator functionality but something accidently may “go through to the keeper!”.

Please let me know if you have issues with Locator or any other version.

——————————————————————————————-
— PREVIOUS RELEASE (09 Oct 2007):
——————————————————————————————-
Over the past 6 months I have had many contracts all of which contributed and idea to the latest revision of my free PL/SQL packages.

I hope these are of use to fellow Oracle Spatialliers out there.

A number of function in GEOM have been renamed to remove the AsPipelined suffix from their names.

This release includes the following:

• Made the packages query and use EE features only when EE is installed
• Java functions have been removed partly due to the lack of Java in XE (my main development platform) and partly due to a complete re-write that will include some JTS functions and the ability to write a shapefile directly from the Oracle database.
• The TOOLS package has had a lot of work done on it in the area of logging activity such that I will be able to bind its output to Oracle’s excellent Application Express (APEX).
• The GEOM package has had had some functions renamed and the following added:
  * Two SDO_Area() functions
  * Two SDO_Length() functions
  * TO_3D() function (with default Z support)
  * Fix_3D_Z() function that replaces NULL Z values with real numbers.
  * ToSdoPoint() that converts a single point whose ordinates are stored in the SDO_ORDINATE_ARRAY to one in which the ordinates are stored in SDO_POINT_TYPE() structure.The Distance() function has been moved to the COGO package:

  The TO_KML() function has been moved a new KML package.

• New KML package with the following functions:
  * Header()
  * To_KML() – A Procedure and a standalone Function
  * Footer()
  * GetDocument()This package can create a KML document via use of Header(), multiple TO_KML procedure calls, followed by Footer() and the GetDocument which will return the completed document to you as a CLOB.
• COGO package has had added: * isGeographic() function that, given an SRID, indicates TRUE if the SRID is longitude/latitude (geographic) or not.
  * Distance() overload functions for computing a distance geographic data
  * GreatCircleBearing() which computes a great circle bearing between two longitude/latitude pairs (ellipsoid can be specified).
  * Longitude/Latitude functions to complement DMS2DD
• CONSTANTS package has had the following “inspector” functions added:
  FUNCTION PI RETURN NUMBER;
  FUNCTION MaxNumber RETURN NUMBER;
  FUNCTION MinNumber RETURN NUMBER;
  FUNCTION MaxLong RETURN NUMBER;
  FUNCTION MinLong RETURN NUMBER;

——————————————————————————————-
— PREVIOUS RELEASE (10 May 2007):
——————————————————————————————-

Tonight I uploaded some changes to my free PL/SQL packages.

The changes include:

• Addition of a NETWORK package that includes some functions for returning the start and end vertex of a linear geometry.
• A new TO_3D() function in the GEOM package that converts a 2D geometry to a 3D geometry.
• Some fixes to the loader and packages as this was the first time I had tried to load the packages into XE.

Because of the latter, I have removed the Java function wrappers in GEOM for GML2GEOMETRY until I have done two things.

• Made the loader able to detect if XE/SE is the host database rather than EE
• I have completed the testing of the original Java version of the centroid function (in progress)
• Finished programming a Peano space key function in Java
• Modified some code that creates a shapefile from Oracle Spatial (will be called SDO2SHAPEFILE)

——————————————————————————————-
— PREVIOUS RELEASE (1 Mar 2007):
——————————————————————————————-

Some customers notified me of a problem they were experiencing with downloading my free spatial types and PL/SQL packages from this site. The problem was that their corporate net-nannies were reporting the zip file as having suspicious executables and scripts within it.

I suspected that it might be the Windows .cmd files which are using when installing the packages. But also the zip file contained a Java .class file (I noted that the original .java file was missing from the zip file so I have added it in). To address the problem, I have uploaded a new version of the file in which:

• The compiled java class utilities.class has been removed (a script will recompile it from the included source – utilities.java)
• All *.CMD files have been renamed to *_CMD
• A ReadMe.Txt file has been created that informs users that the *_CMDs must be renamed to *.CMD.

This zip file has been tested by customers and passes their net-nanny tests!

Thanks to all who reported this problem.

——————————————————————————————-
— PREVIOUS RELEASE (15 Feb 2007):
——————————————————————————————-
Today I uploaded some changes to my Oracle Spatial and Locator PL/SQL packages and types source code which is available as a free download from this site.

The changes include:

• A new TO_KML() function in the GEOM package (I thought of calling it GEOMETRY2KML or TO_KMLGEOMETRY but decided against it).

This allows for the conversion of any geodetic sdo_geometry object to be converted into KML via GML. It does so not via XSLT but rather by hacking the GML produced by the SDO_UTIL.TO_GMLGEOMETRY() function. It is not meant to be a complete or totally flexible method for converting sdo_geometry to KML but to give an indication of what is possible. This function could be used in a trigger with the UTL_SMTP package to email a KML description of a geometry to someone with Google Earth as a quick visualisation/check of a geometry. Or it could be used inside an Application Express form or report for downloading or visualisating a batch of sdo_geometries.

• Some additional test data has been constructed;
• Some minor improvements in TOOLS.GeometryCheck;
• Changes to some of the test functions.

——————————————————————————————-
— PREVIOUS RELEASE (9 Jan 2007):
——————————————————————————————-

I have just uploaded a whole bunch of changes to my PL/SQL packages for processing Oracle Spatial and Locator data.

The most important aspects of the changes are:

• I have introduced a new TOOLS package that includes a Metadata Analysis capability (see MetadataAnalyser procedure). This capability can examine the spatial data held in a table/column and create from scratch or update/validate an existing SDO_GEOM_METADATA record. This includes discovery of sdo_tolerance, SRID and data extents (3D data is supported).
• The old independent ShapeCheck routine is now integrated into the TOOLS package but is now called GeometryCheck.
• A new Vertex Analysis function is included in the TOOLS package that will help a GIS Administrator keep track of the complexity of data over time.

All these functions write output into a new set of metadata tables that are created in the schema where the PL/SQL packages are stored (normally CODESYS).

Finally, I have constructed a set of test/example spatial tables that can be used for testing your PL/SQL functions. The test_*.sql code uses this data when testing the installation of the packages.

I hope you find these packages useful. If you do, or don’t, please let me know.