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()[0][0])
  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