4

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.problem visualised 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?

11
  • 1
    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

1 Answer 1

4

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
3
  • I think that this actually did the trick! I'll test some more and edit my answer if I find any problems. Thank you so much. Sep 18, 2015 at 9:30
  • Which of the solutions did the trick, @JonasPedersen? Sep 18, 2015 at 10:22
  • 1
    The GROUP BY statement (second). Using SELECT DISTINCT ON without the soe.fot_id doesn't make a difference Sep 18, 2015 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.