Error on loading SQL geography type column into Vertica

Malavika · April 2023

I was trying to convert latitude and longitude columns which is in float type to geography type in SQL and then load them into Vertica table. I used the following code to convert the columns into geography type:

_CASE WHEN (Latitude IS NOT NULL AND Longitude IS NOT NULL) THEN geography::Point(Latitude, Longitude, 4326) ELSE NULL END AS Coordinates
_
But while trying to load the data from SQL to Vertica column which is in geography type, I get the following error:

_ ERROR: COPY: Input record 1 has been rejected (Row [1] rejected due to materialized type error on column: [Coordinates] with value: [POINT (-96.8922825 33.08097)].)
_
Is the geography type in SQL and Vertica different? If so how can I convert it?

Bryan_H · April 2023

Try specifying the point as text, for example:
insert into vertica.gtemp (geog) select ST_GeographyFromText('POINT(1 2)') as geog;
You can use concatenate operator to compose a string from coordinates in other fields. Please see more examples and supported functions at https://docs.vertica.com/12.0.x/en/data-analysis/geospatial-analytics/ogc-spatial-definitions/spatial-classes/point/

We're Moving!

Create My New Community Account Now

Error on loading SQL geography type column into Vertica

Answers

Leave a Comment