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

Paul Ramsey pramsey at cleverelephant.ca
Thu Jul 9 11:06:07 PDT 2009


The coordinates you pasted in are valid points in the continental USA...

select st_srid(the_geom), st_astext(the_geom) from market_locations limit 1;
4326	POINT(-77.1042756692815 38.995397678429)

select st_srid(the_geom), st_astext(the_geom) from customer_locations limit 1;
4326	POINT(-100.000971435723 45.0003533555503)

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