psycopg2 not working because column “geom” does not exist

Question

I was planning to post this question on Stack Overflow before realizing that it probably was more related to my poor knowledge of GIS (I'm an absolute beginner in GIS and my knowledge of Python is moderate).

(config: pgAdmin 4, PostgreSQL 10)

I'm trying to use the solution provided in this repository but I have the following error:

File "C:\Users\user\anaconda\Anaconda3\lib\site-packages\pyris\api\extract.py", line 47, in _query cu.execute(q, params) psycopg2.ProgrammingError: column "geom" does not exist

Where the 47th line is located within this piece of code:

def _query(q, params=None):
    """Carry out a SQL query

    Only fetch one result
    """
    Logger.debug("processing query '%s'", q)
    with psycopg2.connect(database="pyris",
                          user=DATABASE['USER']) as cnx:
        with cnx.cursor() as cu:
            if params is not None:
                cu.execute(q, params)
            else:
                cu.execute(q)
            return cu.fetchall()

I assume it comes from the fact that my table (created from this file) only has the following columns:

gid, depcom, nom_com, iris, dcomiris, nom_iris, typ_iris, origine

I tried to manually add a "geom" column whose type would be "geometry" while the value would be "4326" but I'm not even sure I should use that number (as I said, I'm a noob).

Thus I first added a column "geom" with "geometry" type but the program is now throwing me this error:

File "C:\Users\user\anaconda\Anaconda3\lib\site-packages\pyris\api\app.py", line 105, in get res.update(coord) AttributeError: 'list' object has no attribute 'update'

Located in here:

@api.route("/search/")
class IrisFromAddress(Resource):
    @api.doc(parser=address_parser,
             description="Look for an IRIS for a specific address.")
    @api.marshal_with(address_fields, envelope='address')
    def get(self):
        args = address_parser.parse_args()
        query = args['q']
        Logger.info("Look for IRIS for address '%s'", address)
        coord = address.coordinate(query)
        Logger.info("Get coordinate (%s, %s)", coord["lon"], coord["lat"])
        Logger.info("For address '%s'", coord["address"])
        if coord['address'] is None:
            return []
        res = extract.iris_from_coordinate(coord['lon'], coord['lat'])
        res.update(coord)
        return res

And when I try on pgAdmin to add a value to that column, I always get the same error no matter what I try:

ALTER TABLE geoiris
set geom = 4326

ALTER TABLE geoiris
set geom = '4326'

ALTER TABLE geoiris
set geom = cast(4326 as geometry)

Which all result in:

ERROR: syntax error at or near "geom"

Long story short, the more I try to find a solution, the more I get confused. Which is why I'm requesting your help.

EDIT:

I loaded my file through PostGIS Shapefile Import/Export Manager. And, in "Options...", I didn't select the "Load only attribute (dbf) data" option. Which -- I think -- is the right thing to do because it "strips the geometry column from the loading process, leaving just the attribute columns" according to this website.

Answer 1

I finally managed to solve my issue by creating the following extension:

CREATE EXTENSION postgis_topology;

It now works fine. Thank you all!

Answer 2

To add a geometry column you would run:

ALTER TABLE geoiris add column geom geometry(Point, 4326);