3

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()
3
  • 1
    Did you "Create Postgis Extension" ? CREATE EXTENSION postgis; – WKT Apr 6 '16 at 9:32
  • 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... – WKT Apr 6 '16 at 9:41
  • Great, indeed I didn't know how to extend in postGIS. Thanks i posted the right answer – Alexis K. Apr 6 '16 at 9:48
2

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

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
  1. 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);
  1. Preparing query to create a database
sql = "create database "+ str(name_Database); 
  1. Creating a database
cursor.execute(sql)
    
print("Database created successfully, add post-GIS extension to it........")
  1. 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')

2
  • create_engine must be imported, presumably from sqlalchemy package. – Jon Jan 12 at 18:21
  • Also geopandas must be imported, and the line with gpd.GeoDataFrame() should read 'df_to_database = gpd.read_file(vector_file)' – Jon Jan 12 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.