[postgis-users] distance from one class to another

[Brent Pedersen]

hi, searched the archives and read the manual but cant figure out how to do 
this.

for a particular tableB.gid where site_name = 'APPLE' i want to find the 
distance to the closest tableB.gid where site_name = 'PEAR'.  

'the_geom' is in tableA. 'comtrs' links the 2 tables.

thanks for any pointers or references. 


i have tableA:
  Column  |       Type        | Modifiers
----------+-------------------+-----------
 gid      | integer           |
 ddlat    | numeric           |
 ddlong   | numeric           |
 comtrs   | character varying |
 the_geom | geometry          | not null

and tableB:
 comtrs                  | character varying(11) |
 site_name               | character varying(70) |
 product_name            | character varying(80) |
 pounds_product_applied  | real                  |
 chemical_name           | character varying(80) |
 pounds_chemical_applied | real                  |
 amount_treated          | real                  |