ADM_How_to_use_Discoverer_with

This explanation assumes you want to perform spatial functions on data that has addresses. In this case I have a table of customers, with addresses. I have used the OWB Name and Address Geocodes to apply longitude and latitude coordinates. I also have a table of US cities that have location information, so I can compute distances between city centers and customer locations. You don't need two tables to do spatial calculations - you could compare the distances between customers in rows in the same table.

Solution:

1. Populate latitude and longitude columns from address data using OWB Name and Address Geocode (or Pure Integrate), or other tool.

2. Add a new column of type MDSYS.SDO_GEOMETRY.

ALTER TABLE customer_merge_temp add(geometry MDSYS.SDO_GEOMETRY)

3. Update the MDSYS.SDO_GEOMETRY column:

UPDATE customer_merge_temp
     SET geometry = MDSYS.SDO_GEOMETRY(2001, 8265,
MDSYS.SDO_POINT_TYPE(longitude,latitude,NULL), NULL, NULL)
   WHERE longitude IS NOT NULL
     and latitude IS NOT NULL;

-- comments --

- 2001 (geometry type) - specifies the geometry type (2001 being a 2 dimensional point. See the Oracle Spatial User's Guide for other valid types.)

- 8265 (SRID) - specifies the system reference identifier and is used to identify a coordinate system used to define the geometry. (8265 identifies the North American Datum 83 (NAD83))

- Please ensure records that are missing one of the ordinates (longitude/latitude) does not update the geometry column.

Populating the geometry column without all the required coordinates will cause errors when building a spatial index on the table. Also, you must use longitude as the X coordinate and latitude as the Y coordinate when populating the geometries SDO_POINT_TYPE attribute. The SDO_POINT_TYPE attribute provide an efficent way to store 2 dimensional and 3 dimensional points.

SQL> desc customer_merge_temp

Name Null? Type
----------------------------------------------------- --------
--------------------
...
BUSINESS_NAME NOT NULL
VARCHAR2(360)
ADDRESS
VARCHAR2(240)
ADDRESS2
VARCHAR2(240)
CITY
VARCHAR2(60)
STATE
VARCHAR2(60)
COUNTRY
VARCHAR2(60)
ZIP
VARCHAR2(5)
...
LATITUDE
VARCHAR2(15)
LONGITUDE
VARCHAR2(15)
...
GEOMETRY
MDSYS.SDO_GEOMETRY

4. Import the City_geometry table (contains a row for every state/city in the US, with location (GEOMETRY Column).

SQL> desc city_geometries

Name                                      Null?    Type
----------------------------------------- --------
----------------------------
FID                                       NOT NULL NUMBER(38)
CITY                                     NOT NULL VARCHAR2(50)
COUNTY                             NOT NULL VARCHAR2(50)
STATE                                 NOT NULL VARCHAR2(50)
GEOMETRY                                             MDSYS.SDO_GEOMETRY
STATE_CODE                                         VARCHAR2(2)

5. Update Spatial Metadata:

INSERT INTO user_sdo_geom_metadata VALUES
('CUSTOMER_MERGE_TEMP','GEOMETRY',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -180, 180,.5),
MDSYS.SDO_DIM_ELEMENT('LATITUDE', -90, 90,.5)), 8265);

INSERT INTO user_sdo_geom_metadata VALUES
('CITY_GEOMETRIES','GEOMETRY',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -180, 180,.5),
MDSYS.SDO_DIM_ELEMENT('LATITUDE', -90, 90,.5)), 8265);

-- comments --

- Note: You will only need to populate the metadata for the city_geometries table if it was exported from the source system prior to the table being spatially indexed. If a spatial table is exported while the spatial index exists then the import will populate the metadata and create the spatial index.

- USER_SDO_GEOM_METADATA is used by Oracle Spatial to define the valid values for a spatial tables coordinate system. You must have at least 2 dimensions defined for each spatial table. In this case, we are defining a geodetic coordinate system containing a longitude and latitude value. We also identify the coordinate system by specifying the SRID value of 8265 (NAD 83). If the metadata table identifies a SRID for the spatial table then each record with a populated geometry must also specify the same SRID in the spatial object.

- You must have a metadata entry for all tables containing a spatial geometry column. In this case we have two tables, customer_merge_temp and city_geometries. If the metadata entry does not exist, then Oracle will generate errors whenever you try to index the spatial column or utilize any of the spatial operators.

- The SDO_DIM_ELEMENT specifies the dimensions name, lower boundary, upper boundary and tolerance. Tolerance is used to determine when two points are close enough to be considered the same point. For geodetic coordinate systems, like the one we define above, the tolerance is specified in meters. In this case, we are using a tolerance of 1/2 of a meter.

6. Create spatial index:

CREATE INDEX cm_geometry_idx ON customer_merge_temp(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE INDEX city_geometry_idx ON city_geometries(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

7. Create a view to report on:

CREATE OR REPLACE VIEW customer_merge_spa_v AS SELECT cg.state geo_state, cg.city geo_city, SDO_GEOM.SDO_DISTANCE(cg.geometry, cmt.geometry, .5, 'unit=mile') distance,cmt.*
FROM city_geometries cg,

customer_merge_temp cmt;

- The SDO_DISTANCE function is used to calculate the great circle distance between the customer and the target city. The above view returns the distance in miles. Oracle Spatial supports over 20 distance units and they can be found in the Oracle Spatial User's Guide and Reference.

8. Using Discoverer Administrator, create a folder on the view.
9. Create a workbook (attached screen shot)

Discoverer Administrator—How to Use Discoverer with Oracle Spatial

Solution: