时间戳

首页 > 解决方案 > 如何为 Copy From 查询指定列

python - 如何为 Copy From 查询指定列

问题描述

在下面的代码中，我想创建一个副本。对于参数str，它包含 epsg25832 中POINT的几何图形。我想pointGeometry用来自的值填充列str。对于列，它必须包含从点到特定多边形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)';

标签： pythonpostgresqlpostgis

解决方案

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

您必须使用CREATE TABLE来创建表格，然后您可以使用COPY.

推荐阅读