Using st_distance to calculate shortest distance between a table with point geometry to a table with multipolygon geometry

Question

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

and this:

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?

Answer 1

With your query above you will always calculate the shortest distance to the "first" lake within a 5 kilometer distance of your addresses.

"First" is here been determined by the values of the fot_id column.

I would use a statement like this where the Order By statement doesn't use soe.fot_id:

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, st_distance("brondby_adresserV2".the_geom, "soe".geom)
) t
where "brondby_adresserV2".kvhx = t.kvhx

If that doesn't work how about using an aggregation instead? I guess now is the time for me to admit that I don't have PostGIS installed :-)

update "brondby_adresserV2" set dist_lake = t.foo
from
(
select "brondby_adresserV2".kvhx, Min(st_distance("brondby_adresserV2".the_geom, "soe".geom)) as foo
from "brondby_adresserV2", "soe"
where ST_DWithin("brondby_adresserV2".the_geom, "soe".geom, 5000)
Group by "brondby_adresserV2".kvhx
) t
where "brondby_adresserV2".kvhx = t.kvhx