St_geometry 初始用

数据准备

点表

CREATE TABLE point_stgeom tablespace UBOSS_STS_WAREHOUSE as 
select car_number, longid, latid from taxispw where rownum <= 10;

 

添加空间字段

alter table point_stgeom add shape sde.st_geometry;

 

赋值

/*+parallel(t,5) */ 
update point_stgeom t set shape = sde.st_geometry(t.longid, t.latid, 0, 0,4326) ;

创建空间索引

CREATE INDEX spatialidx_point_stgeom ON point_stgeom(shape)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=1,0,0 st_srid=4326');

格网表

CREATE TABLE position_stgeom tablespace UBOSS_STS_WAREHOUSE as 
select id, longid, latid from position ;

添加空间字段

alter table position_stgeom add shape sde.st_geometry;

赋值
DECLARE
dx number;
dy number;
BEGIN
dx := 0.00013497;
dy := 0.00015508;

update position_stgeom t set shape = sde.st_geometry('POLYGON(('||(t.longid-dx)||' '||(t.latid-dy)||
','||(t.longid+dx)||' '||(t.latid-dy)||
','||(t.longid+dx)||' '||(t.latid+dy)||
','||(t.longid-dx)||' '||(t.latid+dy)||
','||(t.longid-dx)||' '||(t.latid-dy)||
'))',4326);

END;

 

创建空间索引

CREATE INDEX spatialidx_position_stgeom ON position_stgeom(shape)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=1,3,0 st_srid=4326');

查询

select atable.car_number, btable.id
from point_stgeom atable , position_stgeom btable 
where 
sde.st_intersects (atable.shape, btable.shape) = 1;