1 Reply Latest reply: Mar 29, 2010 10:04 AM by Jack Wang-Oracle RSS

    Convert LAT/LONG to X/Y

      Hello all,

      i've searched this forum to see how I can convert LAT/LONG values to XY coordinates.
      I've found this:

      sdo_geometry(2001, 8307, null, sdo_elem_info_array(1, 1003, 1),
      sdo_ordinate_array(52.849497, 5.723975, 52.883151, 5.999025))

      I thought that when i executed this query, i'd see 2 values. But i was wrong.

      Is there a function that will return the XY when I give the LAT/LONG values ???
        • 1. Re: Convert LAT/LONG to X/Y
          Jack Wang-Oracle
          You geometry does not validate (sdo_geom.validate_geometry(...) ,gtype and etype don't match!). You used a gtype 2001 (point) but in your elem_info_array (etype 1003) is an exterior polygonal ring. You need to fix your geometry first.
          When you said you want to converting lat/long to X/Y did you mean to convert a lat/long to a point (x,Y) in a projected coordinate system? If so, you can convert the geodetic geometry to a projected geometry(sdo_cs.transform), then use the SDO_UTIL.GETVERTICES function to retrieve all points in their order.

          From oracle spatial manual :

          geom IN MDSYS.SDO_GEOMETRY,
          to_srid IN NUMBER

          geometry IN SDO_GEOMETRY


          Returns the coordinates of the vertices of the input geometry.



          Geometry for which to return the coordinates of the vertices.

          Usage Notes

          This function returns an object of VERTEX_SET_TYPE, which consists of a table of objects of VERTEX_TYPE. Oracle Spatial defines the type VERTEX_SET_TYPE as:

          CREATE TYPE vertex_set_type as TABLE OF vertex_type;

          Oracle Spatial defines the object type VERTEX_TYPE as:

          CREATE TYPE vertex_type AS OBJECT
          (x NUMBER,
          y NUMBER,
          z NUMBER,
          w NUMBER,
          id NUMBER);

          This function can be useful in finding a vertex that is causing a geometry to be invalid. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in Chapter 15), you can use the GETVERTICES function to view the vertices in tabular format.


          The following example returns the X and Y coordinates and ID values of the vertices of the geometries in the SHAPE column of the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)

          SELECT c.mkt_id, c.name, t.X, t.Y, t.id
          FROM cola_markets c,
          TABLE(SDO_UTIL.GETVERTICES(c.shape)) t
          ORDER BY c.mkt_id, t.id;

          MKT_ID NAME X Y ID
          ---------- -------------------------------- ---------- ---------- ----------
          1 cola_a 1 1 1
          1 cola_a 5 7 2
          2 cola_b 5 1 1
          2 cola_b 8 1 2
          2 cola_b 8 6 3
          2 cola_b 5 7 4
          2 cola_b 5 1 5
          3 cola_c 3 3 1
          3 cola_c 6 3 2
          3 cola_c 6 5 3
          3 cola_c 4 5 4
          3 cola_c 3 3 5
          4 cola_d 8 7 1
          4 cola_d 10 9 2
          4 cola_d 8 11 3

          15 rows selected.