--读取要素的几何信息（按照WKT描述）
select ST_AsText(geom) from dzzg where bsm=2715;

select ST_AsText(geom) from ckyd where bsm=420;

--求出dzzg.bsm为2715的要素和cykd.bsm为420的要素的相交部分

select ST_Intersection(ST_GeomFromText(ST_astext(a.geom)),ST_GeomFromText(ST_astext(b.geom)))

FROM dzzg a INNER JOIN ckyd b on ST_Intersects(ST_GeomFromText(ST_astext(a.geom)),ST_GeomFromText(ST_astext(b.geom)))

where a.bsm=2715 and b.bsm=420

//ST_Intersection表示求交

//红色部分表示判断相交 on ST_Intersects

--success

--选出dzzg和ckyd两个图层的相交部分

--只选择前5个 不然需要很多时间

SELECT b.bsm As bbsm, p.bsm As pbsm,

        ST_Intersection(ST_GeomFromText(ST_AsText(b.geom)), ST_GeomFromText(ST_AsText(p.geom))) As intersect_bp

    FROM dzzg b INNER JOIN ckyd p ON ST_Intersects(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom)))

    --WHERE ST_Overlaps(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom)))

    LIMIT 5

--从dzzg层中选出满足以下条件的要素，并显示相交部分

--1、标识码大于2000 [感觉有部分的geom值有问题，所以限定一下搜索范围]

--2、与ckyd中标识码为420的面相交

select  a.bsm,

st_astext(st_intersection(st_geomfromtext(st_astext(a.geom)),st_geomfromtext(st_astext(b.geom)))) as intersection

from dzzg a inner join ckyd b ON ST_Intersects(ST_GeomFromText(ST_AsText(a.geom)),ST_GeomFromText(ST_AsText(b.geom)))

where b.bsm=420 and a.bsm>2000

--查询与已知面相交的图形

SELECT bsm FROM dzzg where

ST_Intersects(

ST_GeomFromText('POLYGON((102.463 24.873,102.465 24.872,102.463 24.872,102.463 24.873))'), ST_GeomFromText(ST_AsText(geom))) and bsm>2700

--创建表

create table test(id1 int4,id2 int4);

select addgeometrycolumn('public','test','shape',4610,'POLYGON',2); //添加几何字段，4610表示坐标系，2表示二维要素

SELECT b.bsm As bbsm, p.bsm As pbsm,

        ST_AsText(ST_Intersection(ST_GeomFromText(ST_AsText(b.geom)), ST_GeomFromText(ST_AsText(p.geom)))) As intersect_bp

    FROM dzzg b INNER JOIN ckyd p ON ST_Intersects(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom)))

    LIMIT 3

--插入记录
--函数st_geomfromtext 表示从“WKT描述”构建几何图形

insert into test(id1,id2,shape)

values(

    21,699,st_geomfromtext('POLYGON((102.481862440151 24.9381407958162,

    102.480064171822 24.9380431117968,102.480009386288 24.9381143096393,

    102.481862440151 24.9381407958162))',4610)

);--删除已知表

drop table ckyd