### 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 ...