CODEWITHSUNDEEP
pythonpython-3.xpostgresqlgeoalchemy2geoalchemy
Tiago_Albuquerque
Tiago_Albuquerque

Reputation: 13

How to insert a Geometry with geoalchemy2 and postgresql

I'm trying to insert a geometry data into database but it's not working. I created a model class for Geometry, but i don't now if it's right.

Geometry class:

from datetime import datetime

from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import DateTime
from sqlalchemy import Float
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import SMALLINT
from sqlalchemy import BIGINT
from sqlalchemy import String
from sqlalchemy import DateTime
from geoalchemy2 import Geometry as Geom
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Geometry(Base):
    __tablename__ = "geometry"

    id = Column(Integer, primary_key=True)
    fill_color = Column(String(7))
    stroke_color = Column(String(7))
    geom = Column(Geom(geometry_type=None)) # here my Geometry column 
    type = Column(String(20))
    area = Column(BIGINT, nullable=False)
    lengh = Column(BIGINT, nullable=False)
    creation_date = Column(DateTime, nullable=False)
    stroke_width = Column(SMALLINT, nullable=False)
    tn = Column(Integer, nullable=False)
    vegetation_index = Column(Integer)

Insert code:

for index, line in data[['Shape_Area', 'Shape_Leng', 'TN', 
  'geometry']].iterrows():
  geometry = line['geometry']
  area = line['Shape_Area']
  lengh = line['Shape_Leng']
  tn = line['TN']
  now = datetime.now()
  
  geom = Geometry(geom='POLYGON((0 0,1 0,1 1,0 1,0 0))', 
    type=geometry.type, area=area, lengh=lengh, creation_date=now, tn=tn)

self._session.add(geom)
self._session.commit()

Error:

sqlalchemy.exc.ProgrammingError

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_geomfromewkt(unknown) does not exist
LINE 1: ..._width, tn, vegetation_index) VALUES (NULL, NULL, ST_GeomFro...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: INSERT INTO geometry (fill_color, stroke_color, geom, type, area, lengh, creation_date, stroke_width, tn, vegetation_index) VALUES (%(fill_color)s, %(stroke_color)s, ST_GeomFromEWKT(%(geom)s), %(type)s, %(area)s, %(lengh)s, %(creation_date)s, %(stroke_width)s, %(tn)s, %(vegetation_index)s) RETURNING geometry.id]
[parameters: {'fill_color': None, 'stroke_color': None, 'geom': 'POLYGON((0 0,1 0,1 1,0 1,0 0))', 'type': 'MultiPolygon', 'area': 699322.6710989687, 'lengh': 3855.587947253079, 'creation_date': datetime.datetime(2020, 11, 4, 17, 25, 37, 943407), 'stroke_width': None, 'tn': '000.000', 'vegetation_index': None}]
(Background on this error at: http://sqlalche.me/e/13/f405)

I want to include any type of geometry, not just polygon

Upvotes: 1

Views: 3701

Answers (1)

Rennan Paloschi
Rennan Paloschi

Reputation: 36

Geometry is a column type, try another name

class myTableWithGeom(Base):

__tablename__ = "myTableWithGeom"

id = Column(Integer, primary_key=True)
fill_color = Column(String(7))
stroke_color = Column(String(7))
geom = Column(Geometry(geometry_type='POLYGON')) # here your Geometry column 
type = Column(String(20))
area = Column(BIGINT, nullable=False)
lengh = Column(BIGINT, nullable=False)
creation_date = Column(DateTime, nullable=False)
stroke_width = Column(SMALLINT, nullable=False)
tn = Column(Integer, nullable=False)
vegetation_index = Column(Integer)

geom_test = myTableWithGeom()

There is no need to instantiate an Geometry object here, do just

geom_test.geom = 'POLYGON((0 0,1 0,1 1,0 1,0 0))'

Then you can add and commit

self._session.add(geom)
self._session.commit()

Upvotes: 0

Related Questions