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

6
  • @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, 2022 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, 2022 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, 2022 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, 2022 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, 2022 at 15:37

1 Answer 1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.