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

[Adam]

Thanks Brad, for the great help.  Here is the final SQL neatly formatted to do what I needed to do, order points in meters from closest to farthest from a specific point:

SELECT 
    location_id, 
    distance_spheroid ( 
        point_geom, 
        SetSRID(MakePoint(-117.12538, 32.6857),4326), 
        'SPHEROID["WGS_1984",6378137,298.257223563]'
    ) 
FROM 
    locations 
ORDER BY 
    distance_spheroid(
        point_geom, 
        SetSRID( MakePoint( -117.13538, 32.6857 ), 4326 ), 
        'SPHEROID["WGS_1984",6378137,298.257223563]'
    )


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


  You don't want to order by the point_geom because Postgres doesn't understand the geometry type natively. You should ORDER BY distance_spheroid(...) with the distance_spheroid clause being the same as in the SELECT clause.


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


    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

    _______________________________________________
    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/a01f1490/attachment.html>