This is where a slightly less easy to use implementation that I put in the PostGIS Wiki as a PL/PGSQL function presents a slightly more robust approach to the problem. The use of this function simply returns the UTM SRID for the appropriate zone for a point that you pass in (note: this could be expanded to be center of extent or whatever pretty easily). So the use of this would be:<br>
select blah from a,b where st_dwithin(transform(a.the_geom,utmzone(a.the_geom)),transform(b.the_geom,utmzone(a.the_geom)),100);<br><br>While being quite a bit more verbose, at least this approach holds the zone constant. Granted it is still rife with the problem of geometries being a distance similar to the size of a UTM zone or more apart or sizes similar to UTM zones, but it at least holds to one zone per comparison.<br>
<br>David<br><br><dl><dd> CREATE OR REPLACE FUNCTION utmzone(geometry) RETURNS integer AS
</dd><dd> $BODY$
</dd><dd> declare
</dd><dd> geomgeog geometry;
</dd><dd> zone int;
</dd><dd> pref int;
</dd><dd> begin
</dd><dd> geomgeog:=transform($1,4326);
</dd><dd> if (y(geomgeog))>0 then
</dd><dd> pref:=32600;
</dd><dd> else
</dd><dd> pref:=32700;
</dd><dd> end if;
</dd><dd> zone:=floor((x(geomgeog)+180)/6)+1;
</dd><dd> return zone+pref;
</dd><dd> end;
</dd><dd> $BODY$ LANGUAGE 'plpgsql' immutable;</dd></dl><br><br><div class="gmail_quote">On Thu, May 15, 2008 at 9:57 AM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">The danger of this approach is that it falls apart as people start<br>
pushing the limits...<br>
<br>
select blah from a, b where st_dwithin(st_utm(a.geom),st_utm(b.goem),100)<br>
<br>
It's a pandora's box... as are all things geodetic<br>
<font color="#888888"><br>
p<br>
</font><div><div></div><div class="Wj3C7c"><br>
On Thu, May 15, 2008 at 7:45 AM, Markus Schaber <<a href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>> wrote:<br>
> Hi, Paul,<br>
><br>
> "Paul Ramsey" <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br>
><br>
>> There's no universal answer to this, but I have long thought that a<br>
>> simple answer suitable for may people would be a ST_UTM(geom) wrapper<br>
>> on transform that picks the appropriate UTM zone for a given geometry.<br>
>> It would work perfectly well for any collection of small objects. It<br>
>> would fall apart for larger things like states and countries that are<br>
>> of similar size to a UTM zone.<br>
><br>
> The function could simply use the centroid of the bounding box, that<br>
> shoul be fast enough.<br>
><br>
><br>
> Regards,<br>
> Markus<br>
><br>
> --<br>
> Markus Schaber | Logical Tracking&Tracing International AG<br>
> Dipl. Inf. | Software Development GIS<br>
><br>
> Fight against software patents in Europe! <a href="http://www.ffii.org" target="_blank">www.ffii.org</a><br>
> <a href="http://www.nosoftwarepatents.org" target="_blank">www.nosoftwarepatents.org</a><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>************************************<br>David William Bitner