8

table A:

lat | long | the_geom | code | sign 
13.8433095 | 100.6360357 | 0101000020E61.... | ABC | start_point
13.7544738 | 100.5459646 | 0101000020E6..... | ABC | end_point
13.4124215 | 100.6232332 | 0101000020E61.... | DEF | start_point
13.2423438 | 100.2324426 | 0101000020E6..... | DEF | end_point

table B:

lat | long | the_geom | code    
13.7546285 | 100.5458729 | 0101000020E.... | ABC    
13.7546698 | 100.5458513 | 0101000020E.... | ABC
13.7547107 | 100.5458233 | 0101000020E.... | DEF    
...

I would like to find the nearest point(s) of each points (start and end point of table A) compare with every points with the same 'code' in table B ?

What's the best PostGIS function/PostgreSQL query to solve this ? What I have tried are ..

ST_Distance_Spheroid and ST_DWithin and ST_Distance

But I have no idea how to combine them within a single query.

1 Answer 1

9

Maybe something like this (I'll assume you have some primary key column "id" in each table):

SELECT A.id, A.code AS Code, A.sign AS Sign, B.id, 
   ST_Distance(A.geom, B.geom) AS Distance 
FROM Table_A AS A, Table_B AS B
WHERE A.id IN ( 
   SELECT X.id
   FROM TableA as X, TableB as Y
-- Here's the important part: refer to the A table **outside** of the subquery
   WHERE X.code=Y.code AND X.id=A.id
-- and use ORDER BY ... LIMIT 1 to get the closest point
   ORDER BY ST_Distance(X.geom, Y.geom) ASC LIMIT 1
);

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.