This is the second in a series of posts on Maps in OBIEE.  Part 1 – Map Basics is here.

In the database geometry objects are stored in a special column type called SDO_GEOMETRY that is specifically for geometric data.  The database stores geometry objects, such as States, as an ordered set of points, which are then connected by line segments by Map Viewer to render the shape.

The type, and its associated metadata views and functions, are provided by the MDSYS schema which is included with your database installation.  Although the SDO_GEOMETRY type is associated with the MDSYS schema it can be used by any database user to create geometry columns in any schema.  The only requirement is that there must be a spatial index on the table that contains the geometry type and there must be an entry in the USER_SDO_GEOM_METADATA view that tells the database that a particular table and column contain geometric data in order for functions and rendering operations to work correctly.

The geometry type is analogous to an XML type in that it is stored as a column in a table but also has its own internal structure that can be addressed by various database functions provided specifically for it.

Using an example from the documentation, if we look at the polygon named cola_b rendered on an xy grid we can see that its definition should include the points (5,1, 8,1, 8,6, 5,7, 5,1).

If we look at the COLA_MARKETS table in the database and look at the row that contains cola_b we can see that  the SHAPE column (which is an SDO_GEOMETRY type) does indeed contain those points.  Checking to see we have an entry in the USER_SDO_GEOM_METADATA view and that we have a spatial index on the table we can create a map view of our COLA_MARKETS table right inside of SQL Developer.

Map based objects such as a US State are merely a more complex example of a polygon that have a reference to a projection system (SRID) for rendering the earth’s 3 dimensional surface on a 2 dimensional plane.  If we look at a stored geometry for Colorado we can see that the only difference between it and the simple polygon we created for cola_b above is a reference to an SRID code and the number of points (longitude and latitude in this case) needed to render it.

That covers the basics of what you need to know about geometry and the database for now.  The SDO_GEOMETRY column type is used to store geometry definitions in the database and the USER_SDO_GEOM_METADATA view gives the database the information it needs to create spatial indexes.

I’ll show you in another post that lines, points, circles, etc. can also be associated with and rendered on maps and it’s pretty easy to create and use these objects to add even more insight to your map visualizations.  Additionally, we’ll talk about several important and useful database procedures & functions that are provided for validating (and fixing if needed) your geometric data which is especially helpful when we import freely available shapefiles into our database to create our own maps for use inside of OBIEE.