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

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

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
(
) t
``````

Can anyone guide me as to what I'm doing wrong?

• I don't think you should include the "soe".fot_id in your Order By statement. That will always give you the shortest distance to the first "SOE" record Sep 18, 2015 at 8:46
• or `"brondby_adresserV2".kvhx` Sep 18, 2015 at 8:48
• Also possible @MichaelStoner. But in this case I think I wan't the shortest distance from each address (brondby_adresserV2) to any lake (soe) Sep 18, 2015 at 8:53
• removing "soe".fot_id doesn't change anything, and the "select distinct on" function needs "brondby_adresserV2".kvhx as the first input in the order by clause Sep 18, 2015 at 8:56
• Did you remove the "soe".fot_id from the Order By part of your statement or from the Select part? Sep 18, 2015 at 8:58

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
(
) t
``````

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
(