select soundex(A.name), A.gid, A.name, soundex(B.name), B.gid, B.name, st_distance(B.the_geom,A.the_geom) * 111.325 as dist from (select name, gid, the_geom, count(soundex(name)) over (partition by soundex(name)) as cnt from poi_new A) A join poi_new B on st_distance(B.the_geom, A.the_geom) * 111.325 < .2 where cnt > 1 and A.gid <> B.gid and A.name = B.name group by A.gid, A.name, B.gid, B.name, dist, soundex(A.name), soundex(B.name);
Soundex value is same and also the names are the same, but it doesn't return any similar names when using this query.
I want to retrieve the names which are located in a particular distance, so I used
st_distance() here, then I tried with another query using window function. I am getting the similar names with same soundex value.
The output which shows similar names with same soundex value, I want to get the results like this with respect to the st_distance function which I used in the query.
Where do I have to change the code? Is the window function which I used is correct or not?