I want to import shapefiles to PostGIS using Python with arcpy & psycopg2 to connect to PostGIS.I don't want to use the existing shapefile loader nor ogr, just write my own import tool.
My steps and where I got stuck:
Any idea?
Code example:
import arcpyimport psycopg2arcpy.env.workspace = r"path\to\data"shp = "BRD.shp"shp2 = shp[:3]desc_shp = arcpy.Describe(shp)#set variables and create list wo OID and Shape attributesif desc_shp.hasOID: print desc_shp.OIDFieldName shp_OID = desc_shp.OIDFieldNameprint desc_shp.shapeFieldName shp_geom = desc_shp.shapeFieldNamefields = desc_shp.fieldsfield_names = []for field in fields: if not (field.name == shp_OID or field.name == shp_geom): field_names.append(field.name)new_list = field_namesprint new_listsc_da_cursor = arcpy.da.SearchCursor(shp,shp_geom)for sc_da_row in sc_da_cursor: print sc_da_row[0]# PostGIS part after hereuser = "postgres"pw = "postgres"db = "pypg"host = "localhost"port = 5432conn_obj = psycopg2.connect(database=db, user=user, password=pw, host=host, port=port)cur_obj = conn_obj.cursor()sql_createtable = "CREATE TABLE " + shp2 + "(" + shp_OID + " serial PRIMARY KEY," + shp_geom + " geometry," + new_list[0] + " VARCHAR(100)," + new_list[1] +" VARCHAR(100))"sql_insert = "INSERT INTO " + shp2 + "(" + shp_geom + ") VALUES(" + str(sc_da_row[0]) +")"cur_obj.execute(sql_insert)conn_obj.commit()conn_obj.close()cur_obj.close()
أكثر...
My steps and where I got stuck:
- Define shapefile
- Select column names from shapefile into a list
- Create SQL CREATE TABLE query with column names of shapefile
- Insert values from shapefile into PostGIS database
Any idea?
Code example:
import arcpyimport psycopg2arcpy.env.workspace = r"path\to\data"shp = "BRD.shp"shp2 = shp[:3]desc_shp = arcpy.Describe(shp)#set variables and create list wo OID and Shape attributesif desc_shp.hasOID: print desc_shp.OIDFieldName shp_OID = desc_shp.OIDFieldNameprint desc_shp.shapeFieldName shp_geom = desc_shp.shapeFieldNamefields = desc_shp.fieldsfield_names = []for field in fields: if not (field.name == shp_OID or field.name == shp_geom): field_names.append(field.name)new_list = field_namesprint new_listsc_da_cursor = arcpy.da.SearchCursor(shp,shp_geom)for sc_da_row in sc_da_cursor: print sc_da_row[0]# PostGIS part after hereuser = "postgres"pw = "postgres"db = "pypg"host = "localhost"port = 5432conn_obj = psycopg2.connect(database=db, user=user, password=pw, host=host, port=port)cur_obj = conn_obj.cursor()sql_createtable = "CREATE TABLE " + shp2 + "(" + shp_OID + " serial PRIMARY KEY," + shp_geom + " geometry," + new_list[0] + " VARCHAR(100)," + new_list[1] +" VARCHAR(100))"sql_insert = "INSERT INTO " + shp2 + "(" + shp_geom + ") VALUES(" + str(sc_da_row[0]) +")"cur_obj.execute(sql_insert)conn_obj.commit()conn_obj.close()cur_obj.close()
أكثر...