PostgreSQL和PostGISWGS84和CGCS2000与GCJ02和BD09坐标系与之间互转

VIP文章 cuisidong1997

已于 2023-03-05 12:51:32 修改

阅读量1.2k

点赞数

分类专栏： pycharm sql 操作系统文章标签： postgresql 数据库 sql

于 2023-03-05 12:39:34 首次发布

本文链接：https://blog.csdn.net/cuisidong1997/article/details/129344577

版权

– 如果转换后结果为null，查看geom的srid是否为4326或者4490
WGS84转GCJ02
select geoc_wgs84togcj02(geom) from test_table
GCJ02转WGS84
select geoc_gcj02towgs84(geom) from test_table

WGS84转BD09
select geoc_wgs84tobd09(geom) from test_table
BD09转WGS84
select geoc_bd09towgs84(geom) from test_table

CGCS2000转GCJ02
select geoc_cgcs2000togcj02(geom) from test_table
GCJ02转CGCS2000
select geoc_gcj02tocgcs2000(geom) from test_table

CGCS2000转BD09
select geoc_cgcs2000tobd09(geom) from test_table
BD09转CGCS2000
select geoc_bd09tocgcs2000(geom) from test_table

GCJ02转BD09
select geoc_gcj02tobd09(geom) from test_table
BD09转GCJ02
select geoc_bd09togcj02(geom) from test_table
PostgreSQL安装PostGIS扩展
复制geoc-pg-coordtansform.sql中代码，在数据库执行

CREATE OR REPLACE FUNCTION “public”.“geoc_bd09towgs84_multipolygon”(“source_geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
target_parts geometry[];
single_polygon geometry;
single_polygon_trans geometry;
final_geom geometry;

BEGIN
IF ST_GeometryType(source_geom) != ‘ST_MultiPolygon’ THEN
RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
single_polygon_trans := geoc_bd09towgs84_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;

END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_bd09towgs84_point”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
x numeric;
y numeric;
gcj_point geometry;
wgs_point geometry;

BEGIN
if st_geometrytype(geom) != ‘ST_Point’ then
return null;
end if;
x := st_x(geom);
y := st_y(geom);
if (geoc_is_in_china_bbox(x, y) = false) then
return geom;
end if;
gcj_point = geoc_bd09togcj02_point(geom);
wgs_point = geoc_gcj02towgs84_point(gcj_point);
return wgs_point;

END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_bd09towgs84_polygon”(“source_geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
target_parts geometry[];
source_npoints integer;
single_line geometry;
single_line_trans geometry;
single_polygon geometry;
final_geom geometry;

BEGIN
IF ST_GeometryType(source_geom) != ‘ST_Polygon’ THEN
RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP				
    source_npoints := ST_NPoints(single_polygon); 
    single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);  
    single_line_trans := geoc_bd09towgs84_line(single_line);  
    target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));  
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom; 
RETURN final_geom;

END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_cgcs2000tobd09”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
BEGIN
IF st_srid(geom) != ‘4490’ THEN
RETURN null;
end if;
return geoc_wgs84tobd09(st_transform(st_setsrid(geom,4490),4326));
END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_cgcs2000togcj02”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
BEGIN
IF st_srid(geom) != ‘4490’ THEN
RETURN null;
end if;
return geoc_wgs84togcj02(st_transform(st_setsrid(geom,4490),4326));
END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_delta”(“lon” numeric, “lat” numeric)
RETURNS “pg_catalog”.“jsonb” AS $BO D Y$
DECLARE
ret varchar;
dLon numeric;
dlat numeric;
radLat numeric;
magic numeric;
sqrtMagic numeric;
ee numeric;
a numeric;
BEGIN
ee := 0.006693421622965823;
a := 6378245;
dLon := geoc_transform_lon(lon - 105, lat - 35);
dLat := geoc_transform_lat(lon - 105, lat - 35);
radLat := lat / 180 * pi();
magic = sin(radLat);

magic = 1 - ee * magic * magic;

sqrtMagic := sqrt(magic);
dLon = (dLon * 180) / (a / sqrtMagic * cos(radLat) * pi());
dLat = (dLat * 180) / ((a * (1 - ee)) / (magic * sqrtMagic) * pi());
ret :='['||dLon||','||dLat||']';
return ret::jsonb;

END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_gcj02tobd09”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
BEGIN
IF st_srid(geom) != ‘4490’ and st_srid(geom) != '4326’THEN
RETURN null;
end if;
case ST_GeometryType(geom)
when ‘ST_LineString’ then
return geoc_gcj02tobd09_line(geom);
when ‘ST_MultiLineString’ then
return geoc_gcj02tobd09_multiline(geom);
when ‘ST_Point’ then
return geoc_gcj02tobd09_point(geom);
when ‘ST_MultiPoint’ then
return geoc_gcj02tobd09_multipoint(geom);
when ‘ST_Polygon’ then
return geoc_gcj02tobd09_polygon(geom);
when ‘ST_MultiPolygon’ then
return geoc_gcj02tobd09_multipolygon(geom);
ELSE
RETURN null;
END CASE;
END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_gcj02tobd09_line”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
p_p geometry;
p_t geometry;
z_t geometry;
i int;
BEGIN
i:=1;
while i <= st_npoints(geom) LOOP
p_p := st_pointn(geom,i);
p_t := geoc_gcj02tobd09_point(p_p);
geom:=st_setpoint(geom,i-1,p_t);
i:=i+1;
end LOOP;
return geom;

END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_gcj02tobd09_multiline”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
i geometry;
transform_i geometry;
multiArr geometry[];

BEGIN
multiArr:=‘{}’::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
transform_i :=geoc_gcj02tobd09_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_gcj02tobd09_multipoint”(“geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS $BO D Y$
DECLARE
i geometry;
transform_i geometry;
multiArr geometry[];

BEGIN
multiArr:=‘{}’::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
transform_i :=geoc_gcj02tobd09_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BO D Y$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION “public”.“geoc_gcj02tobd09_multipolygon”(“source_geom” “public”.“geometry”)
RETURNS “public”.“geometry” AS