Point type in sqlalchemy?

Question

I found this regarding Point type in Postgres: http://www.postgresql.org/docs/current/interactive/datatype-geometric.html

Is there the SQLAlchemy version of this?

I am storing values in this manner: (40.721959482, -73.878993913)

Fat

Answer 1

I found out it's a slight modification of Mohammad's answer.

Yes I need to add a point column via geo/sqlalchemy

from sqlalchemy import Column
from geoalchemy2 import Geometry
# and import others

class Shop(db.Model):
    # other fields
    coordinates = Column(Geometry('POINT'))

On my PostGIS/Gres and PGloader side, since I'm loading from a csv that formats my latitude and longitude points as: "(40.721959482, -73.878993913)" I needed to do a macro in vim for all of my entries (there's a lot) in order to force that column to adhere to how points are created in PostGIS, so I turned the csv column into point(40.721959482 -73.878993913), then upon table creation set the location column with the datatype as geometry(point) location geometry(point).

Fat

Clarification: This is only true if you have the PostGIS extension enabled. SQLAlchemy or GeoAlchemy2 don't seem to support the Point type in Posgres without the extension – Undulant

AArias's answer below should be the accepted as this requires an additional library that could bloat your codebase – Rag

Could I use the Geometry class in Alembic migrations, or do I need to write custom SQL for migrations? For example, can I do this: op.create_table('gps', sa.Column('coordinates', Geometry('POINT'), nullable=False), ...) – Mab

Answer 2

You can use geoalchemy2 whis is an extension to sqlalchemy and can be used with flask-sqlalchemy too.

from sqlalchemy import Column
from geoalchemy2 import Geometry
# and import others

class Shop(db.Model):
    # other fields
    coordinates = Column(Geometry('POINT'))

Works

In Posgresql would the datatype be Point, or will it be geometry(Point)? I can load in my csv file no problem if I set the column Location to Point datatype (Location Point) upon table creation. When I do Location geometry(Point) (I was looking at PostGIS documentation) when I try to load the csv I get an error: Database error XX000: parse error - invalid geometry HINT: "(4" <-- parse error at position 2 within geometry CONTEXT: COPY crime_data_nyc, line 1, column location: "(40.6227027620001" CSV file has the Location column data as "(40.8037530600001, -73.955861904)" – Fat

Amend last comment: Point in postgresql as Location's datatype while doing location = db.Column(Geometry(geometry_type='POINT') results in ProgrammingError: (psycopg2.ProgrammingError) function st_asewkb(point) does not exist [...] HINT: No function matches the given name and argument types. You might need to add explicit type casts. So I assume this is what needs to happen: Postgresql in create table creates Location column as: CREATE TABLE Blah( Location geometry(Point)); and in geoalchemy the type would be location = db.Column(Geometry(geometry_type='POINT')). – Fat

ERROR: geoalchemy2 0.12.3 has requirement SQLAlchemy>=1.4, but you'll have sqlalchemy 1.3.24 which is incompatible. – Incontrovertible

Answer 3

You can extend UserDefinedType to achieve what you want.

Here's an example I found that gets pretty close to what you want subclassing UserDefinedType

Note that Mohammad Amin's answer is valid only if your point is intended to be a geographic point (latitude and longitude constraints). It doesn't apply if you want to represent any point on a plane. Also, in that case you would need to install the PostGIS extension, which I encourage if you are working with geography points as it provides a lot of utlities and extra functions.

Rockwell

If you don't want to add the PostGIS extension, this is closest to the right answer. Only issue is that the example is not specifically for the type Point – Undulant

Answer 4

Here's a working example to use a native PostgreSQL point type in SQLAlchemy without needing PostGIS or other extensions, based on the suggestions made in the other answers:

import sqlalchemy


@dataclass(eq=True, frozen=True, slots=True)
class Coordinate:
    """
    Container to hold a geolocation.
    """

    lat: float
    lng: float


class LatLngType(sqlalchemy.types.UserDefinedType):
    """
    Custom SQLAlchemy type to handle POINT columns.

    References:

    - https://gist.github.com/kwatch/02b1a5a8899b67df2623
    - https://docs.sqlalchemy.org/en/14/core/custom_types.html#sqlalchemy.types.UserDefinedType  # noqa
    """

    # Can do because we made the Coordinate dataclass hashable.
    cache_ok = True

    def get_col_spec(self):
        return "POINT"

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

    def bind_processor(self, dialect):
        """
        Return function to serialize a Coordinate into a database string literal.
        """

        def process(value: Coordinate | Tuple[float, float] | None) -> str | None:
            if value is None:
                return None

            if isinstance(value, tuple):
                value = Coordinate(*value)

            return f"({value.lat},{value.lng})"

        return process

    def result_processor(self, dialect, coltype):
        """
        Return function to parse a database string result into Python data type.
        """

        def process(value: str) -> Coordinate | None:
            if value is None:
                return None

            lat, lng = value.strip("()").split(",")

            return Coordinate(float(lat), float(lng))

        return process

Use it as follows:

class MyModel(Base):
    location = Column(LatLngType)

Flabellum

When you're calling func.POINT inside bind_expression, where is the name func coming from? Trying to figure out how this code works, and not sure where you're getting that. – Calendre

@Calendre It's from sqlalchemy.func, I made that explicit now... – Flabellum