基于postgis实现坐标转换的几个函数

1. 度分秒转换为小数

CREATE OR REPLACE FUNCTION "public"."translate_coor"("degree" varchar)
  RETURNS "pg_catalog"."numeric" AS $BODY$
DECLARE data varchar[];
BEGIN
  if like(tude,'%°%') then 
		SELECT (regexp_split_to_array(tude, '[°′''"″]')) into data;
		return CAST(data[1] as numeric) + CAST(data[2] as numeric) / 60 + CAST(data[3] as numeric)/3600;
	else 
	  return cast(tude as numeric);
	end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 填充地理信息字段

通过表已知的xy坐标转换为geom字段

CREATE OR REPLACE FUNCTION "public"."fill_geom"("tbl" varchar, "x_field" varchar, "y_field" varchar, "srid" int4=4326)
  RETURNS "pg_catalog"."int4" AS $BODY$
BEGIN
  if(srid = 0) THEN
		srid = 4326;
	end if;
  EXECUTE format('update %s set geom=st_geomfromewkt(''srid='|| srid || ';point(''||translate_coor(%s)||'' '' || translate_coor(%s)|| '')'');', tbl,x_field,y_field);
	return 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
1
2
3
4
5
6
7
8
9
10
11
12

• tbl:表名称
• x_field:经度的字段名称
• y_field:维度的字段名称
• srid：选填，int4,默认为4326

3. 触发器实现数据插入自动计算geom

CREATE OR REPLACE FUNCTION "public"."fun_insert_meteorological"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
  if((new.lon ~ '^\d+°(\d+[′''](\d+(\.\d+)?[″"])?)?$' and new.lat ~ '^\d+°(\d+[′''](\d+(\.\d+)?[″"])?)?$') or ( new.lon ~ '^\d+(\.\d+)?$' and new.lat ~ '^\d+(\.\d+)?$')) then 
		new.geom=st_geomfromewkt('srid=4326;point(' || translate_coor(new.lon) || ' ' || translate_coor(new.lat) || ')');
	end if;
	return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
1
2
3
4
5
6
7
8
9
10
11