--- In this we calculate distance to nearest neighbor from each fast
--- food restaurant. The result will be in a table called ffdistance,
--- which we will read into R where we will compare it to the
--- theoreticl distribution of nearest neighbor distances of things
--- that are randomly located.

-- THE OBVIOUS brute force method of finding nearest neighbor distance
-- would be to simply calculate the distance from each fastfood
-- reatuarant to EVERY other restaurant whose location we know; store
-- it in a matrix and then seach each row for the smallest value. This
-- turns out to be impractical, however, as it invoves calculating and
-- storing about 2.5 BILLION distances (50K*50K) the vast majority of
-- which are not the ones we are interested in.  With spatially aware
-- SQL we can do quite a lot better -- but the basic procedure is
-- still that same: calculate a bunch of distances and choose the minum.

-- Step 1: Create a table that hold the distance from each fsatfood
-- restaurant to all such restaurants within 30 KM. This is done with
-- a LEFT JOIN In this case it's also a "self" join on fastfoods.  The
-- key trick in this statement is the ST_DWithin(f1.geom,f2.geom,
-- 30000).  It has the effect of limiting the calculation to
-- restaurants within 30KM of each other. Without this wheeze the
-- querey would take hours and return a tables with 2,500,200,004 --
-- which is too many and too long. ST_DWithin() uses lots of tricks
-- that are invisible to us (but which make considerable use of those
-- indexes that we have been creating) in order to avoid doing much
-- work to figure out which points are in the neighborhood of a
-- particular point.
-- Because it's a LEFT join it will also
-- include restaurants which are not within 30KM of any other fastfood
-- restaurant.

--NOTE that the LEFT JOIN will result in a warning message:
--NOTICE:  LWGEOM_gist_joinsel called with incorrect join type
--NOTICE:  LWGEOM_gist_joinsel called with incorrect join type

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
from fastfoods as f1 LEFT JOIN
     fastfoods 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 limit 10;

-- Let's check for sanity  do the following queries yield results
-- that you would care to believe?

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

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


-- How about min max and average distance?
select avg(dist_km),min(dist_km),max(dist_km) from distmat;



-- how many restaurants have no neighbors within 30KM?
select count(*) from distmat 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
from distmat 
group by ff_id, geom;

--Now we are ready to schlep this over to R where we can compare these
--distances to those that would obtain if fastfood restaurants were
--rain drops.