oracle 如何从oracle中的sdo_geometry获取纬度和经度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30908013/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get lat and long from sdo_geometry in oracle
提问by Banafshe Alipour
how can i get lat and long from point in oracle?
我怎样才能从 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))
回答by Albert Godfrind
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:
您显示的符号不是表示单个 2D 或 3D 点的最佳符号。对这些点进行编码的常见且最有效的方法是：
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.
我见过的所有 GIS 工具都使用这种表示法。您展示的那个也是有效的——它只是使用了更多的存储空间。但是这两个符号在功能上是完全等效的。
Using the compact notation, getting the individual coordinates out is trivial. For example, considering that US_CITIES contains point in the compact notation above:
使用紧凑符号，获取单个坐标是微不足道的。例如，考虑到 US_CITIES 在上面的紧凑符号中包含点：
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:
从您使用的更复杂的基于数组的表示法中获得相同的结果更加复杂。您可以使用 SDO_UTIL.GETVERTCES 方法。例如，假设 US_CITIES_A 包含相同的点，但采用基于数组的表示法：
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.
回答by Rene
You can use sdo_util.getvertices. Example from the documentation
您可以使用 sdo_util.getvertices。文档中的示例
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;
回答by carlosaar22
This will not work if you do not use aliases.
如果您不使用别名，这将不起作用。
回答by Parmanand
select a.id, t.x, t.y from geometry_table a,table(sdo_util.getvertices(a.geometry_column)) t where a.id = 1;
选择 a.id, tx, ty from geometry_table a,table(sdo_util.getvertices(a.geometry_column)) t where a.id = 1;
