Showing results for 
Search instead for 
Do you mean 

How can an Oracle view be created that contains longitude/latitude columns for points (which are projected)?

by Technical Evangelist on ‎03-14-2016 01:58 PM - edited on ‎04-06-2016 07:48 AM by Moderator (755 Views)

Question

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?

Answer

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

 

Contributors