1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need query to get max/min latitude and longitude values

Discussion in 'SQL PL/SQL' started by Nagarjuna25, Aug 30, 2017.

  1. Nagarjuna25

    Nagarjuna25 Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Hyderabad
    Team,

    I have a table with the details userid, Latitude and Longitude. I will provide the current location(latitude and longitude) of the user and I need to get the list of users who belongs 1 km.

    Input: Latitude, Longitude, Distance

    Output: List of users who belongs with in the distance.


    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this problem? We do not do other people's work but we will help YOU write the statement you want. We have to see what you've done to know where you may have gone wrong.
     
  3. Nagarjuna25

    Nagarjuna25 Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Hyderabad
    Thanks for giving reply. I need formula to calculate max / min latitude/longitude when I have certain co-ordinates. I don't want to you guys to write the code. If you guys have formula please provide it so that I can write code.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    " I need formula to calculate max / min latitude/longitude when I have certain co-ordinates. " Have you supplied any table structure or sample data? We work with what you give us.
     
  5. Nagarjuna25

    Nagarjuna25 Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Hyderabad
    Please have the sample data.

    UserId, Latitude, Longitude
    1, 17.386620, 78.320930
    2, 16.486620, 68.320930
    3, 15.586620, 58.320930
    4, 14.686620, 48.320930

    This is the table structure. If I provide input values Latitude, Longitude and radians / distance to the stored procedure I need to get the records of around 1 km distance.

    Please do the needful.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    dlon = lon2 - lon1
    dlat = lat2 - lat1
    a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
    c = 2 * atan2( sqrt(a), sqrt(1-a) )
    d = R * c (where R is the radius of the Earth)

    R = 6371 km
     
  7. Nagarjuna25

    Nagarjuna25 Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Hyderabad
    I have only one input , i.e. user latitude and longitude. do you want me to consider as below.?

    User Input Longitude : lon2
    User Input Latitude: lat2

    Table Data Longitude : lon1
    Table Data Latitude: lat1

    If this is the case then how can i get min and max co-ordinates to use in the query.? I need to calculate four values to provide in the below query.

    Query:
    SELECT * FROM USER WHERE
    ( Longitude > @calculated value1 and Longitude < @calculated value2 )
    AND
    ( Latitude > @calculated value3 and Latitude < @calculated value4)

    Could you please suggest on this.
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Simple example(not to forget about the coordinate transformation from Radin to degrees and back):
    Code (Text):

    WITH datum (UserId, Latitude, Longitude)
    AS
    (SELECT 1, 17.386620, 78.320930 FROM dual UNION ALL
    SELECT 2, 16.486620, 68.320930 FROM dual UNION ALL
    SELECT 3, 15.586620, 58.320930 FROM dual UNION ALL
    SELECT 4, 14.686620, 48.320930 FROM dual
    ),
    step1 AS
    (
    SELECT
    d.*,
    (&lon2 - d.Longitude) dlon,
    (&lon2 - d.Longitude)*3.14159265359/180 dlon_rad,
    (&lat2 - d.latitude) dlat,
    (&lat2 - d.latitude)*3.14159265359/180  dlat_rad

    FROM datum d
    ),
    step2 AS
    (
    SELECT
      st1.*,
       POWER(sin(dlat_rad/2),2) + cos(st1.latitude*3.14159265359/180) * cos(&lat2*3.14159265359/180) * POWER(sin(dlon_rad/2),2) a
    FROM step1 st1
    )
    ,step3 AS (
    SELECT st2.*,
         2 * atan2( sqrt(st2.a), sqrt(1-st2.a))  c    
    FROM step2 st2
    )
    SELECT
        st3.*,
         6371 * st3.c   d
    FROM step3 st3;


     
     
    Last edited: Aug 31, 2017
  9. muthuraj b

    muthuraj b Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    CHENNAI
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've been given a starting point and have all of the calculations done for you; you can write the query that returns the names of people within range. As was said before we do NOT do your work for you, YOU have to do it. We WILL assist you when requested.

    The ball is now in your court.
     
  11. Nagarjuna25

    Nagarjuna25 Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Hyderabad

    Thank you so much for providing the code, Now I achieve my requirement using this code.

    Thanks a lot..