How to create viewonly field in SqlAlchemy and GeoAlchemy2?

602 views Asked by At

I am trying to dynamically populate a field using SqlAlchemy 0.8.4 and GeoAlchemy2 0.2.2. The goal is to assign a District to the Facility based on the facility's position when it is read from the database. The code looks like this:

class District(Base):
    __tablename__ = 'districts'
    id = Column(Integer, primary_key=True)
    geom = Column('geog', Geometry(geometry_type='POLYGON'), nullable=False)

class Facility(Base):
    __tablename__ = 'facilities'
    id = Column(Integer, primary_key=True)
    pos = Column('geog', Geometry(geometry_type='POINT'), nullable=True)
    district = relationship(District,
                            viewonly=True,
                            primaryjoin="District.geom.ST_Contains(Facility.pos)",
                            foreign_keys=[District.id])

But this gives me the following error:

ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'ST_Contains(districts.geog, facilities.geog)' on relationship Facility.district. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation

I would really like to avoid having foreignkey relation between these classes since the districts are constantly changing, but would like to get a facility with a district set without querying the database again.

How should I solve this?

0

There are 0 answers