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 ;)