<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1528" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>Hi
Everybody,</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>A few days ago I had
a problem with projection units. </FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>I had a series
of points in WGS 84 (with a projection which uses decimal degrees
as unit) </FONT></SPAN><SPAN class=633383015-30032007><FONT face=Arial
size=2> and I needed to trace a circle around them, with a radius specified
in meter.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>The 'Buffer()'
function from the postgis library allows the definition of a circle
around a geometry, but it didn't work in this situation because the radius
value must be expressed in the same unit as the reference
point.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>I found on the
Postgis mailing list some solutions which used the definition of a constant
value allowing conversions between degrees and meters.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>But it was an
unsatisfactory answer for my problem because the result of this
conversion varies with the latitude.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>I also wanted to
avoid reprojections into custom projections because I do not
master well this type of operation. </FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>I defined a function
in PL\pgSQL which more or less solve this problem (but with some error gap
because conversion is based on sheperical earth). </FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007></SPAN><SPAN class=633383015-30032007><FONT
face=Arial size=2>It defines a starting point which has the same latitude
as the latitude entered as input parameter and a longitude equals to
0.<BR>It defines another point which will "move" along the same parallel as
the starting point.<BR>It calls the 'distance_sphere' function from the
postgis library, which is based on a spherical earth, in order to calculate
the distance in meters between the source point and the moving point.<BR>We
repeat the operation until the resulting value becomes bigger or equals to
the meter value to convert.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>The last longitude
value known for the "moving" point is returned as the result for the
conversion into degree.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>This function works
and gives consistent values, but is rather slow, because it use a
loop with many iterations.</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>You can find
the source code below for comments, critics or suggestions about
improvements:</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>Franck
Theeten</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>Database
Manager</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>Royal Museum For
Central Africa</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>Tervuren,
Belgium</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>-<SPAN
class=633383015-30032007>--------------------------</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=633383015-30032007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2>--function which convert a distans in meter into a
distance in degree<BR>--Principe: the result varies with the latitude of the
conversion<BR>--Input parameter are : -'latitudevalue' latitude of the point for
the considered distance<BR> --'meter': distance to
convert<BR> -- 'precisionoutput': the precision of the output
value (in decimal degrees)<BR> --'srid' srid of the considered
projection <BR>--process:<BR>--The function defines a starting point with has
the same latitude as the latitude entered as input parameter and a longitude
equals to 0.<BR>--It akso defines another point which will "move" along the same
parallel as the starting point (is given by precisionoutput parameter).<BR>--We
call the 'distance_sphere' function from the postgis library, which is based on
a spherical earth.<BR>--to calculate the distance in meters between the source
point and the moving point,<BR>--We repeat the operation until the resulting
becomes bigger or equals to the meter value to convert.<BR>--The last longitude
value known for the "moving" point is returned as the result ot the conversion
into degree <SPAN class=633383015-30032007>.</SPAN><BR>--</FONT></DIV>
<DIV><FONT face=Arial size=2> </DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION rmca_meterToDegreelatitude(latitudevalue
double precision, meter double precision, precisionoutput double precision, srid
int)<BR>RETURNS numeric <BR>AS $$<BR>DECLARE<BR> variablelong double
precision; <BR> tmpresult double precision;<BR> geometrystart
geometry;<BR> geometrymove geometry;<BR>BEGIN<BR></DIV></FONT><FONT
face=Arial size=2></FONT>
<DIV><FONT face=Arial
size=2> variablelong:=0;<BR> tmpresult:=0;<BR> <BR> geometrystart:=GEOMETRYFROMTEXT('POINT(0
'|| latitudevalue ||')', srid);<BR> <BR> WHILE
tmpresult<=meter LOOP<BR> geometrymove :=
GEOMETRYFROMTEXT('POINT('|| variablelong ::varchar||' '||
latitudevalue::varchar||')',
srid);<BR> <BR> tmpresult=distance_sphere(geometrystart,
geometrymove);<BR> variablelong
:=variablelong+precisionoutput; <BR> END
LOOP;<BR> <BR> RETURN variablelong;<BR>END $$ LANGUAGE
plpgsql;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>-------example of
invocation: SELECT rmca_meterToDegreelatitude(27.9, 1324, 0.00005, 32733);
</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>--(latitude of the
point is 27.9°, </FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>--distance to
convert is 1324m, </FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>--accuracy is
0.000005°</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>--srid of the
projection is 32733 (WGS84 and UTM zone 33 South)</FONT></SPAN></DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial size=2>--result:->
0.0134899999999996°</FONT></SPAN></DIV></SPAN>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=633383015-30032007><FONT face=Arial
size=2><BR></FONT> </SPAN></DIV>###########################################<br><br>This message has been scanned by ICT - Africa Museum<br></BODY><!--[object_id=#africamuseum.be#]--><P><FONT face=Verdana size=1>________________________________________ </FONT></P>
<P><FONT face=Verdana size=1>30/3/2007 - Filtered through antispam by ICT</FONT></P></HTML>