[postgis-users] st_dist, st_sphere, st_spheroid - overhead/efficiency question

Paul Ramsey pramsey at cleverelephant.ca
Thu Jul 9 10:55:23 PDT 2009


select as_astext(st_extent(the_geom)) from market_locations;
select as_astext(st_extent(the_geom)) from customer_locations;

On Thu, Jul 9, 2009 at 10:54 AM, Paul Ramsey<pramsey at cleverelephant.ca> wrote:
> Bo,
>
> When you say something like "no longer match", knowing what you
> started with and what you have now is useful to making any comment.
>
> And there was no reprojection. ST_Transform() doesn't change the
> on-disk values, it reprojects them on the fly. And you were, in any
> event, asking for an identity transform, from 4326 to 4326.
>
> P.
>
> On Thu, Jul 9, 2009 at 10:46 AM, Bo Coughlin<bo at rekindl.com> wrote:
>> Hmmm....I just looked again at the points - they no longer match the
>> original lat/lon coordinates, could I have somehow altered these via
>> reprojecting them?
>> Bo Coughlin
>> bo at rekindl.com
>> 704.414.0805
>>
>>
>> On Thu, Jul 9, 2009 at 1:38 PM, Bo Coughlin <bo at rekindl.com> wrote:
>>>
>>> Tried - got nothing returned...little scary. - bo
>>>
>>> Bo Coughlin
>>> bo at rekindl.com
>>> 704.414.0805
>>>
>>>
>>> On Thu, Jul 9, 2009 at 1:16 PM, Paul Ramsey <pramsey at cleverelephant.ca>
>>> wrote:
>>>>
>>>> Try this:
>>>>
>>>> --
>>>> -- ST_DWithin_Sphere(lonlat-point, lonlat-point, radius-in-meters)
>>>> returns boolean
>>>> --
>>>> -- Meters/Degree @ 60N: select 1/distance_sphere('POINT(0
>>>> 60)','POINT(1 60)') = 1.79866403673916e-05
>>>> --
>>>> CREATE OR REPLACE FUNCTION ST_DWithin_Sphere(geometry, geometry, float8)
>>>>        RETURNS boolean
>>>>        AS 'SELECT $1 && ST_Expand($2,$3 * 1.79866403673916e-05) AND
>>>> $2 && ST_Expand($1,$3 * 1.79866403673916e-05) AND
>>>> ST_Distance_Sphere($1, $2) < $3'
>>>>        LANGUAGE 'SQL' IMMUTABLE;
>>>>
>>>>
>>>> SELECT m.id AS mid,
>>>> m.building_i AS mb_id,
>>>> c.id AS cid, c.building_i AS cb_id,
>>>> m.streetaddr AS m_address,
>>>> m.city AS m_city,
>>>> m.state AS m_state,
>>>> m.zip AS m_zip,
>>>> m.zip4 AS m_zip4,
>>>> round(CAST(
>>>> ST_Distance_Sphere(m.the_geom, c.the_geom)
>>>> AS numeric), 2) AS dist_meters
>>>> FROM market_locations m, customer_locations c
>>>> WHERE st_dwithin_sphere(m.the_geom, c.the_geom, 2414.016)
>>>> _______________________________________________
>>>> 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
>>
>>
>



More information about the postgis-users mailing list