2

I have a table clusters with points.

        CREATE TABLE public.clusters (
        cluster_id int4 NULL,
        punto public.geometry NULL,
        cnt int8 NULL
    );

INSERT INTO clusters (cluster_id,punto,cnt) VALUES
 (0,'SRID=4326;POINT (-96.47418050581395 -7.4773468779069745)',172),
 (1,'SRID=4326;POINT (-74.94689710416667 4.8673576875)',48),
 (2,'SRID=4326;POINT (-74.5459309122807 2.498298842105263)',57),
 (3,'SRID=4326;POINT (-60.125132615384615 -3.9571193076923077)',13),
 (4,'SRID=4326;POINT (-74.00383843137257 6.8918209019607835)',102),
 (5,'SRID=4326;POINT (-67.158604125 4.28251)',16),
 (6,'SRID=4326;POINT (-66.90388150000001 4.3350358125)',16),
 (7,'SRID=4326;POINT (-47.78863400000001 -10.39872394117647)',17),
 (8,'SRID=4326;POINT (-47.62490553846154 -7.9716453846153845)',13),
 (9,'SRID=4326;POINT (-73.56909489999998 -3.3606845)',10);



INSERT INTO clusters (cluster_id,punto,cnt) VALUES
     (10,'SRID=4326;POINT (-75.53259585714285 -9.255620642857144)',14),
     (11,'SRID=4326;POINT (-47.9164067 -8.9248)',10),
     (12,'SRID=4326;POINT (-47.99770891666666 -10.499770499999999)',12),
     (13,'SRID=4326;POINT (-73.56718527272727 -3.030551727272727)',11);

An another table of cities

  CREATE TABLE public.cities (
    geom public.geometry(point, 4326) NULL,
    "name" varchar NULL,
    gn_country varchar NULL
);

INSERT INTO cities (geom,"name",gn_country) VALUES
 ('SRID=4326;POINT (-73.21547 4.37575)','Paratebueno','CO'),
 ('SRID=4326;POINT (-74.88429 4.14924)','Espinal','CO'),
 ('SRID=4326;POINT (-73.17309 6.53226)','Pinchote','CO'),
 ('SRID=4326;POINT (-75.25642 2.77717)','Rivera','CO'),
 ('SRID=4326;POINT (-72.63808 7.43637)','Pamplonita','CO'),
 ('SRID=4326;POINT (-77.66536 2.7717)','TimbiquĂ­','CO'),
 ('SRID=4326;POINT (-75.56738 2.74193)','Teruel','CO'),
 ('SRID=4326;POINT (-74.43512 4.96611)','Sasaima','CO'),
 ('SRID=4326;POINT (-75.5275 5.94806)','Montebello','CO'),
 ('SRID=4326;POINT (-77.05273 2.06895)','PatĂ­a','CO');

I am trying to make a query that gives me the nearest city, country and other data and the distance to the clusters point.

So far I have made this with subqueries, based on this answer https://gis.stackexchange.com/a/33680/98778

but I don't know if this is the best approach as I am new to spatial queries and postgis.

select t1.*,
(select ST_Distance(t1.punto::geography , t2.geom::geography)/1000 FROM cities t2 ORDER BY ST_Distance(t1.punto, t2.geom) LIMIT 1) distance,
(select name FROM cities t2 ORDER BY ST_Distance(t1.punto, t2.geom) LIMIT 1) city_name,
(select geom FROM cities t2 ORDER BY ST_Distance(t1.punto, t2.geom) LIMIT 1) city_geom
from clusters t1

check image for result of above.

If this a good approach, is this efficient or is there a better way?

enter image description here

neavilag
  • 229
  • 1
  • 8
  • @BERA I did after instructions but I didn't realize it must be in the same post so I created a new one so right now I don't know exactly what to do – neavilag Mar 26 '22 at 13:11
  • 1
    Edit this question and add the body of your second post. It will then get reopened. However, it may again get closed as there are many relevant and already answered questions on this board...which shouldn't be a reason to get frustrated, as you will then find an answer! – geozelot Mar 26 '22 at 14:07
  • I will, actually my first approach was based on another answer I found but I don't see if it is efficient to do several sub queries for several fields. – neavilag Mar 26 '22 at 14:37
  • 1
    You want to run a (K)NN search, see this answer for an implementation and further reading in links. – geozelot Mar 26 '22 at 16:27
  • @geozelot, your link to your answer gives me the same output of the initial approach and is using one subquery. Is ok to post my query based on the data as the solution linking your answer ? – neavilag Mar 27 '22 at 15:37
  • Go ahead, it is perfectly fine to self answer and accept! – geozelot Mar 27 '22 at 19:14

1 Answers1

1

Actually based on the reference from @geozelot to this answer I was able to perform my desired output but with only one subquery and CROSS JOIN

    SELECT t1.*,ST_Distance(punto::geography , geom::geography)/1000 distance,
name city_name,geom city_geom
        FROM   clusters t1
        CROSS JOIN LATERAL (
          SELECT *
          FROM   cities
          ORDER BY
                 geom <-> punto
          LIMIT  1
        ) as t2
        order by cluster_id ;
neavilag
  • 229
  • 1
  • 8