Many times we have
the Geo location related information as latitude and longitude. In order
to plot these on the maps using mapviewer in OBIEE we need to convert
these into spatial data which is a option available with the oracle
database. In this entry we will look at how to convert the lat/lon
information into Oracle Spatial data.
1. Create
the Geometry columns in Oracle Database
a. Based
on the latitude/longitude information that is available create Geometry column
b. Insert
Geometry based columns in metadata tables (USER_SDO_GEOM_METADATA)
c. Create
a spatial index
Let’s
look at each of these steps in detail, with the help of an example. As
mentioned earlier, the assumption is that the data we get from the customer has
information on latitude and longitude. If the data that the customer provides
does not have latitude-longitude information, the flow will change slightly to
use the spatial capabilities of the database to convert the address into
latitude and longitude.
Step
1: Create the Geometry Columns in the Database
There
are two columns latitude and longitude that need to be available to be able to
use the spatial capabilities of the Oracle Database.
1. Alter
the table to create the Geometry column. In the example below, we create a
column called store_geo in the “store” table
alter table store add (store_geo sdo_geometry);
2. Populate
the new column using the following query:
update store set store_geo = MDSYS.SDO_GEOMETRY(2001, 8307,
MDSYS.SDO_POINT_TYPE (longitude, latitude,NULL),NULL,NULL);
commit;
3. Check
if the column has been populated correctly:
4. Update
the spatial metadata table with the details for the new geometry column created
INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('store', 'store_geo',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('LONG',
-180.0, 180.0, 0.005),
MDSYS.SDO_DIM_ELEMENT('LAT',
-90.0, 90.0, 0.005)
),
8307);
COMMIT;
5. Create
the spatial index on the geometry column
DROP INDEX
store_idx;
CREATE INDEX
store_idx ON store(store_geo);
INDEXTYPE IS
mdsys.spatial_index;