[postgis-users] Postgisfunction like Arc's "near"

[Stephen Woodbridge]

On 7/27/2013 9:10 AM, Morten Sickel wrote:
> I would like to repeat an analysis that has been done in arcgis (10.1) in
> postgis (1.5) (I am running on debian, I could have upgraded, but as my
> project soon is finished, I would like to, if possible finish it in 1.5)
>
> The ananlysis in arc is done using the near table function:
>
> http://resources.arcgis.com/en/help/main/10.1/index.html#//00080000001n000000
>
> does this exist in postgis 1.5 (or newer)?

This sounds like it just creates a list of features within a a search 
radius of a given feature.

select a.fid as in_fid,
        b.fid,
        st_distance(a.the_geom, b.the_geom) as distance
   from in_feature_table a,
        features_table b
  where st_dwithn(a.the_geom, b.the_geom, search_radius)
  order by a.fid, st_distance(a.the_geom, b.the_geom) asc;

If you want to select the in_feature by its fid then

select a.fid as in_fid,
        b.fid,
        st_distance(a.the_geom, b.the_geom) as distance
   from features_table a,
        features_table b
  where a.fid in (list_of_in_feature_ids) and a.fid != b.fid and
        st_dwithn(a.the_geom, b.the_geom, search_radius)
  order by a.fid, st_distance(a.the_geom, b.the_geom) asc;

-Steve