[postgis-users] Distance() vs ? - performance

[Tyler Mitchell]

> This is probably one of those cases where a fuller explanation of your 
> problem would help. What are you calculating distances *for*, what are 
> your inputs, desired outputs, general description of the problem. It is 
> possible that a different approach to your problem could yeild a better 
> result.

I thought I had! :)

I've got 54,000 polygons covering an area of 611,000 hectares.  These 
polygons represent forest cover types and they are uniquely identified 
with a field called link.  The table is called vri_polygons.

I have another table with road linework.  95,000 features spread over the 
exact same landbase.  The table is called trim_roads.

What I want to do is calculate the shortest distance from each polygon to 
each road.  I want a list of link's and a minium distance to roads.

Here's what I initially did but found performance and memory usage went 
gonzo.

CREATE table vri_road_dist as 
(
SELECT vri.link, 
min(distance(vri.wkb_geometry,roads.wkb_geometry)::integer) AS 
MIN_ROAD_DIST
FROM vri_polygons vri, trim_roads roads
GROUP BY vri.link
);

I assume this forces a cartesian join and tries to load both intersecting 
tables into RAM?  Both tables have GIST indexes.

Tyler
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040217/40d66c56/attachment.html>