高效的使用空间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 sdeESRI ArcSDE Server Setup Utility Wed Feb  9 10:06:29 2011----------------------------------------------------------------Install or update ST_RASTER schema objects: Are you sure? (Y/N): YCreating ST_RASTER schema...C:/DOCUME~1/ZHAOGU~1/LOCALS~1/Temp: No such file or directorySuccessfully 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_PIXELDATAMETHOD------ 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 CLOBMETHOD------ MEMBER FUNCTION DESCRIBE RETURNS CLOB Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM                          VARCHAR2                IN    METHOD------ MEMBER FUNCTION BUILDPYRAMID RETURNS ST_RASTERMETHOD------ MEMBER FUNCTION BUILDPYRAMID RETURNS ST_RASTER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM                          VARCHAR2                IN    METHOD------ MEMBER FUNCTION BUILDSTATS RETURNS ST_RASTERMETHOD------ MEMBER FUNCTION BUILDSTATS RETURNS ST_RASTER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- LEVEL                          NUMBER                  IN    METHOD------ MEMBER FUNCTION DELETESTATS RETURNS ST_RASTERMETHOD------ MEMBER FUNCTION DELETECOLORMAP RETURNS ST_RASTERMETHOD------ 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 NUMBERMETHOD------ MEMBER FUNCTION HASSTATS RETURNS NUMBERMETHOD------ MEMBER FUNCTION GETCOMPRESSIONTYPE RETURNS VARCHAR2METHOD------ MEMBER FUNCTION GETINTERPOLATIONTYPE RETURNS VARCHAR2METHOD------ MEMBER FUNCTION GETINTERLEAVETYPE RETURNS VARCHAR2METHOD------ MEMBER FUNCTION GETPIXELTYPE RETURNS VARCHAR2METHOD------ MEMBER FUNCTION GETPYRAMIDLEVEL RETURNS NUMBERMETHOD------ MEMBER FUNCTION GETPIXELDATA RETURNS ST_PIXELDATAMETHOD------ MEMBER FUNCTION GETPIXELDATA RETURNS ST_PIXELDATA Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- PARAM                          VARCHAR2                IN    METHOD------ STATIC FUNCTION GETVERSION RETURNS NUMBERMETHOD------ 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 NOCOPYMETHOD------ 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来操作这两个空间存储类型，在下节中

进行详细介绍。