Python訪問PostGIS(建表、空間索引、分區表)

標籤：blog   os   資料   for   ar   art   div   amp   

#encoding: utf-8__author__ = ‘Administrator‘import psycopg2import ppygisimport datetimeimport stringimport sysimport loggingimport GeowayLoggerreload(sys)#中文錯誤sys.setdefaultencoding( "utf-8" )vLog = GeowayLogger.GeowayLogger("c://geoway2.log",logging.DEBUG)vLog.start()#postgis#pgisCon = psycopg2.connect(database="postgis_21_sample",user="postgres",password="")pgisCon = psycopg2.connect(database="postgis_21_sample",user="postgres",password="postgres",host="192.98.12.60",port="5432")pgisCursor = pgisCon.cursor()pgisCursor.execute("create table IF NOT EXISTS  tianditupoi_agg(id integer PRIMARY KEY,geometry GEOMETRY)")pgisCursor.execute("create table IF NOT EXISTS  tianditupoi_1( check (id >= 0 and id< 2500001) ) INHERITS (tianditupoi_agg)")pgisCursor.execute("create table IF NOT EXISTS  tianditupoi_2( check (id >= 2500001) ) INHERITS (tianditupoi_agg)")#建立分區表ID索引pgisCursor.execute("CREATE INDEX tianditupoi_1_idindex on tianditupoi_1(id)")pgisCursor.execute("CREATE INDEX tianditupoi_2_idindex on tianditupoi_2(id)")#建立分區規則pgisCursor.execute("CREATE RULE tianditupoi_insert_1 AS ON INSERT TO tianditupoi_agg WHERE (id >= 0 and id < 2500001) DO INSTEAD INSERT INTO tianditupoi_1 VALUES (NEW.id,NEW.geometry)")pgisCursor.execute("CREATE RULE tianditupoi_insert_2 AS ON INSERT TO tianditupoi_agg WHERE (id >= 2500001 ) DO INSTEAD INSERT INTO tianditupoi_2 VALUES (NEW.id,NEW.geometry)")pgisCon.commit()#建立天地圖要素分區表，並將資料進行轉移def fromTdtPoi2TdtPoiAg():     #sql_txt = sqlite3.connect("c://POI.tdb")     startTime = datetime.datetime.now();     cusor = pgisCon.cursor()     cusor.execute("select oid,st_astext(geom) as geometry from tianditupoi")     #row = cusor.fetchone()     i = 0;     for _row in cusor.fetchall():        #print _row        #_p = {"name":_row[1],"address":_row[2],"loc":[_row[9],_row[10]]}        #poi.insert(_p)        geo ="%s"%(_row[1])        istr = ‘insert into tianditupoi_agg(id,geometry)values(%d,%s)‘%(string.atoi(_row[0]),"‘"+geo+"‘")        i = i+1        #logging.info("作業記錄：%s"%(istr))        #pgisCursor.execute("insert into poi(id,name,address,geometry)values("+(_row[0])+",‘"+(_row[1])+"‘,‘"+(_row[1])+"‘,"+ppygis.Point(_row[9], _row[10])+")")        pgisCursor.execute(istr)        #一千條提交一次        if i>= 50000 :            pgisCon.commit()            print("執行中....")            i = 0    #最後提交一次     pgisCon.commit()     endTime = datetime.datetime.now()     print("資料匯入總耗時：%s描述"%((endTime - startTime).seconds))     print("插入完畢...")     #更新空間參考ID     pgisCursor.execute("update tianditupoi_1 set geometry = ST_SetSRID(geometry,4326)")     pgisCursor.execute("update tianditupoi_2 set geometry = ST_SetSRID(geometry,4326)")     #建立分區資料表空間索引     pgisCursor.execute("create index tianditupoi2_geo_index on tianditupoi_2 using gist(geometry)")     pgisCursor.execute("create index tianditupoi1_geo_index on tianditupoi_1 using gist(geometry)")     print("空間索引建立完畢...")     pgisCursor.close()     pgisCon.commit()     pgisCon.close()#進行資料匯入fromTdtPoi2TdtPoiAg()