# How to find the nearest point by using PostGIS function?

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.

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
);
``````