In an app that I am currently developing users are allowed to locations identified, amongst other things by their latitude/longitude coordinates. The location entries end up in a Postgres table.
CREATE TABLE "public"."destis" (
"id" integer DEFAULT nextval('destis_id_seq') NOT NULL,
"author" integer NOT NULL,
"einzig" character varying(12) NOT NULL,
"lang" character varying(2) NOT NULL,
"title" character varying(24) NOT NULL,
"xtent" real[] DEFAULT '{0,0,0,0}' NOT NULL,
"center" real[] DEFAULT '{0,0}' NOT NULL,
"touchdown" real[] NOT NULL,
CONSTRAINT "destis_einzig" UNIQUE ("einzig")
) WITH (oids = false);
The above is a simplified version of that table - I had skipped irrelevant field entries. I want to run a periodic CRON job that updates the weather data (stored on Redis) at each DISTINCT location as identified by the center column in each row of this table.
The issue here is with that word - DISTINCT. In a typical scenario I could have two or more destination entries that are at nearly the same location. e.g.
center = {5.87083,49.7784611}
center = {5.87099,49.7784703}
center = {5.87021,49.778452}
For all practical purposes there is only one latitude/longitude pair here {5.871,49.778} for which I need to launch an external API call to fetch the current weather.
The question here is how do I do this directly in SQL? While I could quite easily fetch all center array values as JSON, compare them for near equality in PHP and then fetch weather for just a few distinct locations I cannot but help wonder if there isn't a way to simply run a query that returns distinct {lat,long} values after rounding them to three places of decimal. I have on occasion, used SELECT DISTINCT... but I have no idea how/whether I can use this on an array field
You can convert the array to an array of rounded values using something like this:
array[round(center[1]::numeric, 2), round(center[2]::numeric, 2)]::real[] as center
I would probably create a function so that I don't have to repeat this expression over and over again.