displaying table of longitude and latitude points

While in most cases your location data are stored in an Oracle database as SDO_GEOMETRY type, sometimes you may have certain point data stored simply as two numeric columns in a table (as longitude/latitude for instance). So how can you display these points using MapViewer? In this article I will present one of the more elegant options, namely using function-based index.

The overall steps are:
1. Create a database function that returns a SDO_GEOMETRY object from two numeric values.

2. Treat this function as a SDO_GEOMETRY column by creating an entry in the USER_SDO_GEOM_METADATA view.

3. Create a Spatial index on this function

4. Create a pre-defined theme in Map Builder for this function.

5. Profit.

Lets say we have an existing table CITIES in the schema SCOTT.

SQL> desc cities;
Name Null? Type
------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(100)
ALIASE VARCHAR2(500)
COUNTR VARCHAR2(100)
POP NUMBER
LON NUMBER
LAT NUMBER


Where the two columns LON and LAT represent each city's location.

So the first step is to create a function that generates an actual SDO_GEOMETRY object when supplied with a pair of (lon, lat).

Step 1. Create function

Execute the following while logged in as SCOTT:

create or replace function get_geometry(lon in number,
lat in number)
return SDO_GEOMETRY deterministic is
begin
return sdo_geometry(2001, 8307, sdo_point_type(lon, lat, NULL),NULL, NULL);
end;
/


Note that the function must be deterministic.

Step 2. Populate Spatial metadata view

Now that we have a function, we need to make Spatial and MapViewer treat it as if it's a real SDO_GEOMETRY column. All we need to do is insert an entry for this function in the USER_SDO_GEOM_METADATA view. Again execute the following as SCOTT:

insert into user_sdo_geom_metadata values('CITIES',
'scott.get_geometry(lon,lat)',
sdo_dim_array(
sdo_dim_element('Longitude', -180, 180, 0.005),
sdo_dim_element('Latitude', -90, 90, 0.005)),
8307);

commit;


Step 3. Create a Spatial index:
We now need to create a Spatial index on the function, just like with any SDO_GEOMETRY columns. Without a Spatial index, your SQL spatial queries won't work, and MapViewer cannot display them easily.

create index CITIES_SDX on
CITIES(get_geometry(lon,lat))
indextype is mdsys.spatial_index;


Step 4. Create a Map Builder theme

Open Map Builder, click Show Data to open the data navigator. You should find the CITIES table under the user SCOTT. Right click it and choose "Create Geometry Theme". You should see that the function we just created is already picked up as the Spatial column, as illustrated in this screen shot:



Go through the normal process and complete the theme creation. You now have a pre-defined MapViewer theme that can be used to display those (X,Y) data. You can also add this theme to your AJAX map as a FoI (Feature Of Interest) layer.