PostGIS和ArcSDE的结合

来源：互联网发布：用js写的小游戏源代码编辑：程序博客网时间：2024/05/21 07:56
将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;
0 0