Find the object that is farthest from the rest-CodePudding

The points are located all over the island, but there is clearly one point that is definitely the one I am looking for

I ran into a problem in Postgres when I was working with spatial dates. I have a table with columns: object id, latitude, longitude, and geometry. I need to find the object(point) that is farthest from the rest. How could I do that?

Data

CREATE TABLE cultural_sites
(
    cult_site_id text COLLATE pg_catalog."default" NOT NULL,
    name text COLLATE pg_catalog."default",
    type_id integer,
    found_year integer,
    prot_cat_id integer,
    closest_house_id integer,
    lat double precision,
    "long" double precision,
    geom geometry(Point,4326),
    CONSTRAINT cult_site_id PRIMARY KEY (cult_site_id)
);

INSERT INTO cultural_sites(cult_site_id, lat, long)
VALUES (923048800130006, 46.152222, 33.7015), (911711012380005, 44.43722, 34.104166), (911711012630005, 44.42833, 34.1225), (911710892610005, 44.95, 34.097222), (911710892840005, 44.958084, 34.106479), (911710988820005, 44.947788, 34.099440), (911710988990005, 44.952224, 34.095908), (911710989390005, 44.9449409, 34.097), (911710989490005, 44.955555, 34.093888), (911710989510005, 44.946944, 34.1019444);

ALTER TABLE cultural_sites
ALTER COLUMN geom
type geometry(Point,4326) using st_point(long,lat);

CodePudding user response：

Do a self join to calculate the distance of a reference point to the furthest point in the table, and in the outer query just filter it again with an ORDER BY and LIMIT to get the longest distance:

SELECT t1.geom,t1.geom <-> j.geom AS distance
FROM cultural_sites t1
CROSS JOIN LATERAL (
  SELECT geom FROM cultural_sites t2    
  WHERE NOT t1.geom = t2.geom
  ORDER BY t1.geom <-> t2.geom
  LIMIT 1) j 
ORDER BY 2 DESC
LIMIT 1;

                        geom                        |      distance      
---------------------------------------------------- --------------------
 0101000020E61000006F1283C0CAD94040A9DDAF027C134740 | 1.2593570786051942

Demo: db<>fiddle