1
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?

1
  • As always, the table definition (\d tbl in psql) would help to clarify. Is gid your PK field? Jul 24, 2015 at 15:34

1 Answer 1

2

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.

1
  • 1
    thnk u @ Erwin Brandstetter i got the results before your reply,anyway i try with st_Dwithin also. Jul 27, 2015 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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