SQL Example for Finding Photos of Assets

photo-assets.sql

--
-- Simple function that uses a circular "radius of view" around the camera 
-- bearing/azimuth. A more complex function w/ a rectangular frustrum could 
-- also be written w/o too much pain.
--
CREATE OR REPLACE FUNCTION ST_InPhoto(pt geometry, camera geometry, bearing real, azimuth real, camera_field real default 30) 
RETURNS boolean AS
$$
DECLARE
  x_c real;
  y_c real;
  z_c real;
  x_p real;
  y_p real;
  z_p real;
  p_len real;
  dot real;
  angle real;
BEGIN

  x_c := cos(radians(bearing)) * cos(radians(azimuth));
  y_c := sin(radians(bearing)) * cos(radians(azimuth));
  z_c := sin(azimuth);

  x_p := ST_X(pt) - ST_X(camera);
  y_p := ST_Y(pt) - ST_Y(camera);
  z_p := ST_Z(pt) - ST_Z(camera);
  p_len = sqrt(x_p^2 + y_p^2 + z_p^2);
  x_p := x_p / p_len;
  y_p := y_p / p_len;
  z_p := z_p / p_len;
  
  dot := x_c*x_p + y_c*y_p + z_c*z_p;
  angle := degrees(acos(dot));

  return angle < camera_field;

END;
$$
LANGUAGE 'plpgsql';


--
-- Assuming a table of photos, where each photo knows the 
-- camera location, bearing and azimuth, and each asset
-- knows its x/y/z location.
--
-- First filter (ST_DWithin) winnows the join down to just
-- the photos that are within a "reasonable" distance of the
-- asset. Second filter winnows that set down to just those
-- photos where the camera was actually pointing at the 
-- asset.
-- 
SELECT photo.id, asset.id
FROM photo
JOIN asset
ON ST_DWithin(photo.the_geom, asset.the_geom, 100)
AND ST_InPhoto(asset.the_geom, photo.the_geom, photo.bearing, photo.azimuth)
WHERE asset.id = 9999;