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 or routines from the MDSYS schema with their embedded algorithms and exposing our routines for execution through our web clients to provide the service externally.

Example SQL

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

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 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, your input parameters would be the column names and ‘dual’ would be replaced 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
• 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;
/