Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select postgis_version()


postgis_version


3.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1



SELECT 1


CREATE SEQUENCE IF NOT EXISTS listing_locations_id_seq;

-- Table Definition
CREATE TABLE "public"."listing_locations" (
    "id" int8 NOT NULL DEFAULT nextval('listing_locations_id_seq'::regclass),
    "coordinate" geography(Point,4326) NOT NULL,
    PRIMARY KEY ("id")
);

CREATE INDEX listing_locations_coordinate_index ON listing_locations USING gist((coordinate::geography));

CREATE SEQUENCE



CREATE TABLE



CREATE INDEX


INSERT INTO "public"."listing_locations" ("coordinate") VALUES
('SRID=4326;POINT(-80.5656 28.0895)'),
('SRID=4326;POINT(-80.5603 28.0683)'),
('SRID=4326;POINT(-80.5656 28.0036)'),
('SRID=4326;POINT(-80.51334900000001 27.9714321)'),
('SRID=4326;POINT(-80.5264 27.9289)'),
('SRID=4326;POINT(-80.521666 27.893906)'),
('SRID=4326;POINT(-80.53 27.88)'),
('SRID=4326;POINT(-80.517985 27.887694)'),
('SRID=4326;POINT(-80.5003 27.8806)'),
('SRID=4326;POINT(-80.4706 27.8164)'),
('SRID=4326;POINT(-80.477305 27.812764)'),
('SRID=4326;POINT(-80.48861615 27.805408)'),
('SRID=4326;POINT(-80.4907622019231 27.7857205)'),
('SRID=4326;POINT(-80.500483 27.801716)'),
('SRID=4326;POINT(-80.4998497 27.765031)'),
('SRID=4326;POINT(-80.48195 27.774576)'),
('SRID=4326;POINT(-80.4746447 27.7613587)'),
('SRID=4326;POINT(-80.39879999999999 27.7646)'),
('SRID=4326;POINT(-80.06771999999999 26.89198)'),
('SRID=4326;POINT(-80.0534 26.8798)'),
('SRID=4326;POINT(-80.07163 26.871447)'),
('SRID=4326;POINT(-80.106881 26.873576)'),
('SRID=4326;POINT(-80.121365 26.894827)'),
('SRID=4326;POINT(-80.12138299999999 26.894443)'),
('SRID=4326;POINT(-80.12182905 26.893736)'),
('SRID=4326;POINT(-80.1193 26.894247)'),
('SRID=4326;POINT(-80.11023400000001 26.894264)'),
('SRID=4326;POINT(-80.11043600000001 26.894245)'),
('SRID=4326;POINT(-80.10974400000001 26.890939)'),
('SRID=4326;POINT(-80.103703 26.910631)'),
('SRID=4326;POINT(-80.109937 26.903458)'),
('SRID=4326;POINT(-80.10885 26.904351)'),
('SRID=4326;POINT(-80.113834 26.906387)'),
('SRID=4326;POINT(-80.105751 26.922902)'),
('SRID=4326;POINT(-80.098776 26.940346)'),
('SRID=4326;POINT(-80.09962299999999 26.930865)'),
('SRID=4326;POINT(-80.0942 26.9342)'),
('SRID=4326;POINT(-80.0960718 26.9394317)'),
('SRID=4326;POINT(-80.0792738 26.933592)'),
('SRID=4326;POINT(-80.074583 26.924072)'),
('SRID=4326;POINT(-80.08889192036909 26.96430185)'),
('SRID=4326;POINT(-80.087746 26.960898)'),
('SRID=4326;POINT(-80.090368 26.959172)'),
('SRID=4326;POINT(-80.09 26.96)'),
('SRID=4326;POINT(-80.09137200000001 26.959324)'),
('SRID=4326;POINT(-80.0748 26.9489)'),
('SRID=4326;POINT(-80.09872900000001 26.940884)'),
('SRID=4326;POINT(-80.123412 26.947297)'),
('SRID=4326;POINT(-80.10133500000001 26.967866)'),
('SRID=4326;POINT(-80.1014 27.0314)'),
('SRID=4326;POINT(-80.1218251462125 27.0512495201499)'),
('SRID=4326;POINT(-80.133994 27.072551)'),
('SRID=4326;POINT(-80.133724 27.072977)'),
('SRID=4326;POINT(-80.144543 27.073457)'),
('SRID=4326;POINT(-80.145312 27.076755)'),
('SRID=4326;POINT(-80.146148 27.052951)'),
('SRID=4326;POINT(-80.13639999999999 27.0595)'),
('SRID=4326;POINT(-80.130144 27.05951)'),
('SRID=4326;POINT(-80.13243199999999 27.059449)'),
('SRID=4326;POINT(-80.132244 26.97891)'),
('SRID=4326;POINT(-80.13455399999999 26.949755)'),
('SRID=4326;POINT(-80.132637 26.946483)'),
('SRID=4326;POINT(-80.132631 26.946485)'),
('SRID=4326;POINT(-80.155044 26.953254)'),
('SRID=4326;POINT(-80.2195892 26.9243999)'),
('SRID=4326;POINT(-80.134101 26.928412)'),
('SRID=4326;POINT(-80.132901 26.927856)'),
('SRID=4326;POINT(-80.13073799999999 26.921047)'),
('SRID=4326;POINT(-80.13 26.92)'),
('SRID=4326;POINT(-80.142257 26.918759)'),
('SRID=4326;POINT(-80.12903 26.91916)'),
('SRID=4326;POINT(-80.16467299999999 26.900212)'),
('SRID=4326;POINT(-80.132115 26.89149)'),
('SRID=4326;POINT(-80.18479000000001 26.911971)'),
('SRID=4326;POINT(-80.19028729999999 26.91215)'),
('SRID=4326;POINT(-80.23999999999999 26.92)'),
('SRID=4326;POINT(-80.40000000000001 27.08)'),
('SRID=4326;POINT(-80.4821 27.0273)'),
('SRID=4326;POINT(-80.2052991658198 27.0936686556943)'),
('SRID=4326;POINT(-80.229066 27.146424)'),
('SRID=4326;POINT(-80.20083 27.143889)'),
('SRID=4326;POINT(-80.2567296 27.183521)'),
('SRID=4326;POINT(-80.25672419999999 27.18445695)'),
('SRID=4326;POINT(-80.29074079999999 27.1793605)'),
('SRID=4326;POINT(-80.2662 27.1678)'),
('SRID=4326;POINT(-80.26468800000001 27.164846)'),
('SRID=4326;POINT(-80.30293399999999 27.166734)'),
('SRID=4326;POINT(-80.2980493 27.164859)'),
('SRID=4326;POINT(-80.251773 27.224391)'),
('SRID=4326;POINT(-80.25190000000001 27.198)'),
('SRID=4326;POINT(-80.227182 27.193861)'),
('SRID=4326;POINT(-80.23401800000001 27.241614)'),
('SRID=4326;POINT(-80.2298 27.2545)'),
('SRID=4326;POINT(-80.2 27.27)'),
('SRID=4326;POINT(-80.2204 27.2995)'),
('SRID=4326;POINT(-80.28743 27.292017)'),
('SRID=4326;POINT(-80.280986 27.287569)'),
('SRID=4326;POINT(-80.290516 27.289818)'),
('SRID=4326;POINT(-80.286356 27.279896)'),
('SRID=4326;POINT(-80.277629 27.279148)');

INSERT 0 100


WITH j (points) AS (
  SELECT 
   ST_Collect( 
	(ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
	(ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
   )::geography
)
SELECT id,ST_AsEWKT(j.points),ST_AsEWKT(coordinate), ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN j ON ST_DWithin(j.points,coordinate,130000,false);


id
st_asewkt
st_asewkt
distance


1
SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)
SRID=4326;POINT(-80.5656 28.0895)
126195.74409706

2
SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)
SRID=4326;POINT(-80.5603 28.0683)
127775.97175479



SELECT 2


SELECT id,ST_AsEWKT(j.points),ST_AsEWKT(coordinate), ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN (SELECT
	   ST_Collect( 
	    (ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
	    (ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
       )::geography) j (points)
	   ON ST_DWithin(j.points,coordinate,130000,false);


id
st_asewkt
st_asewkt
distance


1
SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)
SRID=4326;POINT(-80.5656 28.0895)
126195.74409706

2
SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)
SRID=4326;POINT(-80.5603 28.0683)
127775.97175479



SELECT 2
id	st_asewkt	st_asewkt	distance
1	SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)	SRID=4326;POINT(-80.5656 28.0895)	126195.74409706
2	SRID=4326;MULTIPOINT(-81.70361 28.62167,-81.70365 28.62169)	SRID=4326;POINT(-80.5603 28.0683)	127775.97175479