[GIS] Distance between DB field and Point using PostGIS

distancegeographypostgispostgresql

I'm using Postgresql 9.0 with PostGIS 2.0 extension.
I've got the table and geographic field location:

=# SELECT * FROM geography_columns;
 f_table_catalog | f_table_schema |   f_table_name   | f_geography_column | coord_dimension | srid | type  
-----------------+----------------+------------------+--------------------+-----------------+------+-------
          _dev   | public         |          objects | location           |               2 | 4326 | Point


=# \d objects
                                       Table "public.objects"
     Column     |            Type             |                           Modifiers                           
----------------+-----------------------------+---------------------------------------------------------------
 ...
 location       | geography(Point,4326)       |

And have got records with coordinates:

=# SELECT ST_X(location::geometry), ST_Y(location::geometry) FROM objects;
  st_x  | st_y  
--------+-------
        |      
 -73.01 | 15.01
 -73.02 | 15.02
 -73.03 | 15.03
(4 rows)

I want to grab records within distance in meters from Point(-70 15).

I've calculated this distances to each point (just for know it):

=# SELECT ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-70 15)'), ST_GeographyFromText('SRID=4326;POINT(-70.01 15.01)'));
   st_distance    
------------------
 1543.03968305103
(1 row)

=# SELECT ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-70 15)'), ST_GeographyFromText('SRID=4326;POINT(-70.02 15.02)'));
   st_distance    
------------------
 3086.04520065436
(1 row)

=# SELECT ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-70 15)'), ST_GeographyFromText('SRID=4326;POINT(-70.03 15.03)'));
  st_distance   
----------------
 4629.016529543

Now I see the closest point in 1543 meters and farthest point in 4629 meters.

I've used very similar query to example from here:

Query and measurement functions use units of meters. So distance
parameters should be expressed in meters, and return values should be
expected in meters (or square meters for areas).

-- Show a distance query and note, London is outside the 1000km tolerance
  SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);

=# SELECT location FROM objects WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-70 15)'), 5000);
 location 
----------
(0 rows)

and I didn't get locations within 5000 meters. More than that I cannot calculate distances in meters from location to Point:

=# SELECT ST_Distance(location, ST_GeographyFromText('SRID=4326;POINT(-70 15)')) FROM objects;
   st_distance    
------------------

 323718.841976209
 324792.390871594
 325869.606072403
(4 rows)

There numbers are a lot bigger than numbers from distance in Point to Point example.

I'm guessing something wrong with my column because I cannot even grab points with same coordinates:

=# SELECT location FROM objects WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-70.01 15.01)'), 5000);
 location 
----------
(0 rows)

Best Answer

Looking closely at your data it looks like in the distance calculations you have used different coordinates from those in the *activity_objects* table.

In example on the first query you wrote:

 [...] ST_GeographyFromText('SRID=4326;POINT(-70.01 15.01)'));

where it should have been:

 [...] ST_GeographyFromText('SRID=4326;POINT(-73.01 15.01)'));

Notice -70.01 being used in place of -73.01.

hence the discrepancies in all your subsequent calculations.

Related Question