PL/SQL package: lessons learned

Working with Oracle Spatial a lot, I found a great source of information in the Spatial DB Advisor, which besides a lot of interesting articles, also offers his source-code and PL/SQL packages for free. The site is not very user-friendly (trouble of seeing the threes through the wood), as finding the free packages is something that i only succeed in coming from Google :)
A shortcut: you can find them here.

But a word of warning: if you try to install this package in an existing schema, it will delete all indexes and tables. This is something i had to discover the hard way (painful i might add). So i have adapted my version of create_test_data.sql, where at the top of the file all indexes and tables of the user are dropped.

Nevertheless, extremely useful package.

I will list my favourites (for now):

  • isCompound: checks whether a geometry contains any circular/arc elements
  • ConvertGeometry: converts any special elements – circulararcs, rectangles, circles – in a geometry to vertex to vertex linestrings
  • to_2d: converts a sdo_geometry to 2d. Very useful for us, as geoserver can’t draw 2d, and we only use 2,5d anyway. Meaning that we always use a topview, and the z is the elevation level of the ground-level. Or for pipelines: below ground :)
  • sdo_mbr: function to determine the minimum bounding rectangle! i will use this to adapt my script to fill user_sdo_geom_metadata. Cool :)
  • functions to investigate the data of sdo_geometry (number of rings, number of vertexes, number of coordinates, the coordinates itself, …)
  • functions to investigate the meta-data
  • functions to manipulate your sdo-geometry: scale, rotate, affine transformations, move, adding and removing points, …
  • … and lots more …

In short: awesome!!

So after getting a terrifying scare, now rebuilding the databases (and adapted the script: won’t happen to me again). I mailed the original creator to add a note of warning on his site too. Might help other absent-minded developers :)

I also had another smaller problem, because the install-script overruled my ORACLE_HOME and PATH definition, which made sure SQL*PLUS was not found. But that was easily cured. I am very grateful that he puts up this package for free. And i learned another great lesson today: not to get too enthusiastic before i am sure that it all works ;)