Distance related helper functions

Back To PL/PGSQL/SQL Functions

  • Find UTM (WGS84) SRID for a point (in any SRID)
     -- Function: utmzone(geometry)
     -- DROP FUNCTION utmzone(geometry);
     -- Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom)) )
        FROM sometable;
    
     CREATE OR REPLACE FUNCTION utmzone(geometry)
       RETURNS integer AS
     $BODY$
     DECLARE
         geomgeog geometry;
         zone int;
         pref int;
    
     BEGIN
         geomgeog:= ST_Transform($1,4326);
    
         IF (ST_Y(geomgeog))>0 THEN
            pref:=32600;
         ELSE
            pref:=32700;
         END IF;
    
         zone:=floor((ST_X(geomgeog)+180)/6)+1;
    
         RETURN zone+pref;
     END;
     $BODY$ LANGUAGE 'plpgsql' IMMUTABLE
       COST 100;
    
  • An ST_Dwithin like function that uses indexes for degree point data

 http://www.postgis.org/pipermail/postgis-users/2009-June/023711.html (use for pre PostGIS 1.5.)

Also refer to Examples Find Near by Lat Lon

Caveat from newsgroup: Here's a simple 80/20 solution for people with lon/lat tables of points wanting to do distance searches. It only works for places less than 60degrees north or south, and only for points. If you know your data is all more southerly than 60d, adjust the constant factor to make the index portion of the search more selective (and hence, efficient (faster)).

--
-- 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;

* Neighbor Search Nearest Neighbor Search