Create the Geometry columns in Oracle Database

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;