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.
Best Answer
Maybe something like this (I'll assume you have some primary key column "id" in each table):