将其他坐标类型的坐标转化为墨卡托坐标_pgsql geom转墨卡托平面坐标系-优快云博客

本文链接：https://blog.youkuaiyun.com/Ling_Ze/article/details/136364866

文章讲述了如何在数据库操作中，根据`project_info`表中的`coord_type`查询结果，判断并进行WGS-84、GCJ-02、墨卡托等坐标类型的转换，最终更新`project_info`表的`coord_type`为墨卡托，并更新几何坐标值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

# 1.读取project_info表中的coord_type类型
        geometry_type = self.pg_op.query(sql=f"select coord_type from {PG_MANAGE_PROJECT_INFO} where"
                                         f" project_id = '{project_id}'")[0]['coord_type']
        # 2.判断geometry_type的坐标类型
        if geometry_type == "WGS-84" or geometry_type == "GCJ-02":
            strid = 4326

        elif geometry_type == "墨卡托":
            strid = 3857

        elif geometry_type == "CGCS2000":
            strid = 4490
        else:
            raise ValueError('坐标系类型选择参数为非法参数，%s' % geometry_type)

        # 3.将点表和线表的geom坐标转化为墨卡托坐标，并将project_info表中的coord_type数据更新为墨卡托
        if strid != 3857:
            self.pg_op.update(sql=f"UPDATE dt_node_test SET geom = ST_SetSRID(geom, {strid}) WHERE "
                                  f"projectid = projectid='{project_id}' AND ST_SRID(geom) not in ({strid}, 3857);")
            self.pg_op.update(sql=f"UPDATE dt_pipeline_test SET geom = ST_SetSRID(geom, {strid}) WHERE "
                                  f"projectid = projectid='{project_id}' AND ST_SRID(geom) not in ({strid}, 3857);")

            self.pg_op.update(sql=f"UPDATE dt_node_test SET geom = ST_Transform(ST_SetSRID(geom, {strid}), 3857) WHERE"
                                  f" projectid = '{project_id}' AND ST_SRID(geom) = {strid};")
            self.pg_op.update(sql=f"UPDATE dt_pipeline_test SET geom = ST_Transform(ST_SetSRID(geom, {strid}), 3857) WHERE"
                                  f" projectid = '{project_id}' AND ST_SRID(geom) = {strid};")

            # 将project_info表中的coord_type数据更新为墨卡托
            self.pg_op.query(sql=f"UPDATE {PG_MANAGE_PROJECT_INFO} SET coord_type = '墨卡托' WHERE "
                                 f"project_id = '{project_id}'")