I'm trying and failing to do a calculation of the shortest distance for each point in a point geometry table (15000 records) to the nearest polygon edge in another table (170000 records). The end result I want is a distance for each point record to the nearest polygon record. My tables looks like this:
CREATE TABLE "brondby_adresserV2"( kvhx character varying, coordx integer, coordy integer, pk serial NOT NULL, the_geom geometry(Point,25832), dist_coast double precision, dist_lake double precision, CONSTRAINT "brondby_adresserV2_pkey" PRIMARY KEY (pk) )
CREATE TABLE soe ( gid serial NOT NULL, fot_id double precision, geom geometry(MultiPolygon,25832), CONSTRAINT soe_pkey PRIMARY KEY (gid) )
I've tried using John Barca's answer from this post: PostGIS nearest point with ST_Distance where he uses the st_dwithin so that the query uses the geometry index.
The result I get from my query pussles me and something is definately wrong. When visualising the result in qgis, it looks like the calculation is not done to all the polygons. This is the code that I've used:
update "brondby_adresserV2" set dist_lake = t.foo from ( select distinct on ("brondby_adresserV2".kvhx) "brondby_adresserV2".kvhx, "soe".fot_id, st_distance("brondby_adresserV2".the_geom, "soe".geom) as foo from "brondby_adresserV2", "soe" where ST_DWithin("brondby_adresserV2".the_geom, "soe".geom, 5000) order by "brondby_adresserV2".kvhx, "soe".fot_id, st_distance("brondby_adresserV2".the_geom, "soe".geom) ) t where "brondby_adresserV2".kvhx = t.kvhx
Can anyone guide me as to what I'm doing wrong?