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

Source: Internet
Author: User
Tags postgis

#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 () 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.