Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.