Postgresql – Unexpected results with soundex() function when combined st_distance()

postgispostgresqlpostgresql-9.3soundex

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 & also same names,but it doesn't return any similar names when using this query,and i want to retrieve the names which are located in particular distance,so i used st_distance here,then i tried with another query using window function 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

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?

Best Answer

The condition:

AND    A.name = B.name

only allows identical names, while you obviously want to match names with the same soundex value. So make that:

AND    soundex(A.name) = soundex(B.name)

If gid is the primary key, you can also simplify to:

GROUP BY a.gid, b.gid

However, since there obviously is no aggregation at all, you can simply remove the GROUP BY clause.

As suggested by PostGis, consider ST_DWithin() instead of ST_Distance() in the JOIN clause. The latter cannot utilize a spatial index.

Related Question