如何指定复制自查询的列

本文介绍了如何指定复制自查询的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在下面的代码中，我想创建一个Copy-from。对于参数str，它包含epsg25832中的POINT的地理序列。我想用str中的值填充pointGeometry列。对于distanceToNearestEdge列，它必须包含计算的距离从pointGeometry中的点到特定的多边形。

在运行时，我收到错误消息：

    psycopg2.errors.SyntaxError: FEHLER:  Syntaxfehler bei »geometry«
    LINE 3:                 pointGeometry geometry(point,25832),

1-请告诉我如何指定几何类型的列2-解决了上一点的问题后，如何用该点到特定面的距离值填充distanceToNearestEdge列

样本点：变量str包含以下内容：

POINT (5630506.13939328 996474.635561104)
POINT (5630504.31893951 996495.786819386)
POINT (5630492.79190135 996473.890690898)
POINT (5630490.97145872 996495.04187886)
POINT (5630479.44442823 996473.145821843)
POINT (5630477.62399676 996494.296939486)
POINT (5630475.80349211 996515.448049693)
POINT (5630464.27655362 996493.552001265)
POINT (5630462.45606011 996514.703041153)
POINT (5630455.17335431 996599.307126336)
POINT (5630453.35249492 996620.458129037)
POINT (5630460.03044341 996387.052104826)
POINT (5630458.21032694 996408.203111568)
POINT (5630450.9291293 996492.807064196)
POINT (5630449.10864695 996513.958033765)
POINT (5630443.64676082 996577.410897854)
POINT (5630441.82598575 996598.561837676)
POINT (5630440.00513751 996619.712770058)
POINT (5630446.68298218 996386.307520502)
POINT (5630444.86287686 996407.458456925)
POINT (5630437.58172381 996492.062128279)
POINT (5630435.76125261 996513.21302753)
POINT (5630432.12009066 996555.514803724)
POINT (5630430.29939992 996576.665680666)
POINT (5630428.47863601 996597.816550169)
POINT (5630433.33553977 996385.562937327)
POINT (5630431.5154456 996406.713803432)
POINT (5630424.23433714 996491.317193515)
POINT (5630422.41387709 996512.468022449)
POINT (5630420.59334385 996533.618843946)
POINT (5630418.77273745 996554.769658008)

代码

    def executeCreateCopyTableFromFor(self,str):
    query="""
        COPY {table} (
            pointGeometry geometry(point,25832), 
            distanceToNearestEdge Float)
        FROM {str};
    """.format(table=config['Distance_To_Nearest_Edge']['copy_table_name'],str=str)
    print("query: ",query)      
    data = self.connection.execute(query,[])
    print("data: ",data)        
    return data

更新-1

现在，对于以下查询，我收到错误：

SQL Error [42P01]: FEHLER: Relation »copytabledistancesfrompointstonearestedge« existiert nicht

    COPY copyTableDistancesFromPointsToNearestEdge (
        pointGeometry)
    FROM 'POINT (5630506.13939328 996474.635561104)
POINT (5630504.31893951 996495.786819386)
POINT (5630492.79190135 996473.890690898)
POINT (5630490.97145872 996495.04187886)
POINT (5630479.44442823 996473.145821843)
POINT (5630477.62399676 996494.296939486)
POINT (5630475.80349211 996515.448049693)
POINT (5630464.27655362 996493.552001265)
POINT (5630462.45606011 996514.703041153)
POINT (5630455.17335431 996599.307126336)
POINT (5630453.35249492 996620.458129037)
POINT (5630460.03044341 996387.052104826)
POINT (5630458.21032694 996408.203111568)';

指定

如何指定复制自查询的列

问题描述

推荐答案