0

UPDATE: This question was entered, but by mistake I re-asked in another post, so please consider this post as the one that answer this. This question should be closed and mark as duplicate.

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 and distance to the clusters point.

So far I have made this with subqueries, 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
  • We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please do not delete questions that have been closed, and then re-ask them. We need you to edit the original question so that those edits may be reviewed to determine if the question is ready to be re-opened. Also, as per the [help/behavior] please do not include chitchat like thanks in your posts. – PolyGeo Mar 26 '22 at 07:35
  • Aw man, @PolyGeo, sometimes our rules have a feedback chain like German bureaucracy...I see frustration coming up down the road - when the reopened original get's closed again for being a duplicate of many other (K)NN problems. You think we could simply delete the old and make this a duplicate of a post with a good answer to the problem? – geozelot Mar 26 '22 at 08:37
  • @geozelot the problem with allowing any question to be deleted and re-asked is that it encourages doing so rather than the asker learning how to improve their questions, which feeds into asking better questions at the outset. – PolyGeo Mar 26 '22 at 11:55
  • Thank you for your feedback @PolyGeo, sorry if I misunderstood your instructions since you closed my question I made a new one with your requirements adding code of what I have done so far. So you want me to post the new one in the first one? – neavilag Mar 26 '22 at 13:04
  • @PolyGeo agreed. However, seeing that, even after editing the original, it is bound to get closed again (not even for a bad reason, that is, if the then duplicate is providing an answer), and with the effort of this post in mind, personally I would have gone for the way of mild education and allow for this one faux pas, leave a comment as you did, and directly mark as duplicate of a relevant post. Just saying. Might even lessen your frustration with your never ending work on this board ,) – geozelot Mar 26 '22 at 14:01
  • @neavilag Yes - whenever a question is closed to answers and you seek an answer to it then you should edit it so that the community can vote on whether it should be re-opened. – PolyGeo Mar 26 '22 at 21:40
  • 1
    @geozelot your suggested action is not unreasonable but it’s a duplicate I would not have spotted so it was not an action open to me. Addressing the delete and re-ask was a simpler and more consistent action to take. – PolyGeo Mar 26 '22 at 21:50
  • Yes @hgb I agreed how can it be closed, and pointed to the latest post ? – neavilag Mar 30 '22 at 21:44
  • 1
    @neavilag Someone with privileges to close questions as duplicates would have to do that. You could edit the question and add a link to your other question. That would prevent others from spending time on this one. – hgb Mar 30 '22 at 22:01

0 Answers0