When there are point features in an Oracle database that are in a pojected coordinate system, how does one create a view that includes longitude and latitude columns that dynamically update from the projected points?
In order for this to work, Oracle needs to know the coordinate system that the data is strored in. This requires the use of an SRID in the geometry column as well as in Oracle's metadata. The solution involves the use of two Oracle locator functions. The first transforms the existing geometry to a long/lat geometry using the function:
SDO_CS.TRANSFORM(geom IN SDO_GEOMETRY, to_srid IN NUMBER) RETURN SDO_GEOMETRY;
This requires that the SRID for the desired long/lat coordinate system is known. For example, 8307 is WGS 84.
Then the following statement is required.
SDO_UTIL.GETVERTICES(geometry IN SDO_GEOMETRY) RETURN VERTEX_SET_TYPE;
This will extract the vertices via a pipelined function that can be used in conjunction with a TABLE function to return the correct values.
Here is an example for a table called PT_TEST that has a GEOMETRY field that contains projected data. In this case, the projected data is being transformed to SRID 8307 (WGS 84):
CREATE OR REPLACE VIEW PT_TEST_VIEW AS SELECT A.PID, T.X AS LONGITUDE, T.Y AS LATITUDE FROM PT_TEST A, TABLE