Bulk conversion of coordinates

We use Oracle Spatial functionality — also available in Oracle Locator — to convert coordinates from one coordinate system to another.

We have written our own functions and packages calling the relevant spatial functions/routines from the MDSYS schema with their embedded algorithms and exposing our routines for execution through our web clients to provide the service externally.

For conversion, we run a SQL script (and this can be called in the client) calling the relevant routine with the necessary parameters as below:

SELECT bgs.coord_convert_pkg1.transform_x_f1 (287137, 4337439, 2100, 4121) from dual;

• bgs.coord_convert_pkg1.transform_x_f1: a function transform_x_f1 for converting the x coordinate as below, is in a package called coord_convert_pkg1, which is held in a schema called bgs
• (287137, 4337439, 2100, 4121): the arguments as defined as detailed respectively:
  • X_IN: the original X value
  • Y_IN: the original Y value
  • OLD_SRID: the original coordinate system (e.g. 2100 = Greek Grid ; 81989 = British National Grid),
  • NEW_SRID: the new coordinate system (e.g. 4121 = GGRS86, 4258 = WGS84)
• The SQL output will be the X in new coordinate system

How to use the code

Generally, you call the appropriate function (all functions are listed below) in SQL/PLSQL, passing the relevant parameters and then update your data with a new column(s) for the relevant transformation.

For bulk conversion, you need to create a table with your values. From the example SQL above, your input parameters would be the column names and also replace "dual" with the source table name. Then run your SQL, routine or even a call from a client with the SQL embedded against the source table and direct your output as appropriate. The output can be directed to populate new columns in your source table, a separate table or as a file output.

We are using the EPSG documented SRIDs in an Oracle Spatial implementation for these coordinate systems and a spatial transformation function within Oracle Spatial.

We have three functions: one for the X coordinate, one for the Y coordinate and one to return a full coordinate pair (X and Y) used within the first two. We use the first two in our coordinate conversion application.

The code

FUNCTION TRANSFORM_X_F1
    (X_IN IN NUMBER, Y_IN IN NUMBER, OLD_SRID IN NUMBER, NEW_SRID IN NUMBER)
    RETURN number
    IS
    --
    -- Function to convert/transform between coordinate systems - this function returns the x-coordinate
    -- Input: X_IN - the original X value
    --        Y_IN - the original Y value
    --        OLD_SRID - the original coordinate system (e.g. 2100 = Greek Grid,
    --                                                    81989 = British National grid)
    --        NEW_SRID - the new coordinate system (e.g. 4121 = GGRS86, 4258 = WGS84)
    --
    -- Output: X in new coordinate system
    --
    -- Example: select bgs.coord_convert_pkg1.transform_x_f1 (287137, 4337439, 2100, 4121) from dual
    --
    tmpPoint SDO_POINT_TYPE; /* Temporary holder for point */
    --
    begin
      tmpPoint := transform_coord_f1(x_in, y_in, old_srid, new_srid);
      return tmpPoint.x;
    END TRANSFORM_X_F1;

    FUNCTION TRANSFORM_Y_F1
    (X_IN IN NUMBER, Y_IN IN NUMBER, OLD_SRID IN NUMBER, NEW_SRID IN NUMBER)
    RETURN number
    IS
    --
    -- Function to convert/transform between coordinate systems - this function returns the y-coordinate
    -- Input: X_IN - the original X value
    --        Y_IN - the original Y value
    --        OLD_SRID - the original coordinate system (e.g. 2100 = Greek Grid,
    --                                                    81989 = British National grid)
    --        NEW_SRID - the new coordinate system (e.g. 4121 = GGRS86, 4258 = WGS84)
    --
    -- Output: Y in new coordinate system
    --
    -- Example: select bgs.coord_convert_pkg1.transform_y_f1 (287137, 4337439, 2100, 4121) from dual
    --
    tmpPoint SDO_POINT_TYPE; /* Temporary holder for point */
    --
    begin
      tmpPoint := transform_coord_f1(x_in, y_in, old_srid, new_srid);
      return tmpPoint.y;
    END TRANSFORM_Y_F1;

    FUNCTION TRANSFORM_COORD_F1
    (X_IN IN NUMBER, Y_IN IN NUMBER, OLD_SRID IN NUMBER, NEW_SRID IN NUMBER)
    RETURN MDSYS.SDO_POINT_TYPE
    IS
    --
    -- Function to convert/transform between coordinate systems - this function returns a complete sdo_point
    --
    -- Input: X_IN - the original X value
    --        Y_IN - the original Y value
    --        OLD_SRID - the original coordinate system (e.g. 2100 = Greek Grid,
    --                                                    81989 = British National grid)
    --        NEW_SRID - the new coordinate system (e.g. 4121 = GGRS86, 4258 = WGS84)
    --
    -- Output: X in new coordinate system
    --
    -- Example: select bgs.coord_convert_pkg1.transform_coord_f1(287137, 4337439, 2100, 4121) from dual
    --
    tmpGeom SDO_GEOMETRY; /* Temporary holder for Shape */
    --
    begin
      tmpGeom := sdo_cs.transform ( MDSYS.SDO_GEOMETRY( 2001, old_srid, MDSYS.SDO_POINT_TYPE
       (x_in, y_in, NULL), NULL, NULL), new_srid)
      ;
      return tmpGeom.sdo_point;
    end TRANSFORM_COORD_F1;
END COORD_CONVERT_PKG1;
/

Contact

For more information please contact Martin Nayembil.