current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

For my application, I've an User where each one is geolocated. What I want to achieve, is to retrieve User who are within a distance (e.g. User where distance >= 1000 and distance <= 2000).

My user have a longitude and a latitude field.

So far, here's my request : 

select(%{users.*,
  ST_DISTANCE(
    ST_GeographyFromText(
      'SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')'
    ),
    ST_GeographyFromText('SRID=4326;POINT(%f %f)')
  ) as distance
} % [longitude, latitude])
.where(%{
  ST_DISTANCE(
    ST_GeographyFromText(
      'SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')'
    ),
    ST_GeographyFromText('SRID=4326;POINT(%f %f)')
  ) > %d AND
  ST_DWithin(
    ST_GeographyFromText(
      'SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')'
    ),
    ST_GeographyFromText('SRID=4326;POINT(%f %f)'),
    %d
  )
} % [longitude, latitude, 15, longitude, latitude, distance_in_meters])

This is pretty ugly : can you help me ?

share|improve this question
1  
Not really a proper answer, but you could use a BETWEEN condition since your condition is inclusive. Also, ST_SetSRID(ST_MakePoint(lon,lat),4326) is faster than what you have, according to gis.stackexchange.com/questions/58605/… –  mlinth Mar 31 at 19:52

1 Answer 1

up vote 1 down vote

You should store your data as a new geog column, with a spatial index:

ALTER TABLE users ADD COLUMN geog geography(Point, 4326);
UPDATE users SET geog = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX users_geog_idx ON users USING gist (geog);

You may also want to drop the longitude and latitude columns, as they are redundant. Coordinates can be obtained from point geometries using ST_X and ST_Y.

To find records that are within a specified range, use a regular SELECT statement to capture the outer distance, then an EXCEPT to remove the inner distance.

WITH poi AS (
  SELECT ST_SetSRID(ST_MakePoint(%longitude, %latitude), 4326)::geography AS poi
)
SELECT users.*, ST_Distance(geog, poi) AS distance
FROM users
JOIN poi ON ST_DWithin(geog, poi, %outer_dist)
EXCEPT SELECT users.*, ST_Distance(geog, poi) AS distance
FROM users
JOIN poi ON ST_DWithin(geog, poi, %inner_dist);

The parameters are marked with %, which you can reformat for your application.

share|improve this answer
    
Why did you propose to use Geography instead of Geometry ? I've read some article about the speed of Geography which is slower than Geometry. –  cappie013 Apr 1 at 9:13
    
@cappie013 your question used geography. You could also use ST_DWithin(g1, g2, false) to approximate distances on a sphere, which are faster than distances on spheroids (default). ST_Distance on geometries works in Cartesian space, which is fastest, but I don't recommend this unless you have projected points all in (e.g.) one UTM zone. –  Mike T Apr 1 at 18:58

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.