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

[Brad Ediger]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061230/8aec32e4/attachment.html>