如何编程导出Oracle注释表含ELEMENT列至SHP文件

问题根源 #

SHP格式的DBF文件本身不支持BLOB类型字段，默认导出工具会自动忽略这类字段，这是格式层面的限制，而非工具bug。要保留ELEMENT列，必须先将BLOB数据转换为DBF支持的类型（比如Base64编码的字符串）再写入SHP，导入时反向转换即可。

可行编程方案 #

方案1：Python + cx_Oracle + pyshp

通过将BLOB转为Base64字符串，存入SHP的文本字段，步骤如下：

import cx_Oracle
import shapefile
import base64
from shapely.wkb import loads

# 数据库连接配置
dsn = cx_Oracle.makedsn("your_host", your_port, service_name="your_service")
conn = cx_Oracle.connect(user="your_user", password="your_pass", dsn=dsn)
cursor = conn.cursor()

# 查询数据：几何转WKB，同时获取BLOB及其他属性
cursor.execute("""
    SELECT 
        SDO_UTIL.TO_WKBGEOMETRY(GEOM) AS wkb_geom,
        ELEMENT,
        attr1, attr2  -- 替换为你的实际属性字段
    FROM YOUR_ANNOTATION_TABLE
""")

# 初始化SHP写入器
w = shapefile.Writer("annotation_backup")
# 添加属性字段：ELEMENT转为Base64文本（长度根据实际BLOB大小调整）
w.field("ATTR1", "C", size=50)
w.field("ATTR2", "N", size=10)
w.field("ELEMENT_BASE64", "C", size=8000)

# 遍历数据写入SHP
for row in cursor:
    wkb_geom, element_blob, attr1, attr2 = row
    # 解析WKB为几何对象（根据实际几何类型调整，比如点/线/面）
    geom = loads(wkb_geom)
    w.point(geom.x, geom.y)  # 若为其他几何类型，改用w.line()/w.poly()
    # BLOB转Base64字符串
    element_base64 = base64.b64encode(element_blob.read()).decode('utf-8')
    # 写入属性
    w.record(attr1, attr2, element_base64)

# 清理资源
w.close()
conn.close()

导入还原步骤：读取SHP的ELEMENT_BASE64字段，用base64.b64decode()解码为二进制数据，再插入Oracle的ELEMENT BLOB列。

方案2：GeoTools自定义字段转换（Java）

针对之前GeoTools导出失败的情况，通过自定义FeatureType将BLOB转为Base64字符串后写入SHP：

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.FeatureSource;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.geotools.feature.simple.SimpleFeatureBuilder;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;

import java.io.File;
import java.util.Base64;
import java.util.HashMap;
import java.util.Map;

public class AnnotationShpExporter {
    public static void main(String[] args) throws Exception {
        // Oracle连接参数
        Map<String, Object> oracleParams = new HashMap<>();
        oracleParams.put("dbtype", "Oracle");
        oracleParams.put("host", "your_host");
        oracleParams.put("port", your_port);
        oracleParams.put("database", "your_service");
        oracleParams.put("user", "your_user");
        oracleParams.put("passwd", "your_pass");

        DataStore oracleStore = DataStoreFinder.getDataStore(oracleParams);
        FeatureSource<SimpleFeatureType, SimpleFeature> featureSource = oracleStore.getFeatureSource("YOUR_ANNOTATION_TABLE");
        FeatureCollection<SimpleFeatureType, SimpleFeature> features = featureSource.getFeatures();

        // 构建新FeatureType：替换BLOB字段为Base64字符串字段
        SimpleFeatureType originalType = featureSource.getSchema();
        SimpleFeatureTypeBuilder builder = new SimpleFeatureTypeBuilder();
        builder.setName(originalType.getName());
        builder.setCRS(originalType.getCoordinateReferenceSystem());

        for (int i = 0; i < originalType.getAttributeCount(); i++) {
            String attrName = originalType.getDescriptor(i).getLocalName();
            if ("ELEMENT".equals(attrName)) {
                builder.add("ELEMENT_BASE64", String.class);
            } else {
                builder.add(originalType.getDescriptor(i));
            }
        }
        SimpleFeatureType newType = builder.buildFeatureType();

        // 创建Shapefile存储并写入数据
        File shpFile = new File("annotation_backup.shp");
        ShapefileDataStore shpStore = new ShapefileDataStore(shpFile.toURI().toURL());
        shpStore.createSchema(newType);

        try (FeatureIterator<SimpleFeature> iterator = features.features()) {
            SimpleFeatureBuilder featureBuilder = new SimpleFeatureBuilder(newType);
            while (iterator.hasNext()) {
                SimpleFeature originalFeature = iterator.next();
                featureBuilder.init(originalFeature);
                // BLOB转Base64
                byte[] elementBlob = (byte[]) originalFeature.getAttribute("ELEMENT");
                if (elementBlob != null) {
                    String base64Str = Base64.getEncoder().encodeToString(elementBlob);
                    featureBuilder.set("ELEMENT_BASE64", base64Str);
                }
                SimpleFeature newFeature = featureBuilder.buildFeature(originalFeature.getID());
                shpStore.getFeatureWriter().write(newFeature);
            }
        }

        // 释放资源
        oracleStore.dispose();
        shpStore.dispose();
    }
}

注意事项 #

1. 字段长度限制：DBF文本字段的最大长度取决于版本（dBase III为254，dBase IV为65535），若BLOB编码后超过长度，需先压缩BLOB再编码，或拆分字段存储。
2. 几何类型适配：根据Annotation表的实际几何类型调整代码中的几何写入逻辑（比如注记可能关联点/线，需确保转换正确）。
3. 导入还原：必须对应编写导入逻辑，将Base64字符串解码回BLOB后写入Oracle，才能完整恢复数据。

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