[postgis-users] ST_DWithin advice

[Birgit Laggner]

Hi Adam,

perhaps you could solve the problem with this workaround:

1. Transform pt1 into geometry data type with srid 4326 (coordinates are 
in degrees, now)
2. Create a new point by shifting pt1 for # degrees in x-direction
3. Create a linestring geometry by connecting pt1 and the new point
4. Transform the linestring back into geography data type
5. Calculate length of linestring in meters
6. Use length in meters for definition of the ST_DWithin radius

It's a little bit complicated and maybe you came up with a better idea 
in the meantime...

Regards,

Birgit.



Am 02.06.2014 20:58, schrieb Adam Wright:
> 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.
>
> Any advice on solving the same problem using the geography data type 
> when the input radius is supplied in degrees?
>
> CREATE OR REPLACE FUNCTION dist_check(lat1 numeric, lon1 numeric, lat2 
> numeric, lon2 numeric, innerradius numeric, outerradius numeric)
>
> pt1 geometry;
> pt2 geometry;
> BEGIN
> pt1 := ST_MakePoint(lon1,lat1);
> pt2 := ST_MakePoint (lon2,lat2);
> IF ST_DWithin(pt1,pt2,outerRadius) AND NOT ST_DWithin(pt1,pt2,innerRadius)
> THEN return 1;
> ELSE
> return 0;
> END IF;
>
> Thanks!
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140613/3e37d40b/attachment.html>