Hello ,
My question is related to some functions in PostGIS. Problem: We are trying to find x points from a table that are within 20 nautical miles from a given point. Would like to use the ST_Dwithin function as it would use the GIST index. I could use the ST_Distance_Spheroid function but that would scan the entire table. (1) So here's the query I would like to use: select * from table_A where st_dwithin(shape, GeomFromText('POINT(-90 45)',4269), 20.0/60); The shape field in table_A is also stored in the coordinate system for 4269. Since a nautical mile translates into 1 arcminute of latitude, the distance is divided by 60. (2) I am expecting to get 11 records and confirmed it by using the st_distance_spheroid function as show below: select * from table_A where ST_Distance_Spheroid(shape ,ST_GeomFromText('POINT(-90 45)',4269), 'SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]]') <=1852*20; (3) One another test I did was to use the following and got 11 records as expected. Here what I did was to convert to planar coordinates. Select * from table_A where st_dwithin( transform(shape, 2163),transform(GeomFromText('POINT(-90 45)',4269),2163), 20*1852); The query shown in (1) gives me 3 records. What am I missing? Please help. Thanks & Regards, Itishree