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.