How can I get lat and long from point in oracle?
Like this:
MDSYS.SDO_GEOMETRY(2001,4326,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),
MDSYS.SDO_ORDINATE_ARRAY(51.702814,32.624736))
The notation you show is not the best one for representing single 2D or 3D points. The common and most efficient way to encode those points is this:
SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(51.702814,32.624736,NULL),NULL,NULL)
All the GIS tools I have seen use this notation. The one you show is valid too - it just uses more storage. But the two notations are fully functionally equivalent.
Using the compact notation, getting the individual coordinates out is trivial. For example, considering that US_CITIES contains point in the compact notation above:
select c.city, c.location.sdo_point.x longitude, c.location.sdo_point.y latitude
from us_cities c where state_abrv='CO';
CITY LONGITUDE LATITUDE
------------------------------------------ ---------- ----------
Aurora -104.72977 39.712267
Lakewood -105.11356 39.6952
Denver -104.87266 39.768035
Colorado Springs -104.7599 38.8632
4 rows selected.
Getting the same result from the more complex array-based notation you use is more convoluted. You can use the SDO_UTIL.GETVERTICES approach. For example, assuming US_CITIES_A contains the same points but in the array-based notation:
select city, t.x longitude, t.y latitude
from us_cities_a, table (sdo_util.getvertices(location)) t
where state_abrv = 'CO';
CITY LONGITUDE LATITUDE
------------------------------------------ ---------- ----------
Aurora -104.72977 39.712267
Lakewood -105.11356 39.6952
Denver -104.87266 39.768035
Colorado Springs -104.7599 38.8632
4 rows selected.
Another approach I actually find simpler is to just define a couple of simple functions to extract the values from the array:
create or replace function get_x (g sdo_geometry) return number is
begin
return g.sdo_ordinates(1);
end;
/
and
create or replace function get_y (g sdo_geometry) return number is
begin
return g.sdo_ordinates(2);
end;
/
Then using the functions makes for a simpler syntax:
select city, get_x(location) longitude, get_y(location) latitude
from us_cities_a
where state_abrv = 'CO';
CITY LONGITUDE LATITUDE
------------------------------------------ ---------- ----------
Aurora -104.72977 39.712267
Lakewood -105.11356 39.6952
Denver -104.87266 39.768035
Colorado Springs -104.7599 38.8632
4 rows selected.