CODEWITHSUNDEEP
sqldatabaseetlverticasqlgeography
Malavika N
Malavika N

Reputation: 1

Error on loading SQL geography type column into Vertica

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?

Upvotes: 0

Views: 99

Answers (1)

marcothesane
marcothesane

Reputation: 6749

I would use the Vertica functions as you can find them in the docu.

For me, it worked like this:

WITH indata(latitude,longitude) AS (
            SELECT -96.8922825, 33.08097
  UNION ALL SELECT NULL       , 33.08097
  UNION ALL SELECT -96.8922825, NULL
)
-- transform to geography type ...
,to_geo(geo) AS (
  SELECT
    ST_GeographyFromText(
      'POINT('||latitude::VARCHAR(16)||' '||longitude::VARCHAR(16)||')'
    )
  FROM indata
)
-- transform back to well-known-text ...
SELECT ST_AsText(geo) FROM to_geo;
-- out           ST_AsText                                                                                                                                                                              
-- out ------------------------------
-- out  POINT (-96.8922825 33.08097)
-- out  (null)
-- out  (null)

You don't need a CASE WHEN expression. If one operand is NULL, the result is NULL, too. So just work with:

    ST_GeographyFromText(
      'POINT('||latitude::VARCHAR(16)||' '||longitude::VARCHAR(16)||')'
    )

Upvotes: 0

Related Questions