reduce vars

postgresql_grid.sql

  /*

   -- EXAMPLE --

   SELECT
   * 
   FROM grid(array(SELECT
     (
       lat,
       lng,
       count(*)
      )::Pin
     FROM  MYSCHEMA.MYTABLE group by lat, lng ),
        10, -- width 
        10  -- height
  )
  */
  CREATE TYPE pin AS (
    lat float,
    lng float,
    count bigint
  );
  CREATE OR REPLACE FUNCTION GRID( inPins pin[], 
    width integer, 
    height integer )
  RETURNS table
   (
    lat float,
    lng float,
    count bigint
  ) AS

  $$

  DECLARE
  minLat float;
  minLng float;
  stepLat float;
  stepLng float;
  returnPins pin[];

  BEGIN

  /**
   * calcuate bbox
   */
  SELECT 
    MIN( t.lat ), 
    (MAX( t.lat )-MIN( t.lat )) / height,
    MIN( t.lng ),
    (MAX( t.lng ) - MIN( t.lng ))/ width
  INTO
    minLat, stepLat,
    minLng, stepLng
  FROM
    unnest( inPins ) t;

  /**
   * CREATE INITAL DATA TO KM_CLUSTER
   */
RETURN QUERY
SELECT 
  SUM(PIN.lat*PIN.count)/SUM(PIN.count),
  SUM(PIN.lng*PIN.count)/SUM(PIN.count),
  SUM(PIN.count)
FROM
(
SELECT 
      minLat + stepLat * ( currentHeight     ) AS fromLat,
      minLat + stepLat * ( currentHeight + 1 ) AS toLat,
      minLng + stepLng * ( currentWidth      ) AS fromLng,
      minLng + stepLng * ( currentWidth  + 1 ) AS toLng
    FROM
      generate_series(0, width ) currentWidth,
      generate_series(0, height) currentHeight
) as bounds
  INNER JOIN unnest( inPins ) AS PIN
ON
  PIN.lng BETWEEN bounds.fromLng AND bounds.toLng AND
  PIN.lat BETWEEN bounds.fromLat AND bounds.toLat
GROUP BY fromLat, toLat, fromLng, toLng;

  end;
  $$ language plpgsql;