Python access to PostGIS (build table, spatial index, partition table)

#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&lt ; 2500001) INHERITS (Tianditupoi_agg)) Pgiscursor.execute ("CREATE table IF not EXISTS tianditupoi_2 (check (ID >= 2500 001) 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 () #创建天地图要素分区表 and transfer data 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.fet     Chone () 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) '% (s Tring.atoi (_row[0]), "'" +geo+ "'") i = i+1 #logging. info ("Run log:%s"% (ISTR)) #pgisCursor. Execute ("INSERT INTO POI (id,name,address,geometry) VALUES (" + (_r Ow[0]) + ", '" + (_row[1]) + "', '" + (_row[1]) + "'," +ppygis. Point (_row[9], _row[10]) + ")" Pgiscursor.execute (ISTR) #一千条提交一次 if i>= 50000:pgiscon. Commit () print ("Executing ....") i = 0 #最后提交一次 pgiscon.commit () EndTime = Datetime.datetime.now ( Print ("Data import Total time:%s description"% ((endtime-starttime). seconds)) print ("Insert Complete ...") #更新空间参考ID Pgiscursor.execute ("upd Ate tianditupoi_1 Set geometry = St_setsrid (geometry,4326) ") Pgiscursor.execute (" update tianditupoi_2 set geometry = S T_setsrid (geometry,4326) ") #创建分区表空间索引 Pgiscursor.execute (" CREATE index tianditupoi2_geo_index on tianditupoi_2 usi     ng gist (geometry) ") Pgiscursor.execute (" CREATE index tianditupoi1_geo_index on tianditupoi_1 using gist (geometry) ") Print ("Spatial index Creation Complete ...") Pgiscursor.close () Pgiscon.commit () pgiscon.close () #进行数据导入froMtdtpoi2tdtpoiag ()