Skip to content

Instantly share code, notes, and snippets.

@thiagomata
Last active February 16, 2022 10:39
Show Gist options
  • Save thiagomata/18ea14853998468c1a1d to your computer and use it in GitHub Desktop.
Save thiagomata/18ea14853998468c1a1d to your computer and use it in GitHub Desktop.
/*
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment