How to get lat and long from sdo_geometry in oracle

The notation you are showing is not the best one for representing single 2D or 3D points. The common and most efficient way to encode these points is as follows:

SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(51.702814,32.624736,NULL),NULL,NULL)

      

        
        
        
      

    

All GIS tools I have seen use these symbols. The one you show is also valid - it just uses more storage. But these two entries are fully functionally equivalent.

Using compact notation, getting the individual coordinates is trivial. For example, given that US_CITIES contains a dot 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 more complex array based notation is more confusing. You can use SDO_UTIL.GETVERTICES approach. For example, if US_CITIES_A contains the same points, but in 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 find simpler is to simply define a few simple functions to extract values ​​from an 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 functions simplifies the 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.