Python accesses PostGIS (Table creation, spatial index, partition table), pythonpostgis

Python accesses PostGIS (Table creation, spatial index, partition table), pythonpostgis

# Encoding: UTF-8 _ author _ = 'admin' import psycopg2import ppygisimport datetimeimport stringimport sysimport loggingimport GeowayLoggerreload (sys) # Chinese error sys. setdefaultencoding ("UTF-8") vLog = GeowayLogger. geowayLogger ("c: // geoway2.log", logging. DEBUG) vLog. start () # postgis # pgisCon = psycopg2.connect (database = "postgis_21_sample", user = "s", password = "") pgisCon = psycopg2.connect (database = "postgis_21_sample ", user = "postgres", password = "postgres", host = "192.98.12.60", port = "5432 ") pgisCursor = pgisCon.cursor()pgisCursor.exe cute ("create table if not exists tianditupoi_partition (id integer primary key, geometry GEOMETRY) "export pgiscursor.exe cute (" create table if not exists tianditupoi_1 (check (id> = 0 and id <2500001) INHERITS (tianditupoi_its) "export pgiscursor.exe cute (" create table if not exists tianditupoi_2 (check (id> = 2500001) INHERITS (tianditupoi_ute) "Export cute (" create index partition on tianditupoi_1 (id) "export pgiscursor.exe cute (" create index tianditupoi_2_idindex on tianditupoi_2 (id) "Export cute (" create rule partition as on insert to tianditupoi_partition WHERE (id> = 0 and id <2500001) do instead insert into tianditupoi_1 VALUES (NEW. id, NEW. geometry) "Maid (" create rule tianditupoi_insert_2 as on insert to tianditupoi_where (id> = 2500001) do instead insert into tianditupoi_2 VALUES (NEW. id, NEW. geometry) ") pgisCon. commit () # create a world graph element partition table and transfer data to def fromTdtPoi2TdtPoiAg (): # SQL _txt = sqlite3.connect ("c: // POI. tdb ") startTime = datetime. datetime. now (); cusor = pgisCon. cursor () cusor.exe cute ("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_values (id, geometry) values (% d, % s) '% (string. atoi (_ row [0]), "'" + geo + "'") I = I + 1 # logging.info ("Running log: % s" % (istr )) export pgiscursor.exe cute ("insert into poi (id, name, address, geometry) values (" + (_ row [0]) + ", '" + (_ row [1]) + "','" + (_ row [1]) + "'," + ppygis. point (_ row [9], _ row [10]) + ") pgisCursor.exe cute (istr) # One thousand submit once if I> = 50000: pgisCon. commit () print ("executing .... ") I = 0 # last submitted pgisCon. commit () endTime = datetime. datetime. now () print ("total data import time: % s description" % (endTime-startTime ). seconds) print ("inserted... ") # to update a space, refer to ID pgisCursor.exe cute (" update tianditupoi_1 set geometry = ST_SetSRID (geometry, 4326) ") pgisCursor.exe cute (" update Character set geometry = ST_SetSRID) ") # create a partition table space index pgisCursor.exe cute (" create index partition on tianditupoi_2 using gist (geometry) ") pgisCursor.exe cute (" create index partition on tianditupoi_1 using gist (geometry )") print ("space index created... ") pgisCursor. close () pgisCon. commit () pgisCon. close () # import data fromTdtPoi2TdtPoiAg ()

 

I used postgis to create a new database with four tables, which I created at the command prompt. Now I want to use PGadmin to check whether

Check your permission settings.