Can't insert a POINT geometry value

[davegriffin]

I'm sure this more of a documentation issue rather than a code one, but I cannot find any examples of how to encode a POINT value in an INSERT statement. (Issue #234 doesn't explicitly state how this is resolved.) Every variation I have tried returns:

message: 'Cannot get geometry object from data you send to the GEOMETRY field',
code: 'ER_CANT_CREATE_GEOMETRY_OBJECT',

Installed: [email protected]

I have tried using the x,y object:

row {
  city: 'Abbot Springs',
  state: 'AL',
  county: 'Shelby',
  latitude: 33.36083,
  longitude: 86.48167,
  location: { x: 33.36083, y: 86.48167 }
}

and I have tried using the POINT string:

row {
  city: 'Abbot Springs',
  state: 'AL',
  county: 'Shelby',
  latitude: 33.36083,
  longitude: 86.48167,
  location: 'POINT(33.36083,86.48167)' 
}

Destination schema is:

CREATE TABLE sccom_geocodes (
    id                  int(11) NOT NULL AUTO_INCREMENT,
    city                varchar(100) NOT NULL,
    state               varchar(50),
    county              varchar(100),
    latitude            float NOT NULL,
    longitude           float NOT NULL,
    location            point NOT NULL,
    PRIMARY KEY (id),
    SPATIAL KEY sccom_geocodes_loc_index (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I would be happy to update the docs with details on GEOMETRY columns.

Thank you.

[a613]

The solution I found was to embed the POINT() syntax into the placeholder statement:

const location = {
  latitude: 45,
  longitude: -80,
};
const [result] = await db.query<ResultSetHeader>(
  'INSERT INTO `events` (user_id, dt, coordinate) VALUES (?, NOW(), POINT(?, ?))',
  [userId, location.longitude, location.latitude],
);

Remember that latitude is north/south, so it is the y value, and longitude is east/west so is x.

[pihentagy]

Nice, and could you insert 2 points in bulk? It drives me nuts that I cannot :-(

[sidorares]

@a613 @pihentagy linking my answer to a related issue #1244 (comment)

[sidorares]

@davegriffin see linked answer. If you feel that it should be explicitly mentioned in the documentation feel free to open a PR to start discussion, closing this for now