How do you create a table in mysql using SQLalchemy with a spatial column?

Question

I am trying to create a table using SQLalchemy and geoalchemy2 like so:

class RLocModel(Base):
    __tablename__ = 'rloc'
    id = Column(Integer, primary_key=True)
    loc = Column(Geometry('POINT'))                     

This is against a mysql database (actaully a AWS mysql compatible Aurora database).

I get an exception as follows:

(_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(POINT,-1), \n\tPRIMARY KEY (id)\n)' at line 3") [SQL: '\nCREATE TABLE rloc (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tloc geometry(POINT,-1), \n\tPRIMARY KEY (id)\n)\n\n']

I am not sure it is expressing the correct dialect.

I can do this manually as so:

CREATE TABLE `geo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loc` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `loc` (`loc`)
) ENGINE=InnoDB AUTO_INCREMENT=11905 DEFAULT CHARSET=latin1;

Any ideas?

Answer 1

According to documentation:

Note: GeoAlchemy 2 doesn’t currently support other dialects than PostgreSQL/PostGIS.

What you can do is to create the custom Point type:

from sqlalchemy import func
from sqlalchemy.types import UserDefinedType

class Point(UserDefinedType):
    def get_col_spec(self):
        return "POINT"

    def bind_expression(self, bindvalue):
        return func.ST_GeomFromText(bindvalue, type_=self)

    def column_expression(self, col):
        return func.ST_AsText(col, type_=self)

Now, you just use this custom type inside your model:

class RLocModel(Base):
    __tablename__ = 'rloc'
    id = Column(Integer, primary_key=True)
    loc = Column(Point, nullable=False)

Then you can use your model like this:

geo = RLocModel()
geo.loc = 'POINT(0 0)'

Answer 2

in case anyone is looking for SRID support, below is a modified version of @adrihanu's answer with support.

from sqlalchemy import func
from sqlalchemy.types import UserDefinedType

class Point(UserDefinedType):
    def get_col_spec(self):
        return "POINT SRID 4326"

    def bind_expression(self, bindvalue):
        return func.ST_GeomFromText(bindvalue, 4326, type_=self)

    def column_expression(self, col):
        return func.ST_AsText(col, type_=self)

The rest is the same

class RLocModel(Base):
    __tablename__ = 'rloc'
    id = Column(Integer, primary_key=True)
    loc = Column(Point, nullable=False)

In use

geo = RLocModel()
geo.loc = 'POINT(40.7647919738352, -73.99207372979897)'