[postgis-users] Selecting geocoded postal codes within a given distance ( miles for example )

Hugh W. O'Brien hugho at intelis.com
Thu Dec 19 15:11:27 PST 2002

ok Paul, progress report and problem. :-)  

I am trying to find a conversion for Lon/meters and Lat/meters as you 
suggested in a previous post in this thread.  I am using long lats in 
Houston, TX.

Sample sql file:

-- calculating number of decimal degrees long/meter and decimal number 
of degrees lat/meter around the following point:
-- long -95.09846 lat 29.55475

-- calculating number of decimal degrees long/meter
select length_spheroid( 'LINESTRING( -95.09846 29.55475, -93.09846 
29.55475 )', 'SPHEROID["GRS_1980",6378137,298.257222101]' ) AS "Degrees 
Longitude Per Meter" ;
This result is always NaN for points with the same latitude.*

-- calculating number of decimal number of degrees lat/meter
select length_spheroid( 'LINESTRING( -95.09846 29.55475, -95.09846 
27.55475 )', 'SPHEROID["GRS_1980",6378137,298.257222101]' ) AS "Degrees 
Latitude Per Meter" ;

*This works just fine.

*What am I doing wrong?

-- Hugh W. O'Brien

Paul Ramsey wrote:

> Then the fastest way to get something in operation is to calculate the 
> miles/longitude value for somewhere in the south of Texas and use that 
> everwhere. Construct query boxes in lat/lon using the (fixed) 
> miles/latitude and miles/longitude values and then compare distances 
> using distance_spheroid... the SQL will end up looking something like:
> select * from postaltable where
>   the_geom && BOX3D( X - R * Lon/Miles , Y - R * Lat/Miles , etc etc )
>   and
>   distance_spheroid( POINT( X Y ) , the_geom , SPHEROID ) < R
> Note that I have completely bastardized the constructors for the 
> various objects above in the interests of understandability. I assume 
> you can fill out the details yourself...
> P.
> Hugh W. O'Brien wrote:
>> Unfortunately, I must support all US postal codes so all of 
>> continental US and it's territories.  I might also have to include 
>> support for Canada in the future.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20021219/fea90798/attachment.html>

More information about the postgis-users mailing list