title: Find n Nearest Neighbors for given Point using PostGIS?
body: <p>I am trying to solve the problem of finding the n nearest neighbors using PostGIS:</p>
  
  <p>Starting Point:</p>
  
  <ul>
  <li>Table geoname with geonames (from
  geonames.org) containing
  latitude/longitude (WSG-84)</li>
  <li>Added a GeometryColumn geom with
  srid=4326 and datatype=POINT</li>
  <li>Filled geom with values: UPDATE geoname
  SET geom =
  ST_SetSRID(ST_Point(longitude,latitude),
  4326);</li>
  <li>Created GIST index for geom (CREATE
  INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON
  geoname;)</li>
  <li>Created PRIMARY KEY UNIQUE BTREE index for geonameid</li>
  </ul>
  
  <p>Problem:
  Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid.</p>
  
  <p>Possible solution:</p>
  
  <p>Inspired by <a href="http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor" rel="noreferrer">http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor</a>, I tried the following query:</p>
  
  <pre><code>"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
  "FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid &lt;&gt; ende.geonameid " +
  "AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5"
  </code></pre>
  
  <p>Processing time: about 60s</p>
  
  <p>Also tried an approach based on EXPAND:</p>
  
  <pre><code>"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
  "FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid &lt;&gt; ende.geonameid AND expand(start.geom, 300) &amp;&amp; ende.geom " +
  "order by distance limit 5"
  </code></pre>
  
  <p>Processing time: about 120s</p>
  
  <p>The intended application is some kind of autocomplete. So, any approach taking longer than >1s is not applicable. Is it generally possible to achieve a response time of &lt;1s with PostGIS?</p>