How to get lat and long from sdo_geometry in oracle

Question

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

Splenic

Answer 1

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.

Multiped

fascinatingly, just FYI, you MUST use a table alias on the table that has the geometry to use the sdo_point.x|y – Gustafsson

To be clear: this is a generic requirement when using objects in Oracle. You also need an alias to invoke any object method. This is in SQL, not OL/SQL: you can inspect objects in a variable directly. – Multiped

Answer 2

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;

Arteriovenous

The accepted answer doesn't appear to work if you only have a Locator license. At least, it didn't for us. This answer does work for a Locator license. – Mechanize

That is strange because using sdo_point_type is very much supported in locator. – Arteriovenous

It is strange, and I'm not saying there's not another reason. But I have a Point geometry in a.ora_geometry. If I use sdo_util.getvertices(a.ora_geometry) as above, it works. If I say "select ora_geometry.sdo_point from a", I get "ORA_GEOMETRY.SDO_POINT: ungültiger Bezeichner" – Mechanize

This syntax should work: select t.geometry.sdo_point from table t – Arteriovenous

You are correct. If I use select ora_geometry.sdo_point from foo_table, I get invalid identifier. If I use select t.ora_geometry.sdo_point from foo_table t, it works. Can you explain why the table alias is neccessary? – Mechanize

No, I just know it is. – Arteriovenous

"Can you explain why the table alias is necessary?". That has to do with ambiguities with the "." notation for the SQL parser. Adding an alias lifts this ambiguity. – Multiped

Answer 3

This will not work if you do not use aliases.

Mulderig

Can you add more context to your answer? – Cabana

Use alias form name of the tables, example: select * from tabla t – Mulderig

Answer 4

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

Hibernate