Error while converting WKT to Geography data type in snowflake while using jdbc to insert data

user_15265604 0
Viewed 232

I am trying getting error when inserting wkt format data to geography column using JDBC.

The code I am using is

    private static final String QUERY_SNOWFLAKE = "INSERT INTO %s " +
            "(GEOM_AS_WKB, GEOM_AS_WKT, GEOID, NAMELSAD, TRACTCE, BLKGRPCE, INSIDE_CENTROID_LATITUDE, " +
            "INSIDE_CENTROID_LONGITUDE, AREA_SQUAREMILES, X_MIN, X_MAX, Y_MIN, Y_MAX, PART_COUNT, HOLE_COUNT, STATEFP, " +
            "COUNTYFP, STUSPS, STATE, COUNTY, VINTAGE, GEOM) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
            "?, ?, ?, ?, ST_GEOGRAPHYFROMWKT(?))";

preparedStatementCENSUS_BLOCK_GROUP_GEOGRAPHY.setString(22,censusBlockGroupGeographyModel.getTheGeomText());

The error I am getting

Invalid expression [IFF(CAST(PARSE_WKT(?) AS VARIANT) IS NULL, null, OBJECT_CONSTRUCT('_shape', CAST(PARSE_WKT(?) AS VARIANT), 'version', 1, 'has_internal', TRUE, 'internal', GEOGRAPHY_COMPUTE_INTERNAL(CAST(CAST(PARSE_WKT(?) AS VARIANT) AS OBJECT))))] in VALUES clause

Anyone has any idea about this error

Thanks

snowflake-cloud-data-platform gis geospatial jdbc geometry
1 Answers

You can insert a WKT, WKB, or GeoJSON value into a GEOGRAPHY column directly without calling a parsing function. Snowflake will figure out the format of the value and attempt to parse it automatically. This should work for you:

private static final String QUERY_SNOWFLAKE = "INSERT INTO %s " +
        "(GEOM_AS_WKB, GEOM_AS_WKT, GEOID, NAMELSAD, TRACTCE, BLKGRPCE, INSIDE_CENTROID_LATITUDE, " +
        "INSIDE_CENTROID_LONGITUDE, AREA_SQUAREMILES, X_MIN, X_MAX, Y_MIN, Y_MAX, PART_COUNT, HOLE_COUNT, STATEFP, " +
        "COUNTYFP, STUSPS, STATE, COUNTY, VINTAGE, GEOM) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
        "?, ?, ?, ?, ?)";

preparedStatementCENSUS_BLOCK_GROUP_GEOGRAPHY.setString(22,censusBlockGroupGeographyModel.getTheGeomText());
user_75612440
Related