ArcGIS Python中如何通过CSV Upsert LineString？遇几何识别问题

核心问题修复：CSV几何类型识别错误 #

ArcGIS Online的gis.content.analyze默认自动识别几何类型时，若CSV中线要素的WKT格式或列名无明确标识，会被误判为Point。解决关键是输出标准WKT格式+手动指定几何类型：

步骤1：调整PostgreSQL SQL输出标准线WKT #

修改SQL语句，将ST_MakeLine生成的几何转换为WKT格式，确保几何列存储LINESTRING(x1 y1, x2 y2)格式的字符串：

CASE 
    WHEN round(earth_distance(ll_to_earth(w.latitude::double precision, w.longitude::double precision), ll_to_earth(base.bh_latitude::double precision, base.bh_longitude::double precision)) * 3.28084::double precision) < 35000::double precision 
    THEN ST_AsText(ST_MakeLine(ST_SetSRID(ST_MakePoint(w.longitude::double precision, w.latitude::double precision), 4326), ST_SetSRID(ST_MakePoint(base.bh_longitude::double precision, base.bh_latitude::double precision), 4326))) 
END AS line_geometry

步骤2：调用gis.content.analyze时手动指定几何类型 #

不要依赖自动识别，通过参数明确指定线要素类型和空间参考，避免误判：

from arcgis.gis import GIS

gis = GIS("home")
csv_item = gis.content.add({"type": "CSV"}, data="your_line_data.csv")

# 手动指定几何类型为线要素
analysis = gis.content.analyze(
    item=csv_item,
    file_type="csv",
    item_properties={
        "type": "Feature Service",
        "geometryType": "esriGeometryPolyline",
        "spatialReference": {"wkid": 4326}
    }
)

# 发布要素服务
feature_layer_item = csv_item.publish(analyze_result=analysis)

更简便的Upsert方案：直接更新预定义图层 #

若已在ArcGIS Online中创建线要素图层，可跳过文件格式转换和发布步骤，直接用FeatureLayer.edit_features执行Upsert，无需额外系统库，完美适配MWAA环境：

实现代码示例 #

from arcgis.gis import GIS
from arcgis.features import FeatureLayer
import pandas as pd
from sqlalchemy import create_engine

# 1. 连接PostgreSQL获取数据
engine = create_engine("postgresql://user:password@host:port/dbname")
query = """
SELECT 
    your_id_column,
    attr1, attr2,  -- 替换为你的属性列
    CASE 
        WHEN round(earth_distance(ll_to_earth(w.latitude::double precision, w.longitude::double precision), ll_to_earth(base.bh_latitude::double precision, base.bh_longitude::double precision)) * 3.28084::double precision) < 35000::double precision 
        THEN ST_AsText(ST_MakeLine(ST_SetSRID(ST_MakePoint(w.longitude::double precision, w.latitude::double precision), 4326), ST_SetSRID(ST_MakePoint(base.bh_longitude::double precision, base.bh_latitude::double precision), 4326))) 
    END AS line_geometry
FROM your_table
"""
df = pd.read_sql(query, engine)

# 2. 连接ArcGIS Online并获取目标线图层
gis = GIS("home")
target_layer = FeatureLayer("https://your-agol-portal-url/arcgis/rest/services/your_line_layer/FeatureServer/0", gis=gis)

# 3. 构造upsert用的features数组
features = []
for _, row in df.iterrows():
    if pd.isna(row["line_geometry"]):
        continue
    feature = {
        "attributes": {
            "your_id_column": row["your_id_column"],
            "attr1": row["attr1"],
            "attr2": row["attr2"]
        },
        "geometry": {"wkt": row["line_geometry"]}
    }
    features.append(feature)

# 4. 执行Upsert
result = target_layer.edit_features(updates=features)
print(f"更新成功: {result['updateResults'][0]['success']}")

方案适配性说明 #

• 无需安装GDAL、OGR等系统依赖，仅依赖ArcGIS API for Python和pandas（MWAA默认支持或可通过requirements.txt安装）
• 跳过中间文件转换，避免Shapefile列名截断、GeoJSON字符限制等问题
• 直接操作现有图层，效率远高于分批对比标识符的方案

内容的提问来源于stack exchange，提问作者Mojo713