Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Stupidest mistake ever. Thank you a lot. –  ck3g Jun 4 '13 at 9:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.