[postgis-users] Finding Points with distance to a location.

[Adam]

I've changed this to give me output by miles:

SELECT 
    location_id, 
    ( 
      distance_spheroid(point_geom, 
      SetSRID(MakePoint(-117.13538, 32.6857),4326), 
      'SPHEROID["WGS_1984",6378137,298.257223563]') / 1609.344 
    ) 
FROM 
    locations;

How would I change this to give me the first returned row as the closest point and ascend to the farthest point?  I know I'd usually use an "ORDERY BY" clause, but what do I "ORDER BY"?  I tried "ORDER BY point_geom" but that isn't exactly in order.


  ----- Original Message ----- 
  From: Brad Ediger 
  To: PostGIS Users Discussion 
  Sent: Saturday, December 30, 2006 12:58 AM
  Subject: Re: [postgis-users] Finding Points with distance to a location.


  My bad.


  SELECT location_id, Distance(point_geom, SetSRID(MakePoint(-117.13538, 32.6857),4326)) FROM locations;

  PostGIS does some sanity checks to ensure you're not operating on different SRIDs (which would be meaningless). You have to tell PostGIS that the point you're creating has SRID 4326 (WGS84). Note that SetSRID won't convert a geometry from one SRID to another; you need Transform() for that. You're simply tagging that point with the 4326 SRID.


  On Dec 30, 2006, at 12:54 AM, Adam wrote:


    WHen I run: SELECT location_id, Distance(point_geom, MakePoint(-117.13538, 32.6857)) FROM locations;

    I get error:  Operation on two GEOMETRIES with different SRIDs

    What does that mean?
      ----- Original Message ----- 
      From: Brad Ediger 
      To: PostGIS Users Discussion 
      Sent: Saturday, December 30, 2006 12:49 AM
      Subject: Re: [postgis-users] Finding Points with distance to a location.


      You want to use the Distance function in the result set:



      SELECT location_id, Distance(point_geom, MakePoint(-117.13538, 32.6857)) FROM table;


      You don't need a WHERE clause unless you want to restrict the result rows (for example, if you wanted to limit the results to locations within 50 miles of the target point).


      I would suggest reviewing the PostgreSQL documentation for some basic SQL instruction (at http://www.postgresql.org/docs/; the manual is actually very good). Then you can refer to the PostGIS reference (http://postgis.refractions.net/docs/ch06.html) for a list of functions.


      You really don't need a lot of knowledge on top of PostgreSQL to use PostGIS effectively. Most everything that seems confusing or vague about the PostGIS documentation can be clarified by reading the OpenGIS specs, at http://www.opengeospatial.org/standards/sfa and http://www.opengeospatial.org/standards/sfb.


      And you can always ask on postgis-users if you need clarification.


      Hope this helps,
      Brad


      On Dec 30, 2006, at 12:10 AM, Adam wrote:


        I've tried:

        SELECT point_geom, location_id
        FROM locations
        WHERE Distance(point_geom, 'POINT(-117.13538 32.6857)')

        But get error:  argument of WHERE must be type boolean, not type double precision




          ----- Original Message ----- 
          From: Adam 
          To: PostGIS Users Discussion 
          Sent: Saturday, December 30, 2006 12:07 AM
          Subject: [postgis-users] Finding Points with distance to a location.


          I figured this out last year, but can't figure out how I figured it out.  I've read "Mapping Hacks" and "Web Mapping Illustrated" along with what I could find in the WIKI and documentation but can't start to figure it out.

          I have my geometry column named "point_geom" and a unique column named "location_id".

          I want to query the distance of all the points in my "point_geom" column to coords:  Long = -117.13538 :: Lat = 32.6857

          What would the query for this look like?  Is there a comprehensive article or book out that goes in depth into how spatial queries work?

          The output would look like this:

          location_id | distance
          -------------------------------
          5 | 45.38823
          9 | 50.39952
          1 | 82.39923
          .
          .
          .
          -------------------------------


          I'm guessing that you'd use distance_spheroid for the calculation.     





----------------------------------------------------------------------



          _______________________________________________
          postgis-users mailing list
          postgis-users at postgis.refractions.net
          http://postgis.refractions.net/mailman/listinfo/postgis-users

        _______________________________________________
        postgis-users mailing list
        postgis-users at postgis.refractions.net
        http://postgis.refractions.net/mailman/listinfo/postgis-users





--------------------------------------------------------------------------



      _______________________________________________
      postgis-users mailing list
      postgis-users at postgis.refractions.net
      http://postgis.refractions.net/mailman/listinfo/postgis-users

    _______________________________________________
    postgis-users mailing list
    postgis-users at postgis.refractions.net
    http://postgis.refractions.net/mailman/listinfo/postgis-users




------------------------------------------------------------------------------


  _______________________________________________
  postgis-users mailing list
  postgis-users at postgis.refractions.net
  http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061230/fa5d35af/attachment.html>