[postgis-users] -- Still having trouble retrieving properties 100 meters around a point --

[Michael Fuhr]

On Thu, Sep 21, 2006 at 12:07:33AM -0600, Michael Fuhr wrote:
> SRID 32011 is NAD27 and feet; if you want meters then you could use
> 32618 (WGS84 / UTM zone 18N, which covers New Jersey).

If you're going to be handling coordinates outside of this UTM zone
then it might be better to keep the (lon lat) format and use SRID
4326 (or 4267 if the coordinates are NAD27).  In that case you'd
insert geometries without the transform:

INSERT INTO smarter.vow_properties (address, geom)
  VALUES ('12 LONG BRIDGE DR, MOUNT LAUREL, NJ 08054',
	  SetSRID(MakePoint(-74.921916, 39.940252), 4326));

You'd have to to expand points by degrees and you could use
distance_sphere() or distance_spheroid() to compute distances in
meters.  For example:

SELECT a.property_id, a.address, distance_sphere(a.geom, b.geom) AS distance
FROM smarter.vow_properties AS a
JOIN (
  SELECT SetSRID(MakePoint(-74.905868, 39.940252), 4326) AS geom
) AS b ON a.geom && Expand(b.geom, 0.0015) AND distance_sphere(a.geom, b.geom) <= 100
ORDER BY distance;

This query expands the point by 0.0015 degrees because at 40N a
distance of 0.0015 degrees longitude is about 128 meters (that value
has no significance other than being close to 100 meters and not
less than 100 meters):

test=> SELECT distance_sphere(GeomFromText('POINT(-75 40)'), GeomFromText('POINT(-75.0015 40)'));
 distance_sphere  
------------------
 127.770120450092
(1 row)

-- 
Michael Fuhr