Finding Distinct Features Closest to a Point

Recently I had to try and find the first 100 features closest to a point. This is a relatively simple query however the features need to be distinct which added an extra layer of complexity.

Turns out the best way to do it is to write a custom PostGIS function

CREATE OR REPLACE FUNCTION nearest_feature(
	_tablename character varying, 
	_distinctcolumn character varying) 
RETURNS integer AS $$
DECLARE
	distinct text;	
BEGIN	
	RAISE NOTICE 'Creating new blank distinct table..';
	EXECUTE 'CREATE TABLE ' || (_tablename) || '_distinct AS SELECT * FROM ' || (_tablename) || ' WHERE false;';
	RAISE NOTICE 'Finished creating new blank distinct table.';
	
	RAISE NOTICE 'Running distinct query on table %.% ',_tablename,_distinctcolumn;
 
	FOR distinct IN EXECUTE 'SELECT DISTINCT ON (' || _distinctcolumn || ') ' || _distinctcolumn || ' FROM ' || _tablename || ';' LOOP
		RAISE NOTICE 'Finding features for distinct %',oscat;
		EXECUTE 'INSERT INTO ' || (_tablename) || '__distinct SELECT * FROM ' || (_tablename) || ' WHERE ' || (_distinctcolumn) || ' = $1 ORDER BY wkb_geometry <-> st_setsrid(st_makepoint(531364,178281),27700) LIMIT 10;' USING distinct;
		
	END LOOP;
 
	RETURN 1;
END;
$$
LANGUAGE plpgsql;

Then run this function by using the following SQL

SELECT nearest_feature('schema.tablename', 'distinct_column_name');

This was an interesting problem and I manage to solve it.

Useful other information here