Latitude and longitude calulations

[cadams]

OK tech heads,

With the advent of Cognos 11.0.8 we have the ability to report using maps with Latitude and Longitude, I have a requirement to list coordinates that are X distance from point A.

so for example I work at 38.8977° N, 77.0365° W, Donald has a headache, and would like to know all the pharmacies within 5 miles.

[aussieadam]

if you have longitude/latitudes of all pharmacy places it's pretty simple with a calculation.
I have personally done this in a function on our database. would just have to look at distance returned <= 5.
CREATE OR REPLACE FUNCTION latlon_distance(
    lat1 numeric,
    lon1 numeric,
    lat2 numeric,
    lon2 numeric)
  RETURNS numeric AS
$BODY$
DECLARE   
   radius numeric;
   dlon numeric;
   dlat numeric;
   a numeric;
   c numeric;
   d numeric;
BEGIN
   dlon = lon2-lon1;
   dlat = lat2-lat1;
   radius = 3961;
   
   a = (sin(radians(dlat/2)))^2 + cos(radians(lat1)) * cos(radians(lat2)) * (sin(radians(dlon/2)))^2;
   c = 2 * atan2(sqrt(a), sqrt(1-a));
   d = radius * c;
   return d;
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Honestly unsure if cognos supports sine and cosine, but if your database does you should be able to figure out a way to get that in a filter.

[dougp]

I think this isn't a Cognos question.  It's a question regarding filtering the data by using spatial functions in the database.  You'll probably want to use a stored procedure or view.

If you can't create stored procedures or views, you'll need to contact your database administrator about this.

If you can create stored procedures or views, you'll need to know what RDBMS you are using.  For Microsoft SQL Server, this information may help:
Buffer
Distance
Intersects
Intersection
All of those pages have a table of contents from which you can learn about other SQL Spatial functions.

I haven't had the opportunity to play with this technology much.  It may be possible to use these functions directly in the FM model.
Different combinations of these functions may produce different results and have different performance implications.