- 论坛徽章:
- 0
|
本帖最后由 zhn158 于 2013-05-31 17:55 编辑
两张表,第一张表是grid,共28万多条记录如下:- CREATE TABLE grid
- (
- id serial NOT NULL,
- x integer,
- y integer,
- closestdistance double precision,
- geom geometry(Polygon,2335),
- CONSTRAINT grid_pkey PRIMARY KEY (id)
- )
- CREATE INDEX idx_grid_geom ON grid USING gist(geom);
复制代码 第二张表cell_p, 共2万多条记录,结构如下- CREATE TABLE cell_p
- (
- id character varying NOT NULL,
- geom geometry(Point,2335),
- CONSTRAINT cell_p_pkey PRIMARY KEY (id)
- )
- CREATE INDEX idx_cell_p_geom ON cell_p USING gist(geom);
复制代码 要对表grid中的每个点,在表cell_p中找出最近的点,并用到该点的距离更新表grid 中closestdistance的值,试了很多中方法都很慢,最快的更新2500条记录差不多需要近5分钟,求教更好的办法
我试过的最快的方法如下:- #distance.py
- import psycopg2
- def knnDistance3(geom):
- dbname = 'postgis20'
- conn = psycopg2.connect("dbname=%s user=postgres" % (dbname))
- cur = conn.cursor()
- ewkt = "'%s'::GEOMETRY" %(geom)
- table = "cell_p"
- sql = "SELECT ST_Distance(geom, %s) AS distance FROM %s \
- ORDER BY geom<->%s limit 1" % (ewkt,table,ewkt)
- cur.execute(sql)
- return round(cur.fetchall()[0][0])
复制代码- import distance
- import psycopg2
- conn = psycopg2.connect("dbname=postgis20 user=postgres")
- cur = conn.cursor()
- cur.execute("SELECT id, ST_AsEWKT(geom) FROM public.grid order by id")
- for id, geom in cur.fetchall():
- l = distance.knnDistance3(geom)
- cur.execute("UPDATE public.grid SET closestdistance=%f WHERE id=%s" % (l,id))
- print id,l
- conn.commit()
复制代码 |
|