# PostGis computing the nearest point so slow ah

The two table, the first table is grid, a total of more than 28 records are as follows:
1. CREATE TABLE grid
2. (
3. id serial NOT NULL,
4. x integer,
5. y integer,
6. closestdistance double precision,
7. geom geometry(Polygon,2335),
8. CONSTRAINT grid_pkey PRIMARY KEY (id)
9. )

10. CREATE INDEX idx_grid_geom ON grid USING gist(geom);
Table second cell_p, a total of more than 2 records, as follows
1. CREATE TABLE cell_p
2. (
3. id character varying NOT NULL,
4. geom geometry(Point,2335),
5. CONSTRAINT cell_p_pkey PRIMARY KEY (id)
6. )

7. CREATE INDEX idx_cell_p_geom ON cell_p USING gist(geom);
To each point on the grid, find the nearest point in table cell_p, and use the point distance update tables in the grid closestdistance value, tried a lot of methods are very slow, the fastest update 2500 records about the need for nearly 5 minutes, seek a better way

As the fastest method I tried:
1. #distance.py
2. import psycopg2

3. def knnDistance3(geom):
4. dbname = 'postgis20'
5. conn = psycopg2.connect("dbname=%s user=postgres" % (dbname))
6. cur = conn.cursor()
7. ewkt = "'%s'::GEOMETRY" %(geom)
8. table = "cell_p"
9. sql = "SELECT ST_Distance(geom, %s) AS distance FROM %s \
10. ORDER BY geom<->%s limit 1" % (ewkt,table,ewkt)
11. cur.execute(sql)
12. return round(cur.fetchall())
1. import distance
2. import psycopg2

3. conn = psycopg2.connect("dbname=postgis20 user=postgres")
4. cur = conn.cursor()
5. cur.execute("SELECT id, ST_AsEWKT(geom) FROM public.grid order by id")
6. for id, geom in cur.fetchall():
7. l = distance.knnDistance3(geom)
8. cur.execute("UPDATE public.grid SET closestdistance=%f WHERE id=%s" % (l,id))
9. print id,l
10. conn.commit()

Started by Ann at June 08, 2016 - 12:08 AM

A little Leng Qingya

Posted by Ann at June 23, 2016 - 12:45 AM

No GIS, but this data, should not be so slow?
See your GRID table 280000 data, all of one time to the front to scan, it seems not a good idea

Posted by Harrison at July 01, 2016 - 12:51 AM