CODEWITHSUNDEEP
amazon-redshift
QDex
QDex

Reputation: 153

Insert data in a column geometry on redshift

I created this table on a database in redshift; and try to insert data. Do you know how to insert the point coordinate into the column geometry ?

CREATE TABLE airports_data (
    airport_code character(3),
    airport_name character varying,
    city character varying,
    coordinates geometry,
    timezone timestamp with time zone
  );


INSERT INTO airports_data(airport_code,airport_name,city,coordinates,timezone) 
VALUES ('YKS','Yakutsk Airport','129.77099609375, 62.093299865722656', 'AsiaYakutsk');

I had an error when trying to make this insert.

Query ELAPSED TIME: 13 m 05 s ERROR: Compass I/O exception: Invalid hexadecimal character(s) found

Upvotes: 1

Views: 1147

Answers (1)

dxwell
dxwell

Reputation: 71

In Redshift, make your longitude and latitude values into a geometry object. Use:

    ST_Point(longitude, latitude) -- for an XY point
    
    ST_GeomFromText('LINESTRING(4 5,6 7)') -- for other geometries

You're missing city in your INSERT VALUES and 'AsiaYakutsk' is not a valid datetime value - see https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timestamptz

Ignoring your timezone column and adding city into values, use this:

INSERT INTO airports_data(airport_code,airport_name,city,coordinates)
VALUES ('YKS','Yakutsk Airport','Yakutsk',ST_Point(129.77099609375, 62.093299865722656));

Upvotes: 1

Related Questions