高效的使用空间SQL操作空间数据库系列之三

空间函数已经讲了三个类型了，再介绍一下最后一个类型，geometry类型。

该类型的函数的功能是创建一个新的geometry对象，其函数格式为：

--input:(Geometry_A, arcgs)

--returns (Geometry)

输入为一个Geometry对象以及一些参数，返回一个Geometry对象，以下三个函数是使用比较多的此类型的函数：

---ST_BUFFER

---ST_UNION

---ST_DIFFERENCE

最后举个例子，该例子在一个SQL语句中使用了前面所介绍的四种类型的函数，SQL语句如下：

select st_astext(shape) from testpoint where st_within(shape,st_buffer(st_point(118.75386000,30.02375000,10),0.01))=1 / ST_ASTEXT(SHAPE) -------------------------------------------------------------------------------- POINT ( 118.75386000 30.02375000) POINT ( 118.74942000 30.02905000) 上面介绍了矢量数据的空间存储类型以及各种类型的函数，从ArcSDE10开始，ESRI提供了对栅格 数据进行存储的空间存储类型ST_RASTER，在上一节中已经介绍了现在SQLSERVER，ORACLE和PostgreSQL 中支持这种存储类型，可以看看该存储类型在ORACLE中是如何定义的，首先现在ORACLE中安装此数据 类型： 192.168.100.228 [sde ~]$ sdesetup -o install_st_raster -d ORACLE10G -p sde ESRI ArcSDE Server Setup Utility Wed Feb 9 10:06:29 2011 ---------------------------------------------------------------- Install or update ST_RASTER schema objects: Are you sure? (Y/N): Y Creating ST_RASTER schema... C:/DOCUME~1/ZHAOGU~1/LOCALS~1/Temp: No such file or directory Successfully created ST_RASTER schema. Successfully installed ArcSDE components. Refer SDEHOME/etc/sde_setup.log for more details.

安装完后，查看其定义如下： SQL> desc st_raster st_raster is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- RASTER_ID NUMBER(38) TYPE NUMBER(38) EXTENT SE_EXTENT PROPERTIES SE_RASTERPROPERTY_LIST RASTERBAND_LIST ST_RASTERBAND_LIST DATA ST_PIXELDATA METHOD ------ FINAL CONSTRUCTOR FUNCTION ST_RASTER RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN OPT VARCHAR2 IN METHOD ------ FINAL CONSTRUCTOR FUNCTION ST_RASTER RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN METHOD ------ FINAL CONSTRUCTOR FUNCTION ST_RASTER RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DATA ST_PIXELDATA IN OPT VARCHAR2 IN METHOD ------ FINAL CONSTRUCTOR FUNCTION ST_RASTER RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DATA ST_PIXELDATA IN METHOD ------ MEMBER FUNCTION DESCRIBE RETURNS CLOB METHOD ------ MEMBER FUNCTION DESCRIBE RETURNS CLOB Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION BUILDPYRAMID RETURNS ST_RASTER METHOD ------ MEMBER FUNCTION BUILDPYRAMID RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION BUILDSTATS RETURNS ST_RASTER METHOD ------ MEMBER FUNCTION BUILDSTATS RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LEVEL NUMBER IN METHOD ------ MEMBER FUNCTION DELETESTATS RETURNS ST_RASTER METHOD ------ MEMBER FUNCTION DELETECOLORMAP RETURNS ST_RASTER METHOD ------ MEMBER FUNCTION SETCOLORMAP RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN METHOD ------ MEMBER FUNCTION EXPORT RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN METHOD ------ MEMBER FUNCTION EXPORT RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAME VARCHAR2 IN PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION MOSAIC RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAMES VARCHAR2 IN METHOD ------ MEMBER FUNCTION MOSAIC RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILENAMES VARCHAR2 IN PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION MOSAIC RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DATA ST_PIXELDATA IN METHOD ------ MEMBER FUNCTION MOSAIC RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DATA ST_PIXELDATA IN PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION CROP RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- EXTENT VARCHAR2 IN METHOD ------ MEMBER FUNCTION CROP RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- EXTENT VARCHAR2 IN UNIT VARCHAR2 IN METHOD ------ MEMBER FUNCTION SUBSET RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION SUBSET RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN OPT VARCHAR2 IN METHOD ------ MEMBER FUNCTION GETVALUE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BAND NUMBER IN LEVEL NUMBER IN X NUMBER IN Y NUMBER IN METHOD ------ MEMBER FUNCTION GETVALUE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BAND NUMBER IN LEVEL NUMBER IN POINT SE_COORD IN METHOD ------ MEMBER FUNCTION HASCOLORMAP RETURNS NUMBER METHOD ------ MEMBER FUNCTION HASSTATS RETURNS NUMBER METHOD ------ MEMBER FUNCTION GETCOMPRESSIONTYPE RETURNS VARCHAR2 METHOD ------ MEMBER FUNCTION GETINTERPOLATIONTYPE RETURNS VARCHAR2 METHOD ------ MEMBER FUNCTION GETINTERLEAVETYPE RETURNS VARCHAR2 METHOD ------ MEMBER FUNCTION GETPIXELTYPE RETURNS VARCHAR2 METHOD ------ MEMBER FUNCTION GETPYRAMIDLEVEL RETURNS NUMBER METHOD ------ MEMBER FUNCTION GETPIXELDATA RETURNS ST_PIXELDATA METHOD ------ MEMBER FUNCTION GETPIXELDATA RETURNS ST_PIXELDATA Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN METHOD ------ STATIC FUNCTION GETVERSION RETURNS NUMBER METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TYPE VARCHAR2 IN PARAM VARCHAR2 IN OPT VARCHAR2 IN METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OP VARCHAR2 IN PRIMARY VARCHAR2 IN PARAM VARCHAR2 IN OPT VARCHAR2 IN METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TYPE VARCHAR2 IN PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TYPE VARCHAR2 IN METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TYPE VARCHAR2 IN PARAM VARCHAR2 IN OPT VARCHAR2 IN MINX NUMBER IN MINY NUMBER IN MAXX NUMBER IN MAXY NUMBER IN DATA BLOB IN MASK BLOB IN METHOD ------ MEMBER FUNCTION UPDATEVALUE RETURNS ST_RASTER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OP VARCHAR2 IN DATA ST_PIXELDATA IN PARAM VARCHAR2 IN METHOD ------ MEMBER FUNCTION COPYVALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RASTERCOLUMNID UNDEFINED IN METHOD ------ MEMBER FUNCTION INSERTVALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RASTERCOLUMNID UNDEFINED IN FILENAME VARCHAR2 IN OPT VARCHAR2 IN METHOD ------ MEMBER FUNCTION INSERTVALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RASTERCOLUMNID UNDEFINED IN FILENAME VARCHAR2 IN OPT VARCHAR2 IN MINX NUMBER IN MINY NUMBER IN MAXX NUMBER IN MAXY NUMBER IN DATA BLOB IN MASK BLOB IN METHOD ------ MEMBER FUNCTION GETVALUE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BUF VARCHAR2 IN OFFSET UNDEFINED IN METHOD ------ MEMBER FUNCTION GETVALUE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BAND UNDEFINED IN LEVEL UNDEFINED IN X NUMBER IN Y NUMBER IN UNIT VARCHAR2 IN METHOD ------ MEMBER FUNCTION GETPIXELDATA RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN DATA BLOB IN/OUT NOCOPY MASK BLOB IN/OUT NOCOPY METHOD ------ MEMBER PROCEDURE DESCRIBE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM VARCHAR2 IN RESULT CLOB IN/OUT MEMBER PROCEDURE DELETEVALUE

其和ST_GEOMETRY基本上从结构上是一样的，都包括成员变量，成员函数等等。

1. 构造函数(Consturctors)

2. 属性函数

3. 编辑函数

在前面的章节中详细介绍了矢量和栅格数据存储类型的各项内容，那具体如何使用SQL来操作这两个空间存储类型，在下节中

进行详细介绍。