so I know there have been some questions asked about how to calculate distance using postgis and how to order results by their distance from a respective point.

I'm not very talented at SQL (at all) and so was wondering if I could get some help returning: a.) a list of records sorted by their distance to a point b.) with each record in a small array containing the record itself and the distance from the record to the point passed as the second geometric var

I've already learned how to order my results from this question: https://stackoverflow.com/questions/5111627/order-by-nearest-postgis-georuby-spatial-adapter

So I'm guessing maybe my solution might look something like:

matches =ReferencePoint.where(custom_sql).order("ST_Distance(reference_points.geom, ST_GeomFromText('POINT #{other_loc.x #{other_loc.y}', 3361))")

Which I assume works...haven't actually tested it....

...that would be perhaps amended like:

#aborted code

OK, I don't want to run a second select command, I want to add the sql to the code in my order command. Which means I probably have to use #select....don't know how to do that simultaneously after an ORDER command. So yeah, I need some help. Thanks

EDIT: Example data: - lets say I had 100 boats scattered about around a buoy. I want sql to return those boats in order of their distance to the buoy, closest first. I'm going to use pseudo-sql code to represent the last part of this (text inside of select method):

buoyshape = Buoy.find(2341).projected_geometry_3361 #I document srid codes in my columns
Boat.order{st_distance(:proj_shape_3361, buoyshape)}.select("* + st_distance(proj_shape_3361, ?)", buoy_shape)

OK so I don't know how to write sql so the last #select component doesn't do what its supposed to do. But basically I want the distance between the buoy and boat to be returned as an additional column for each record.

migrated from stackoverflow.com Jun 9 '14 at 15:45

This question came from our site for professional and enthusiast programmers.

  • 1
    A sample of you input and expected results would be very helpful, i.e. for input data X I expect to get output data Y – Craig Ringer May 14 '14 at 6:05
  • Do realise that this will always need all (N*(N-1)/2) possible pairs of points, (and sort the result) which will always be slow. – wildplasser May 14 '14 at 11:40
  • @wildplasser noted, its worth it I think as long as doing it in sql is faster than doing it in ruby. I may actually even consider porting this job to a C library or something but wouldn't know where to begin – boulder_ruby May 16 '14 at 3:24
  • @CraigRinger see my edit for some code (and pseudocode in lieu of real code which would be the answer to my question) – boulder_ruby Jun 4 '14 at 16:48
  • @wildplasser At least its not a redundancy issue right? – boulder_ruby Jun 6 '14 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.