如何指定复制自查询的列

问题描述

在下面的代码中，我想创建一个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)';

---

解决方案

COPY不创建表，因此您不在列名列表中指定数据类型。

您必须使用CREATE TABLE来创建表，然后您可以使用COPY填充它。