nearest point in postgres tables

Question

I need some help in finding nearest points of each entry of a particular table to another if and only if the nearest entry comes within 10 miles.

2 tables are here:

Table A
lon | lat | block_id

and

table B
 city        | latitude | longitude | block_id
-------------------+----------+-----------+----------

Now, I have to update blockid of table A with the one of Table B which is closest to that entry and if within 10 miles. If no match found, maybe update it "NA". Table A consists of a million entries, table B 10 entries.

I am working with postgres 9.4. I am quite new to postgres, hence not very comfortable in it although fairly comfortable with sql.

Please bear with me if any mistakes. Thanks a lot for your help, I am stuck bad.

Thanks a lot,

Then this one may help. You convert lon, lat to point create a spatial index and then you can use <-> operator for fast searchs — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 12, 2019 at 15:20
I understand I can get geom by ST_point(lon, lat) and to get the distance I can ST_distance(geom1, geom2), I checked up the link, i guess its not much helpful, close, but somehow not able to figure out much, also, I think sird should be 4326, so in other words, I am a bit confused, can you please help in giving any other link in pointing out related query or help in formulating one.... Thanks — SudipM
– SudipM, Commented Feb 13, 2019 at 6:15

Irshad Khan · Accepted Answer · 2020-09-10 12:09:04Z

7

Create an Indexing on a 'location' field, and the location field has a point data type.

CREATE INDEX ON table_name USING GIST(location);

GiST index is capable of optimizing “nearest-neighbor” search :

SELECT * FROM table_name ORDER BY location <-> point '(-74.013, 40.711)' LIMIT 10;

Note: The point first element is longitude and the second element is latitude.

answered Sep 10, 2020 at 12:09

Irshad Khan

6,1462 gold badges50 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2019-02-14 16:23:58Z

0

can you create a field column geom with geometry type? update using ST_point.

Then you do

with cte as (
      SELECT A.geom, B.geom, B.block_id,
             ST_Distance(A.geom, B.geom) as dist -- check this value first
      FROM TableA as A
      CROSS JOIN TableB as B
      WHERE ST_Distance(A.geom, B.geom) < 16000 -- 10 miles
)
SELECT *
FROM  cte

EDIT:

Lets assume your CTE works, then you can find the nearest point like this

with cte as (
  ....
), sort as (
   SELECT *, row_number() over (partition by A.id order by dist) as  rn
   FROM cte
)
SELECT *
FROM sort 
WHERE rn = 1

edited Feb 14, 2019 at 16:23

answered Feb 13, 2019 at 8:33

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

nearest point in postgres tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related