<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Adam,<br>
<br>
perhaps you could solve the problem with this workaround:<br>
<br>
1. Transform pt1 into geometry data type with srid 4326 (coordinates
are in degrees, now)<br>
2. Create a new point by shifting pt1 for # degrees in x-direction<br>
3. Create a linestring geometry by connecting pt1 and the new point<br>
4. Transform the linestring back into geography data type<br>
5. Calculate length of linestring in meters<br>
6. Use length in meters for definition of the ST_DWithin radius<br>
<br>
It's a little bit complicated and maybe you came up with a better
idea in the meantime...<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">Am 02.06.2014 20:58, schrieb Adam
Wright:<br>
</div>
<blockquote
cite="mid:CAFFuKMDPTNp=B9+iNkJABdvuV76QEGDePJ7SXumFAz=LfwvXbQ@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>We have several tables with latitude and longitude columns
(data type: numeric) and I need to calculate whether a given
lat/lon is within # degrees of a point-radius ring. I came up
with the function below. The user supplied radius has to be in
degrees (e.g. give me all records within 5 to 25 degrees of
this lat/lon pair). Sample query: select * from mytable where
dist_check(mytable.latitude.mytable.longitude,35.6895,139.6917,5,25)=1. </div>
<div><br>
</div>
<div>Any advice on solving the same problem using the geography
data type when the input radius is supplied in degrees? </div>
<div><br>
</div>
<div>
<div><span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">CREATE
OR REPLACE FUNCTION dist_check(lat1 numeric, lon1 numeric,
lat2 numeric, lon2 numeric, innerradius numeric,
outerradius numeric)</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">pt1
geometry;</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">pt2
geometry;</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">BEGIN</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">pt1
:= ST_MakePoint(lon1,lat1);</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">pt2
:= ST_MakePoint (lon2,lat2);</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">IF
ST_DWithin(pt1,pt2,</span><span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">outerRadius)
AND NOT ST_DWithin(pt1,pt2,</span><span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">innerRadius)</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">THEN
return 1;</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">ELSE</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">return
0;</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<span
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">END
IF;</span><br
style="color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px">
<br>
</div>
<div>Thanks!</div>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>