How to get lat and long from sdo_geometry in oracle

42.7k views Asked by At

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))
4

There are 4 answers

2
Albert Godfrind On BEST ANSWER

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.
7
Rene On

You can use sdo_util.getvertices. Example from the documentation

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;
2
carlosaar22 On

This will not work if you do not use aliases.

0
Parmanand On

select a.id, t.x, t.y from geometry_table a,table(sdo_util.getvertices(a.geometry_column)) t where a.id = 1;