Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: Latitude and Longitude Data Type Issue

    I am using DB2 to create boundary boxes around a central point (in this case a company location), by finding the maximum and minimum latitude and longitude points. I am then trying to count the number of companies that fall within the box drawn by the maximum and minimum latitude and longitude to get an idea of the density. The SQL statement to do this is here:

    SELECT DISTINCT
    S.BACKUP,
    COUNT(S.BACKUP) AS "NUMBER OF POINTS"

    FROM BRDB_EXTRA.SUPPLEMENTAL S
    WHERE
    (S.CLIENT_ID_3*(3.141593/180))>=((S.CLIENT_ID_3*(3.141593/180)) - 0.000785)
    AND
    (S.CLIENT_ID_3*(3.141593/180))<= ((S.CLIENT_ID_3*(3.141593/180)) + 0.000785)
    AND
    (S.CLIENT_ID_2 *(3.141593/180))>=((S.CLIENT_ID_2*(3.141593/180)) - (ASIN(SIN(0.000785)/COS(CAST(S.CLIENT_ID_3 AS FLOAT) *(3.141593/180)))))
    AND
    (S.CLIENT_ID_2*(3.141593/180)) <= ((S.CLIENT_ID_2*(3.141593/180)) + (ASIN(SIN(0.000785)/COS(CAST(S.CLIENT_ID_3 AS FLOAT) *(3.141593/180)))))

    GROUP BY
    S.BACKUP


    S.BACKUP is the companies name
    S.CLIENT_ID_2 is the Longitude in Degrees in the form of (-81.90155649)
    S.CLIENT_ID_3 is Latitude Degrees in the form of (41.37859739)

    When I run the query, error code -420 comes up, so I think the error is a data type mismatch, but I don't know how to fix it! Please Help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Theres so many calculations going on there that short of stepping through each element it wkuld be hard to say. I take it you have already excluded potentialy invalid values.

    Hiw big is your bounding box. Assuming you are using lat/long although its impossible to know from your column names all you need is to know the position of one business apply your correction factors to create the boundaries.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?
    What are the data type for CLIENT_ID_2 and CLIENT_ID_3?

    I got SQL0802N by using DECIMAL(10,8) for CLIENT_ID_2 and CLIENT_ID_3 on DB2 9.7.5.
    SQL0802N Arithmetic overflow or other arithmetic exception occurred.
    SQLSTATE=22003

    Anyway,
    try the followings.
    (1) Use floationg-point operations to avoid SQL0802N.

    (2) Use RADIANS function rather than CLIENT_ID_3*(3.141593/180).
    Note:
    RADIANS - IBM DB2 9.7 for Linux, UNIX, and Windows
    The RADIANS function returns the number of radians for an argument that is expressed in degrees.

    The argument can be any built-in numeric data type.
    If the argument is decimal floating-point, the operation is performed in decimal floating-point;
    otherwise, the argument is converted to double-precision floating-point for processing by the function.
    (3) Remove unnecessary parentheses.

    (4) Separate "a central point (in this case a company location)" and "companies that fall within the box".


    As a consequence,
    here is an example considering (1) to (4).
    Code:
    SELECT t.BACKUP
         , COUNT(s.BACKUP) AS "NUMBER OF POINTS"
     FROM  BRDB_EXTRA.SUPPLEMENTAL AS t /* central point(s?) */
     LEFT  OUTER JOIN
           BRDB_EXTRA.SUPPLEMENTAL AS s /* companies that fall within the box */
      ON   RADIANS(s.CLIENT_ID_3)
              BETWEEN RADIANS(t.CLIENT_ID_3) - 0.000785
                  AND RADIANS(t.CLIENT_ID_3) + 0.000785
       AND RADIANS(s.CLIENT_ID_2)
              BETWEEN RADIANS(t.CLIENT_ID_2) - ASIN( SIN(0.000785) / COS( RADIANS(t.CLIENT_ID_3) ) )
                  AND RADIANS(t.CLIENT_ID_2) + ASIN( SIN(0.000785) / COS( RADIANS(t.CLIENT_ID_3) ) )
    /*
     choose a central point (a company)
     For example:
     WHERE t.BACKUP = 'a company''s name'
    */
     GROUP BY
           t.BACKUP
    ;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are not
    I am then trying to count the number of companies that fall within the box drawn by the maximum and minimum latitude and longitude to get an idea of the density.
    do you actually mean with a 15 mile circumference?
    or do you mean radius?

    computationally a bounding box is far easier as all you need do is work out the co-ordicnates of the corner points and then retrieve all rows within those co-ordinates rather than calculate an arbitrary n mile radius. its just as arbitrary as the bounding box as you are calculating the point to point distance NOT the actual transit distance
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by healdem View Post
    so you are not
    I am then trying to count the number of companies that fall within the box drawn by the maximum and minimum latitude and longitude to get an idea of the density.
    do you actually mean with a 15 mile circumference?
    or do you mean radius?

    computationally a bounding box is far easier as all you need do is work out the co-ordicnates of the corner points and then retrieve all rows within those co-ordinates rather than ...
    I think latitude 0.000785 radian is 5km.
    It need adjustment to longitude( i.e. "ASIN( SIN(0.000785) / COS( RADIANS(t.CLIENT_ID_3) ) )" ).
    But, I don't know exactly the expression is right or not, because of my poor mathematical skills.
    (If the boundary box was small enough, "0.000785 / COS( RADIANS(t.CLIENT_ID_3) )" might be a good approximation.)

    The conditions I wrote was
    Code:
      ON   RADIANS(s.CLIENT_ID_3)
              BETWEEN RADIANS(t.CLIENT_ID_3) - 0.000785
                  AND RADIANS(t.CLIENT_ID_3) + 0.000785
       AND RADIANS(s.CLIENT_ID_2)
              BETWEEN RADIANS(t.CLIENT_ID_2) - ASIN( SIN(0.000785) / COS( RADIANS(t.CLIENT_ID_3) ) )
                  AND RADIANS(t.CLIENT_ID_2) + ASIN( SIN(0.000785) / COS( RADIANS(t.CLIENT_ID_3) ) )
    It (might be equivalent to OP(handa11)'s conditions, if I didn't misunderstood OP's intention.)
    means like
    Code:
           latitude  s
              BETWEEN latitude  t - 5km AND latitude  t + 5km
       AND longitude s
              BETWEEN longitude t - 5km AND longitude t + 5km
    /*
    where
     t: central point
     s: companies that fall within the box 
    */
    and just looking for s that fall within a 10km squre box (5km in 4 directions north/east/south/west from t).

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think latitude 0.000785 radian is 5km.
    The length of the equator(the circumference of earth) is about 40000 km.
    So, "latitude 0.000785 radian" is
    40000 km * 0.000785 / (3.14159265 * 2) = 4.997km ≒ 5km

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The formulae I've used for Geographical calcualtions laregly came from here:-
    Calculate distance and bearing between two Latitude/Longitude points using Haversine formula in JavaScript
    and
    Aviation Formulary V1.46
    translated in VBA and Lava as required.

    Id check with the customer if they actually NEED 15 mile radius or thats just how they defined the problem without understand whatr they are asking. If they will accept a bounding box, say 30mile square centred on the target company then in psuedo code

    find the location of the specified target company
    work out your conversion factor
    convert miles into nm 1 mile = 08.Nm, so 15 miles is 13.0263nm
    1 Nm is one minute of arc at sea level, or 1852m/6076ft
    60 minutes in one degree.
    so 13.0263 is 0.217105 degrees

    so your bounding box is -0.217105, -0.217105; -0.217105, +0.217105;+0.217105, +0.217105;+0.217105, -0.217105;

    or psuedo SQL
    where lat between targetlat -0.21710 and targetlat +0.21710
    AND long between targetlong -0.21710 and targetlong +0.21710
    targetlat & targetlong being the specified origin

    or if you want to stuff it into a SP and generalise it
    $offset = $targetdistance*0.8684
    where lat between targetlat - $offset and targetlat + $offset
    AND long between targetlong - $offset and targetlong + $offset

    targetlat & targetlong being the specified origin.
    targetdistance is the specified range (in your example 15miles)
    ive used $ to denote a workings variable (haven't got a clue what that is in DB2 speak

    I appreciate that this doesn't give the companies within a bounding circle, but a bounding box however its significantly less complex, less load on the server. if this is a faciltiy you expect to be used significnatly then it may well be a smarter way of getting a relevant result

    Years back we had to do some jiggery pokery with Lat / Long's. the client specified a radius.. but it quickly bogged down the server. checking back with the client they weren't wedded to the radius concept, they just wanted an indicative metric. We offered a boiunding box, which they found was just as valid as bounding circle for their purposes and was dramatically less demanding on the db server
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1)
    The formulae I've used for Geographical calcualtions laregly came from here:-
    You didn't wrote concrete query example with using the formulae,
    so I couldn't know what your intended query was, and it was difficult to mention for your comments.

    Could you please supply some working SQL code using the fomulae?; not only write your opinion by descriptions.


    (2) Your mentioned numbers like
    "15 mile" , "30mile square" , "nm 1 mile = 08.Nm, so 15 miles is 13.0263nm" , "1852m/6076ft" , "13.0263 is 0.217105 degrees" , "-0.217105" , "-0.217105" , so on ...
    were not appear in other posts in this thread (only you metioned those numbers).

    Note: Especially I confused "nm", because ny dictionary wrote nm are nanometer; natural mile; nonmetallic.
    So, I guessed first nm might be natural mile.
    But, I found later the phrase "15 miles is 13.0263nm".
    Is that mean "nm in 13.0263nm was not natural mile"?

    Then, I want you to work with MSK units or SI units, to make more easy conversations with you and me(our?).
    If you thought that using ft or miles had some benifis, please explain the rational.


    (3) And, I want you to use DB2 SQL syntax to explain your opinions.
    Because, I am not familiar with the syntax like
    $offset = $targetdistance*0.8684

    Variables starting with $ are not popular in SQL or SQL stored procedure syntax (in DB2).
    And, what was the number 0.8684?

    (4)
    We offered a boiunding box, which they found was just as valid as bounding circle for their purposes and was dramatically less demanding on the

    db server
    OK, I thought also that the bounding box and the bounding circle were differen stuff
    (and I intended to offer a query based on the bounding box concept).

    If you thought that my sample query(based on bounding box) was not appropriate or confused,
    please supply an example of SQL query which you coceived.

    Or, if you thought that bounding circle was useful same or than boundary box,
    please supply an example of SQL query using bounding circle concept.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Nm is nautical miles, the conversion factor from miles to nautiCal miles is 0.8684.
    The reason for converting to nm is that it makes the maths easy and easy to understand.
    A nautical mile is defined as one minute of arc (1/60th of a degree) at sea level for latitude,irrespective of longitude and one minute of arc for longitude at the equator.

    I cant give you a worked example in db2. I dont use it. My use of $ was to indicate in a stored procedure that it was a parameter.

    Essentially what i am suggesting is that rather than use the corrected version of the op`s equation calculate what the corners of the bounding box are (effectively that means the offset) and apply that offset in a simple where clause.

    That means you are applying a simple where clause rather trying to using a complex formula on every to calculate if that row falls within the box.

    So the implementation is.
    Calculate the offset.
    Convert statute miles (the required distance) into nautical miles (multiply by 0.8684) and divide the product by 60. This gives you the offset expressed as decimal degrees.

    Finding the lat/long of the origin you can write a simple where clause.
    Where latitude between origin_latitude - offset and origin_latitude + offset and longitude between origin_longitude - offset and origin_longitude + offset

    How you express that in db2 i dunno, i dont use db2. I have used this in mysql and sql server. The latter as a stored procedure.

    If you need precision you should adjust the longitude bounding offset by the difference between the logical nm in longitude at the equator (0 latitude) and the measured nm at the actual latitude, and use one of the formulae from the supplied reference.

    Its a heck of a lot quicker to apply the bounding box as a where clause (especially on indexed lat/long columns) than to calculate each row.

    Navigation is one of those measurement systems that doesn`t fit well to the metric system. It pre dates si.
    Last edited by healdem; 12-07-13 at 19:34.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I might understand some of your points.
    (1) Compare degrees with degrees, not to compare radians with radians.
    (2) Calculate offsets first. Then use the offsets to calculate boundaries.

    Example 2: a query considering (1) and (2)
    LEFT OUTER JOIN was used to put all(find central point, calculate box boundaries, find companies that fall within the box) in a query.
    Code:
    SELECT t.BACKUP
         , COUNT(s.BACKUP) AS "NUMBER OF POINTS"
     FROM  BRDB_EXTRA.SUPPLEMENTAL AS t /* central point(s) */
     CROSS JOIN
           LATERAL
           (SELECT box_size / 40000 * 360 AS offset_latitude /* degrees */
                 , DEGREES(
                      ASIN(
                         SIN( box_size / 40000 * 3.141592654 * 2 )
                       / COS( RADIANS(t.CLIENT_ID_3) )
                      )
                   ) AS offset_longitude /* degrees */
             FROM  (VALUES FLOAT(5./*km*/) ) AS f(box_size)
           )
     LEFT  OUTER JOIN
           BRDB_EXTRA.SUPPLEMENTAL AS s /* companies that fall within the box */
      ON   s.CLIENT_ID_3
              BETWEEN t.CLIENT_ID_3 - offset_latitude
                  AND t.CLIENT_ID_3 + offset_latitude
       AND s.CLIENT_ID_2
              BETWEEN t.CLIENT_ID_2 - offset_longitude
                  AND t.CLIENT_ID_2 + offset_longitude
    /*
     choose central point (a company)
     For example:
     WHERE
           t.BACKUP = 'company''s name'
    */
     GROUP BY
           t.BACKUP
    ;
    Use of miles/nautical miles might make some calculations a little easier.
    But, I'm not familir that units and feel troublesome to remember the conversion factors.
    Last edited by tonkuma; 12-08-13 at 06:12.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I can understand using the complex formula if there is a need to rank the locations that fall within the bounding box (ie select rows and order them in ascending distance). if that is a requirement then my proposal fails.

    if all you need is rows that fall within a bounding box then use the where clause

    The last time I hit this sort of issue all we needed was a list of locations that fell within a specified bounding box. it was a 'how many potential customers exist within n miles of a distribution depot' issue, as opposed to find the possible locations in ascending distance order.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •