PostGIS:依托于PostgreSQL,其本身又拥有丰富的空间函数。当数据模型确定后,简单的功能运用SQL脚本,更为复杂的用存储过程加以辅助,可以比ArcEngine更为快速开发:
ArcSDE For PostgreSQL:部署教程本人整理完成,具体请下载 http://pan.baidu.com/s/1qXSrnti
1、三者共性: PostGIS,ArcSDE For PostgreSQL,ArcEngine都支持OGC标准,其数据之间的转换可以为WKT,WKB。这也是三者结合的基础。 主要函数:
PostGIS :
1. st_geometryfromtext(text, int4)
2. st_geometryfromtext(text)
3. st_asewkt(geometry)
4. st_asbinary(“public”.”geometry”)
ArcSDE For PostgreSQL:
1. st_geometry(text, int4)
2. st_astext(“sde”.”st_geometry”)
需要注意的是,PostGIS和ArcEngine在空间查询的处理上是有偏差的,ArcEngine加上了容差而PostGIS应该只是在相交的判断上加了容差,最为强大的九交模型查询是没有加容差的,这就造成了在大比例尺捕捉的情况下编辑的内容,用PostGIS和ArcEngine查询的结果不一致。为了解决这个问题可以将PostgreSQL中同一个库中同时拥有SDE模式和PostGIS模式,同时将PostGIS中关键的空间判断函数置换为SDE模式中的函数,如st_relate,st_crosses,st_touches,st_contains,st_equals,st_intersects,st_overlaps,st_within等。
将PostGIS的空间判断底层函数替换成ArcSDE
优点是跟ArcMap一致,缺点是慢
CREATE TEMP TABLE tbtemp (
oldgeom geometry,
newgeom sde.st_geometry
);
CREATE INDEX tb_temp_oldgeom_index ON tbtemp(oldgeom);
-- st_relate
-- 注意空间参考的设置
CREATE OR REPLACE FUNCTION st_relate(IN geom1 geometry, IN geom2 geometry, IN param TEXT)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.st_relate(newgeom1,newgeom2,param::cstring) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_crosses(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_crosses(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_touches(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_touches(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_contains(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_contains(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_equals(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_equals(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_intersects(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_intersects(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_overlaps(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_overlaps(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION st_within(IN geom1 geometry, IN geom2 geometry)
RETURNS BOOLEAN AS $BODY$
DECLARE
newgeom1 sde.st_geometry;
newgeom2 sde.st_geometry;
istrue BOOLEAN;
srid integer;
BEGIN
SELECT fvalue::integer INTO srid FROM pg_environment WHERE fkey='srid';
SELECT newgeom INTO newgeom1 FROM tbtemp WHERE oldgeom = geom1;
IF newgeom1 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom1),srid) INTO newgeom1;
INSERT INTO tbtemp VALUES(geom1,newgeom1);
END if;
SELECT newgeom INTO newgeom2 FROM tbtemp WHERE oldgeom = geom2;
IF newgeom2 IS NULL THEN
SELECT sde.st_geometry(st_asewkt(geom2),srid) INTO newgeom2;
INSERT INTO tbtemp VALUES(geom2,newgeom2);
END if;
SELECT sde.stf_within(newgeom1,newgeom2) INTO istrue;
RETURN istrue;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;