--- This should look a little familliar by now since we have done this
--- bit before: we are finding the distance from each fastfood
--- restaurant to it's nearest neighbor.

-- We know have fastfoods_eqd which we just passed over from R via rgdal

\d fastfoods_eqd

-- NOTE that the we still have a "geom" column -- but it only a
-- string. It is was once a spatial data type when we read it into R
-- using RpostgreSQL, it lost that meaning.  The result of our use of
-- coordinates() in R has produced the column "wkb_geometry" the "wkb"
-- part stands for "well known binary" which of course is
-- nonesense. the wkb_ format is a useful standard for moving gis
-- information among different applications.  It is easy to transform
-- it back to a useful column for our purpose.

-- So let's get rid of the useless geom column and replace it 
alter table fastfoods_eqd drop column geom;

select AddGeometryColumn(YOUR-SCHEMA,'fastfoods_eqd', 'geom',2163,'POINT',2);

update fastfoods_eqd
set geom = ST_GeomFromWKB(wkb_geometry, 2163);

create index fastfoods_eqd_idx on fastfoods_eqd using gist(geom);

select f1.ff_id as ff_id, f1.geom as geom,
       f2.ff_id as ff_id2, 
       f1.lat as lat1,
       f1.lon as lon1, 
       f2.lat as lat2, 
       f2.lon as lon2, 
       ST_Distance(f1.geom,f2.geom)/1000 as dist_km
into TEMP distmat_eqd
from fastfoods_eqd as f1 LEFT JOIN
     fastfoods_eqd as f2 ON
     ST_DWithin(f1.geom,f2.geom, 30000) and
     NOT f1.ff_id = f2.ff_id ;

--distmat is a TEMPORARY table (it will disappear when you logout of
--postgresql) It holds distances from each restaurant to its neighbors
--that are within 30KM

select * from distmat_eqd limit 10;

-- how many neighbors (within 30KM) do restaraunts have?
select ff_id, count(distinct(ff_id2)) as neighbors
       into TEMP nbors
       from distmat_eqd group by ff_id;

select min(neighbors) as minn, max(neighbors) as maxn, avg(neighbors)
as meann from nbors;


-- think about this one
select avg(dist_km),min(dist_km),max(dist_km) from distmat_eqd;

--does that seem like a rather high average distance?

-- how many restaurants have no neighbors within 30KM?

select count(*) from distmat_eqd where ff_id2 is NULL;

-- That's not enough for me to worry about. If you feel
-- otherwise... fine.  Write when you get work.


--STEP 2 
-- Find the nearest of all those neighbors.
-- dragging the geom col along so that rgdal will see this table.

select ff_id, geom, min(dist_km) as dist_km
into ffdistance_eqd
from distmat_eqd 
group by ff_id, geom;

select avg(dist_km) from ffdistance;
select avg(dist_km) from ffdistance_eqd;

-- Have we doubled the average distance between fastfood restaurants or what?

