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

# 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:

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

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
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 :

SDO_CS.TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
to_srid IN NUMBER
) RETURN MDSYS.SDO_GEOMETRY;

SDO_UTIL.GETVERTICES
Format
SDO_UTIL.GETVERTICES(
geometry IN SDO_GEOMETRY
) RETURN VERTEX_SET_TYPE;

Description

Returns the coordinates of the vertices of the input geometry.

Parameters

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.

Examples

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.