Python访问PostGIS(建表、空间索引、分区表)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

#encoding: utf-8 __author__ = 'Administrator' import psycopg2 import ppygis import datetime import string import sys import logging import GeowayLogger reload(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()