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()

• WKT: Did you "Create Postgis Extension" ? CREATE EXTENSION postgis
• WKT: Are you working with postgres database as your conn_string shows? Try to create a database "mydb" and create the postgis extension. After that try to run your sql. If it works, go to python...
• Alexis K.: Great, indeed I didn't know how to extend in postGIS. Thanks i posted the right answer

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')

• Jon: create_engine must be imported, presumably from sqlalchemy package.
• Jon: Also geopandas must be imported, and the line with gpd.GeoDataFrame() should read 'df_to_database = gpd.read_file(vector_file)'