如何从sdo_geometry中获取经度和纬度

问题描述

我怎样才能在oracle中获得经度和纬度?

how can i get lat and long from point in oracle?

赞:

MDSYS.SDO_GEOMETRY(2001,4326,NULL,
  MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),
  MDSYS.SDO_ORDINATE_ARRAY(51.702814,32.624736))

推荐答案

您显示的符号不是代表单个2D或3D点的最佳符号.编码这些点的最常见，最有效的方法是:

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)

我见过的所有GIS工具都使用此表示法.您显示的那个也是有效的-它只会使用更多的存储空间.但是这两个符号在功能上完全相同.

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.

使用紧凑的表示法，得出单个坐标是微不足道的.例如，考虑到US_CITIES在上面的紧凑表示法中包含点:

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.

从您使用的更为复杂的基于数组的表示法中获得相同的结果更加费解.您可以使用SDO_UTIL.GETVERTICES方法.例如，假设US_CITIES_A包含相同的点，但使用基于数组的表示法:

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;
/

和

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.

这篇关于如何从sdo_geometry中获取经度和纬度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！