iamgeoknight · March 19, 2022 12:25 · Mar 19, 2022
diff --git a/import_shapefile.py b/import_shapefile.py
@@ -0,0 +1,91 @@
+import geopandas as gpd
+import psycopg2
+import psycopg2.extras as extras
+from pygeos import to_wkb, set_srid
+
+# Create connection
+def create_connection(credentials):
+    conn = psycopg2.connect(**credentials)
+    cur = conn.cursor()
+    return [conn, cur]
+
+# Close connection
+def close_connection(conn, cur):
+    conn.close()
+    cur.close()
+
+def does_table_exist(cur, name, schema):
+    """
+        Check if table exists in the database.
+    """
+    sql = f"""
+        SELECT * from pg_tables where schemaname = '{schema}' and tablename  = '{name}'
+    """
+    cur.execute(sql)
+    status = cur.fetchone()
+    if status is None:
+        return False
+    else:
+        return True
+
+def createTableFromGDF(gdf, conn, cur, name, schema, geom_name):
+    """
+        Create table based on geopandas dataframe.
+    """
+    if_exists = does_table_exist(cur, name, schema)
+    if if_exists is False:
+
+        srid = gdf.crs.to_epsg()
+        table_name = schema + "." + name
+
+        # Create a table query for geopandas file based on the columns list
+        create_table_query = 'GID SERIAL PRIMARY KEY'
+
+        for column in gdf.columns:
+            column_type = str(gdf.dtypes[column])
+            if column == geom_name:
+                create_table_query += f', {geom_name} GEOMETRY(GEOMETRY, {srid})'
+            elif column_type.find('int') != -1:
+                create_table_query += ', ' + column + ' INTEGER'
+            elif column_type.find('float') != -1:
+                create_table_query += ', ' + column + ' NUMERIC'
+            else:
+                create_table_query += ', ' + column + ' TEXT'
+
+        create_table_query = 'CREATE TABLE ' + table_name + '(' + create_table_query + ')'
+        cur.execute(create_table_query)
+        conn.commit()
+
+def to_postgis_using_psycopg2(gdf, conn, cur, name, schema = "public", geom_name="geom"):
+    """
+        Using psycopg2 to export geopandas to postgis database.
+        pygeos method to_wkb is used to convert geometries to wkb(well known binary) format. 
+        hex=true will return Hexadecimal string of the wkb which can be stored in postgis geometry column.
+        set_srid is used to set the srid of the geometries.
+    """
+    if geom_name not in gdf.columns:
+        gdf = gdf.rename(columns={gdf.geometry.name: geom_name}).set_geometry(geom_name, crs=gdf.crs)
+
+    createTableFromGDF(gdf, conn, cur, name, schema, geom_name)    
+
+    srid = gdf.crs.to_epsg()
+
+    #convert geom to wkb hex string
+    geom = to_wkb(
+        set_srid(gdf[geom_name].values.data, srid=srid), hex=True, include_srid=True
+    )
+    gdf[geom_name] = geom
+    tuples = [tuple(x) for x in gdf.to_numpy()]  
+    cols = ','.join(list(gdf.columns))
+    query = "INSERT INTO %s(%s) VALUES %%s" % (name, cols)
+    extras.execute_values(cur, query, tuples)
+    conn.commit()
+
+if __name__ == "__main__":
+    credentials = {"user":"postgres", "password":"admin", "host":"localhost", "port":"5432", "database":"postgres"}
+    [conn, cur] = create_connection(credentials)
+    counties = gpd.read_file("shps/germany_counties.shp")
+    name = "counties"
+    schema = "public"
+    to_postgis_using_psycopg2(counties, conn, cur, name, schema, geom_name="geom")
+    close_connection(conn, cur)
No results found