informix gis 数据处理

来源：互联网发布：摄氏度转华氏度 c语言编辑：程序博客网时间：2024/06/05 06:17

/**********************************************************************************************、
informix gis 数据处理
/**********************************************************************************************、
一 Oracle Spatial中SDO_Geometry详细说明
1 Oracle Spatial 定义的SDO_GEOMETRY类型为：

CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER, //前面字符串为字段名；后面字符串为字段类型
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);
其中sdo_geometry AS OBJECT ，标识该类型为对象类型。开始我们可以想想它为ArcObjects中的Geometry对象（本来要素的shape字段中的对象就是Geometry），
而不要理解他是怎么样组织的。至于该类型中的SDO_POINT_TYPE、SDO_ELEM_INFO_ARRAY、SDO_ORDINATE_ARRAY也是Oracle Spatial自定义的类型和 sdo_geometry 是一样的。

现在对sdo_geometry 类型中的各个参数简单的介绍：

1、SDO_GTYPE ：表示要存储的几何类型，如点线面。它是通过 NUMBER类型来表达的；

2、SDO_SRID ：几何的空间参考坐标系，类型也为 NUMBER；

3、SDO_POINT ：如果几何类型点类型的话，就是存储点坐标，否则为空。oracle自定义的SDO_POINT_TYPE类型；

4、SDO_ELEM_INFO ：定义要如何理解SDO_ORDINATES中的坐标串的；

5、SDO_ORDINATES ：存储实际坐标的，以X、Y以及不同点之间都是逗号隔开；

下面将详细介绍这些字段参数的含义

一、SDO_GTYPE

SDO_GTYPE 值是有四位数字组成的，它们的格式为：dltt 。

其中，d 表示几何的维数。如二维、三维对应的d=2和d=3；

l 定义了LRS。一般l=0；

tt 定义了地理对象的类型。现在使用从00到07，如tt=01代表为单点；

SDO_GTYPE 2001 代表单点POINT 类型

二、SDO_SRID

SDO_SRID定义了空间坐标参考系统。如果SDO_SRID为null，则没有指定坐标系统，如果SDO_SRID不为null，
那么它的值必须在MDSYS.CS_SRS 表中的 SRID 列有对应的值，而且它的值必须插入USER_SDO_GEOM_METADATA视图中
MDSYS.CS_SRS 表： SRID 空间参考ID
CS_NAME 坐标系统名称
SQL> select cs_name from MDSYS.CS_SRS where srid=4326;

CS_NAME
--------------------------------------------------------------------------------
WGS 84

二 informix spatial 说明
1 informix 的坐标系
> select srid,description from spatial_references where srid=4326;

srid description

4326 EPSG 4326, version 8.1.1

2 informix spatial 功能加载(以root 用户执行)
[root@xxgkdb1 gbase]# tar -xvf spatial8.21.FC7.tar -C /home/informix/gbase/extend/

echo "VPCLASS bts,num=1">>$INFORMIXDIR/etc/$ONCONFIG

数据库重新启动后，可以创建空间类型的表（而且系统中自动建立了17张与空间有关的表）

2 ST_Point 函数
ST_Point(X double precision, Y double precision, SRID integer)

三如何将 ORACLE 中的数据导入到 INFORMIX 中
1 oracle 端：
1）表结构
create table HB_SJCJ_QY_JCXX_DIAN
(
OBJECTID VARCHAR2(50),
SHAPE MDSYS.SDO_GEOMETRY,
OBJECTTYPE VARCHAR2(50)
)
;
2）具体数据如下：
SQL> select shape from HB_SJCJ_QY_JCXX_DIAN;

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.48823, 39.87494, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.39039, 39.85949, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.37185, 39.85296, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.40378, 39.89828, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.37185, 39.85296, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.37185, 39.85296, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.45905, 39.89279, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.4412, 39.91751, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.40378, 39.89828, NULL), NULL, NULL)

3）创建中间表
> create table a(
OBJECTID VARCHAR2(50) ,GTYPE VARCHAR2(50), SRID VARCHAR2(50),
POINT_x VARCHAR2(50), POINT_y VARCHAR2(50), POINT_z VARCHAR2(50),
OBJECTTYPE VARCHAR2(50));

4）创建存储过程
create or replace procedure get_shape
is
v_OBJECTID varchar2(50);
v_shape MDSYS.SDO_GEOMETRY;
v_objecttype varchar2(50);
v_SDO_GTYPE varchar2(50);
v_SDO_SRID varchar2(50);
v_SDO_POINT_x varchar2(50);
v_SDO_POINT_y varchar2(50);
v_SDO_POINT_z varchar2(50);
v_SDO_POINT SDO_POINT_TYPE;
begin

for v_tab in(select OBJECTID,shape ,OBJECTTYPEfrom HB_SJCJ_QY_JCXX_DIAN) loop
v_shape:=v_tab.shape;
v_SDO_GTYPE:=v_shape.SDO_GTYPE;
v_SDO_SRID:=v_shape.SDO_SRID;
v_SDO_POINT:=v_shape.SDO_POINT;
v_SDO_POINT_x:=v_SDO_POINT.x;
v_SDO_POINT_y:=v_SDO_POINT.y;
v_SDO_POINT_z:=v_SDO_POINT.z;
insert into a values(v_tab.OBJECTID,v_SDO_GTYPE,v_SDO_SRID,v_SDO_POINT_x,v_SDO_POINT_y,
v_SDO_POINT_z,v_tab.OBJECTTYPE);
end loop;
commit;
end;
/

5）执行存储过程，将数据导入到中间表
truncate table a;
exec get_shape();
select OBJECTID,GTYPE,SRID,POINT_x,POINT_Y from a;

objectid D157D75BD42B47F5A59AB5ADAC9A6EF0
gtype 2001
srid 4326
point_x 116.40034
point_y 39.90343

295 row(s) retrieved.

2 INFORMIX 端建立中间表
create table a(
OBJECTID VARCHAR(50) ,GTYPE VARCHAR(50), SRID VARCHAR(50),
POINT_x VARCHAR(50), POINT_y VARCHAR(50), POINT_z VARCHAR(50),
OBJECTTYPE VARCHAR(50));

3 通过KETTLE 将数据导入到INFORMIX 端

4 INFORMIX 端将数据从中间表导入到正式表
insert into HB_SJCJ_QY_JCXX_DIAN
select OBJECTID,ST_Point( POINT_x, POINT_y,4326),OBJECTTYPE from a;

5 3d,4d 数据如何写入
例子：UPDATE points_of_interest SET thepoint_lonlat = PointFromText('POINT(' || longitude || ' ' || latitude || ')',4326)
1) 将一个表中的 x,y.z,m 坐标写入空间表
-- 创建空间表
create table a
(
OBJECTID LVARCHAR(50),
SHAPE ST_Geometry,
OBJECTTYPE LVARCHAR(50)
)
;

--存有x,y.z,m 坐标的表
create table b(x decimal(10,2),y decimal(10,2),z decimal(10,2),m decimal(10,2));

--x,y,z,m 坐标数据
insert into b values(112,44,111,2);
insert into b values(132.4,59,222,3);
insert into b values(172.56,86,444,5);
insert into b values(152,44.9,666,7);

> select * from b;

x y z m

112.00 44.00 111.00 2.00
132.40 59.00 222.00 3.00
172.56 86.00 444.00 5.00
152.00 44.90 666.00 7.00

4 row(s) retrieved.

--x,y 坐标数据
> select st_PointFromText('POINT (' || x || ' ' || y ||')',4326) from b;

(expression) 4326 POINT (112 44)

(expression) 4326 POINT (132.4 59)

(expression) 4326 POINT (172.56 86)

(expression) 4326 POINT (152 44.9)

4 row(s) retrieved.

--x,y,z 坐标数据
> select st_PointFromText('POINT z(' || x || ' ' || y ||' ' ||z ||')',4326) from b;

(expression) 4326 POINT Z (112 44 111)

(expression) 4326 POINT Z (132.4 59 222)

(expression) 4326 POINT Z (172.56 86 444)

(expression) 4326 POINT Z (152 44.9 666)

4 row(s) retrieved.

4 row(s) retrieved.

--x,y,z,m 坐标数据

> select st_PointFromText('POINT zm(' || x || ' ' || y ||' ' ||z ||' ' ||m ||')',4326) from b;

(expression) 4326 POINT ZM (112 44 111 2)

(expression) 4326 POINT ZM (132.4 59 222 3)

(expression) 4326 POINT ZM (172.56 86 444 5)

(expression) 4326 POINT ZM (152 44.9 666 7)

4 row(s) retrieved.

--将x,y 空间数据写入
insert into a select 'aa',st_PointFromText('POINT(' || x || ' ' || y || ')',4326),'dfadf' from b;
> select * from a;

objectid aa
shape 4326 POINT (112 44)
objecttype dfadf

objectid aa
shape 4326 POINT (132.4 59)
objecttype dfadf

objectid aa
shape 4326 POINT (172.56 86)
objecttype dfadf

objectid aa
shape 4326 POINT (152 44.9)
objecttype dfadf

4 row(s) retrieved.

--将x,y,z 空间数据写入
> insert into a select 'aa',st_PointFromText('POINT z(' || x || ' ' || y || ' ' || z ||')',4326),'dfadf' from b;
objectid aa
shape 4326 POINT Z (112 44 111)
objecttype dfadf

objectid aa
shape 4326 POINT Z (132.4 59 222)
objecttype dfadf

objectid aa
shape 4326 POINT Z (172.56 86 444)
objecttype dfadf

objectid aa
shape 4326 POINT Z (152 44.9 666)
objecttype dfadf

8 row(s) retrieved.

--将x,y,z,m 空间数据写入
> insert into a select 'aa',st_PointFromText('POINT zm(' || x || ' ' || y || ' ' || z ||' ' || m ||')',4326),'dfadf' from b;
objectid aa
shape 4326 POINT ZM (112 44 111 2)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (132.4 59 222 3)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (172.56 86 444 5)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (152 44.9 666 7)
objecttype dfadf

12 row(s) retrieved.

--空间表数据
> select * from a;

objectid aa
shape 4326 POINT (112 44)
objecttype dfadf

objectid aa
shape 4326 POINT (132.4 59)
objecttype dfadf

objectid aa
shape 4326 POINT (172.56 86)
objecttype dfadf

objectid aa
shape 4326 POINT (152 44.9)
objecttype dfadf

objectid aa
shape 4326 POINT Z (112 44 111)
objecttype dfadf

objectid aa
shape 4326 POINT Z (132.4 59 222)
objecttype dfadf

objectid aa
shape 4326 POINT Z (172.56 86 444)
objecttype dfadf

objectid aa
shape 4326 POINT Z (152 44.9 666)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (112 44 111 2)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (132.4 59 222 3)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (172.56 86 444 5)
objecttype dfadf

objectid aa
shape 4326 POINT ZM (152 44.9 666 7)
objecttype dfadf

--得到空间数据（点）的X,Y,Z,M 坐标值

> select st_x(shape::st_point),st_y(shape::st_point),st_z(shape::st_point),st_m(shape::st_point) from a;

(expression) (expression) (expression) (expression)

112.0000000000 44.00000000000
132.4000000000 59.00000000000
172.5600000000 86.00000000000
152.0000000000 44.90000000000
112.0000000000 44.00000000000 111.0000000000
132.4000000000 59.00000000000 222.0000000000
172.5600000000 86.00000000000 444.0000000000
152.0000000000 44.90000000000 666.0000000000
112.0000000000 44.00000000000 111.0000000000 2.000000000000
132.4000000000 59.00000000000 222.0000000000 3.000000000000
172.5600000000 86.00000000000 444.0000000000 5.000000000000
152.0000000000 44.90000000000 666.0000000000 7.000000000000

12 row(s) retrieved.

--根据条件取得空间数据（点）的X,Y,Z,M 坐标值
> select st_x(shape::st_point),st_y(shape::st_point),st_z(shape::st_point),st_m(shape::st_point) from a where st_x(shape::st_point)>150;

(expression) (expression) (expression) (expression)

172.5600000000 86.00000000000
152.0000000000 44.90000000000
172.5600000000 86.00000000000 444.0000000000
152.0000000000 44.90000000000 666.0000000000
172.5600000000 86.00000000000 444.0000000000 5.000000000000
152.0000000000 44.90000000000 666.0000000000 7.000000000000

6 row(s) retrieved.

--增加一条多边型空间数据

> INSERT INTO a VALUES('1111',ST_PolyFromText('polygon((10.01 20.03,10.52 40.11,30.29 41.56,31.78 10.74,10.01 20.03))',4326),'111');

objectid aa
shape 4326 POINT ZM (152 44.9 666 7)
objecttype dfadf

objectid 1111
shape 4326 POLYGON ((10.01 20.03, 31.78 10.74, 30.29 41.56, 10.52 40.11,
10.01 20.03))
objecttype 111

13 row(s) retrieved.

--取得空间坐标的 x,y,z,m 的最大，最小值（此值是每个空间对象，每行的最大最小值）
>select st_minx(shape) minx,st_miny(shape) miny,st_minz(shape) minz,st_minm(shape) minm,st_maxx(shape) maxx,st_maxy(shape) maxy,st_maxz(shape) maxz,st_maxm(shape) maxm from a;
minx 152.0000000000
miny 44.90000000000
minz 666.0000000000
minm 7.000000000000
maxx 152.0000000000
maxy 44.90000000000
maxz 666.0000000000
maxm 7.000000000000

minx 10.01000000000
miny 10.74000000000
minz
minm
maxx 31.78000000000
maxy 41.56000000000
maxz
maxm

13 row(s) retrieved.

--取得空间坐标的 x 的最小值（此值是每个空间对象，每行的最小值）

> select st_minx(shape) minx from a;

minx

112.0000000000
132.4000000000
172.5600000000
152.0000000000
112.0000000000
132.4000000000
172.5600000000
152.0000000000
112.0000000000
132.4000000000
172.5600000000
152.0000000000
10.01000000000

13 row(s) retrieved.

--取得所有空间对象的最小X 值
> select min(st_minx(shape)) minx from a;

minx

10.01000000000

1 row(s) retrieved.

--取得SRID
> select st_srid(shape) maxx from a;

maxx

4326
4326
4326
4326
4326
4326
4326
4326
4326
4326
4326
4326
4326

13 row(s) retrieved.

--将空间对象用TEXT 形式存放到表bb 中　（如果为多类型，导入到空间表时会发生从错误，如同时存在点和多边型。需要按不同类型导入）
create table bb
(
OBJECTID LVARCHAR(50),
SHAPE LVARCHAR(32000),
OBJECTTYPE LVARCHAR(50)
)
;

> insert into bb select objectid ,st_astext(shape)::lvarchar(32000),objecttype from a;

13 row(s) inserted.

> select * from bb;

objectid aa
shape POINT (112 44)
objecttype dfadf

objectid aa
shape POINT (132.4 59)
objecttype dfadf

objectid aa
shape POINT (172.56 86)
objecttype dfadf

objectid aa
shape POINT (152 44.9)
objecttype dfadf

objectid aa
shape POINT Z (112 44 111)
objecttype dfadf

objectid aa
shape POINT Z (132.4 59 222)
objecttype dfadf

objectid aa
shape POINT Z (172.56 86 444)
objecttype dfadf

objectid aa
shape POINT Z (152 44.9 666)
objecttype dfadf

objectid aa
shape POINT ZM (112 44 111 2)
objecttype dfadf

objectid aa
shape POINT ZM (132.4 59 222 3)
objecttype dfadf

objectid aa
shape POINT ZM (172.56 86 444 5)
objecttype dfadf

objectid aa
shape POINT ZM (152 44.9 666 7)
objecttype dfadf

objectid 1111
shape POLYGON ((10.01 20.03, 31.78 10.74, 30.29 41.56, 10.52 40.11, 10.01
20.03))
objecttype 111

13 row(s) retrieved.

--将bb 表中的数据导入到空间数据表cc
create table cc
(
OBJECTID LVARCHAR(50),
SHAPE ST_Geometry,
OBJECTTYPE LVARCHAR(50)
)
;

--导入点数据
insert into cc select OBJECTID,ST_PointFromText(shape,4326), OBJECTTYPE from bb;

--导入多边型数据
insert into cc select OBJECTID,ST_PolyFromText(shape,4326), OBJECTTYPE from bb;

6 附录：空间数据表名
> > select tabname from systables where tabid>99;

tabname sysblderrorlog

tabname sysbldobjects

tabname sysbldobjdepends

tabname sysbldobjkinds

tabname sysbldregistered

tabname sysbldirequired

tabname sysbldiprovided

tabname bld_registered

tabname bldi_required

tabname bldi_provided

tabname spatial_references

tabname geometry_columns

tabname spatial_ref_sys

tabname se_metadatatable

tabname st_units_of_measure

tabname se_views

tabname spatial_references_expand

17 row(s) retrieved.

0 0