postgresql_grid.sql

  /*

   -- EXAMPLE --

   SELECT
   * FROM
     unnest(grid(array(SELECT
     (
       lat,
       lng,
       count(*)
      )::Pin
     FROM  MYSCHEMA.MYTABLE group by lat, lng ),
        -33.687131, -- minLat
        40.946627,  -- maxLat
        -72.900276, -- minLng
        -8.461811,  -- maxLng
        10, -- width 
        10  -- height
  ))
  */
  CREATE TYPE pin AS (
    lat float,
    lng float,
    count bigint
  );
  CREATE OR REPLACE FUNCTION GRID( inPins pin[], 
    minLat float, 
    maxLat float, 
    minLng float, 
    maxLng float, 
    width integer, 
    height integer )
  RETURNS pin[] AS

  $$

  DECLARE

  stepLat float;
  stepLng float;
  returnPins pin[];

  BEGIN

  IF minLat IS NULL THEN
    SELECT 
      MIN( lat ) INTO minLat
    FROM
     unnest( inPins );
  END IF;

  IF maxLat IS NULL THEN
    SELECT 
      MAX( lat ) INTO maxLat
    FROM
     unnest( inPins );
  END IF;

  IF minLng IS NULL THEN
    SELECT 
      MIN( lng ) INTO minLng
    FROM
     unnest( inPins );
  END IF;

  IF maxLng IS NULL THEN
    SELECT 
      MAX( lng ) INTO maxLng
    FROM
     unnest( inPins );
  END IF;

  /**
   * DEFINE THE STEP TO EACH CLUSTER
   */
  SELECT ( maxLat - minLat  ) / height into stepLat;
  SELECT ( maxLng - minLng  ) / width  into stepLng;


  /**
   * CREATE INITAL DATA TO KM_CLUSTER
   */
RETURN ARRAY( 
SELECT (
  SUM(PIN.lat*PIN.count)/SUM(PIN.count),
  SUM(PIN.lng*PIN.count)/SUM(PIN.count),
  SUM(PIN.count)
)::Pin
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 >= bounds.fromLng and PIN.lng <= bounds.toLng and
  PIN.lat >= bounds.fromLat and PIN.lat <= bounds.toLat
GROUP BY fromLat, toLat, fromLng, toLng
);

  end;
  $$ language plpgsql;