Create Table with postGIS geometry in Python

Question

I'm creating the following table with a postGIS geometry column in python but get an error psycopg2.ProgrammingError: type "geometry" does not exist What should I use to be able to use postGIS in python?

import psycopg2

def main():
        conn_string = "host='localhost' dbname='postgres' user='postgres' password='postgres'"
        conn = psycopg2.connect(conn_string)
        conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        cursor = conn.cursor()


        createTable = """  CREATE TABLE map
                        (
                            id INT PRIMARY KEY DEFAULT NULL,
                            geom GEOMETRY DEFAULT NULL,
                        );
                        """
        cursor.execute(createTable)
        conn.commit()
        cursor.close()
        conn.close()
if __name__ == "__main__":
    main()

Answer 1

to do so you only need to add the postgis extension.

cursor.execute("CREATE EXTENSION postgis;")

and then create the table

createTable = """  CREATE TABLE map
                        (
                            id INT PRIMARY KEY DEFAULT NULL,
                            geom GEOMETRY DEFAULT NULL,
                        );
                        """
        cursor.execute(createTable)

Answer 2

To create spatial database in postgreSQL with postGIS extension

A. 1st create a non-spatial database

1. establishing the connection

conn = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432')

conn.autocommit = True

2. Creating a cursor object using the cursor() method

cursor = conn.cursor()

post_gis_database_name = 'assingn_any_name'

name_Database   = str(post_gis_database_name);

3. Preparing query to create a database

sql = "create database "+ str(name_Database); 

4. Creating a database

cursor.execute(sql)
    
print("Database created successfully, add post-GIS extension to it........")

5. Closing the connection

conn.close()

B. 2nd step is to add postgis extension to database created above

def main():
    conn1 = psycopg2.connect(dbname=name_Database, user='postgres', password='your_password', host='localhost', port= '5432')
    conn1.autocommit = True
    cursor = conn1.cursor()
    cursor.execute("CREATE EXTENSION postgis;")
    conn1.commit()
    cursor.close()
    conn1.close()

if __name__ == "__main__":
    main()

C. 3rd is to write spatial-data in the tables of the database created above

engine_url = "postgresql://username:password@localhost:5432/"+name_Database

engine = create_engine(engine_url)

vector_file = 'gdf_final/ or any shapefile path/ or geopandas dataframe'

df_to_database = gpd.GeoDataFrame(vector_file)

df_to_database.to_postgis(con=engine, name='give_table_name', if_exists = 'replace')