Professional Web Applications Themes

Can it be done better? - MySQL

Chow can i do something like that: SELECT rfc.radioid, rank, CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency) AS callsign, earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND dist<50 ORDER BY rank DESC, dist; earth_distance_miles i a function. I'm getting errors trying to execute this query. I could do like this: Chow can i do something like that: SELECT rfc.radioid, rank, CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency) AS callsign, earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND earth_distance_miles(zipt.latitude,longitude,lat,l on)<50 ORDER BY rank DESC, dist; but is it not cosing distance to be ...

  1. #1

    Default Can it be done better?

    Chow can i do something like that:

    SELECT
    rfc.radioid,
    rank,
    CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency) AS
    callsign,
    earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist
    FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt
    WHERE zipt.zip=92627 AND dist<50
    ORDER BY rank DESC, dist;

    earth_distance_miles i a function. I'm getting errors trying to execute
    this query.

    I could do like this:

    Chow can i do something like that:

    SELECT
    rfc.radioid,
    rank,
    CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency) AS
    callsign,
    earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist
    FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt
    WHERE zipt.zip=92627 AND
    earth_distance_miles(zipt.latitude,longitude,lat,l on)<50
    ORDER BY rank DESC, dist;

    but is it not cosing distance to be calculated twice?
    Ralph Guest

  2. #2

    Default Re: Can it be done better?

    Ralph wrote: 

    Hi, Ralph,

    Sorry, as you found, you can't compute something in the SELECT clause
    and use it in the WHERE clause. Part of this would be that the SELECT
    clause may not even be evaluated if the WHERE clause fails (and no, you
    can't calculate in the WHERE clause and use in the SELECT clause, either.

    Yes, and you're wasting a lot of time needlessly calculating distance,
    especially if you have a large table. For instance, if you had all 40K+
    zip codes, you would be computing (at least) 40K+ in every query.
    That's a lot of calculations - especially since you're doing trig functions.

    Much better would be to use a scripting language such as PHP. Determine
    a box X distance N, S, E and W of your point. This will give you the
    minimum and maximum latitude and longitude; everything must now be X
    distance from that point.

    Now create an index on long/lat (or lat/long) and perform your search
    operation, with the additional info, i.e.

    something like:

    SELECT
    rfc.radioid,
    rank,
    CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency)
    AS callsign,
    earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist
    FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt
    WHERE zipt.zip=92627 AND
    latitude >= min_latitude AND latitude <= max_latitude AND
    longitude >= min_longitude AND longitude <= max_longitude AND
    earth_distance_miles(zipt.latitude,longitude,lat,l on)<50
    ORDER BY rank DESC, dist;

    You might be able to get this into a function in MySQL 5.x; I haven't tried.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Can it be done better?

    Jerry Stuckle wrote: 
    >
    > Hi, Ralph,
    >
    > Sorry, as you found, you can't compute something in the SELECT clause
    > and use it in the WHERE clause. Part of this would be that the SELECT
    > clause may not even be evaluated if the WHERE clause fails (and no, you
    > can't calculate in the WHERE clause and use in the SELECT clause, either.
    >
    > Yes, and you're wasting a lot of time needlessly calculating distance,
    > especially if you have a large table. For instance, if you had all 40K+
    > zip codes, you would be computing (at least) 40K+ in every query. That's
    > a lot of calculations - especially since you're doing trig functions.
    >
    > Much better would be to use a scripting language such as PHP. Determine
    > a box X distance N, S, E and W of your point. This will give you the
    > minimum and maximum latitude and longitude; everything must now be X
    > distance from that point.
    >
    > Now create an index on long/lat (or lat/long) and perform your search
    > operation, with the additional info, i.e.
    >
    > something like:
    >
    > SELECT
    > rfc.radioid,
    > rank,
    > CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency)
    > AS callsign,
    > earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist
    > FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt
    > WHERE zipt.zip=92627 AND
    > latitude >= min_latitude AND latitude <= max_latitude AND
    > longitude >= min_longitude AND longitude <= max_longitude AND
    > earth_distance_miles(zipt.latitude,longitude,lat,l on)<50
    > ORDER BY rank DESC, dist;
    >
    > You might be able to get this into a function in MySQL 5.x; I haven't
    > tried.
    >[/ref]
    Thank you for response.

    In your example I still calculate earth_distance_miles twice though. Is
    there a way do do it only once. With sub queries, functions or something
    else. I'm reading about mySQL but i have not found the answer yet :(

    Ralph
    Ralph Guest

  4. #4

    Default Re: Can it be done better?

    Ralph wrote: 
    >>
    >>
    >> Hi, Ralph,
    >>
    >> Sorry, as you found, you can't compute something in the SELECT clause
    >> and use it in the WHERE clause. Part of this would be that the SELECT
    >> clause may not even be evaluated if the WHERE clause fails (and no,
    >> you can't calculate in the WHERE clause and use in the SELECT clause,
    >> either.
    >>
    >> Yes, and you're wasting a lot of time needlessly calculating distance,
    >> especially if you have a large table. For instance, if you had all
    >> 40K+ zip codes, you would be computing (at least) 40K+ in every query.
    >> That's a lot of calculations - especially since you're doing trig
    >> functions.
    >>
    >> Much better would be to use a scripting language such as PHP.
    >> Determine a box X distance N, S, E and W of your point. This will
    >> give you the minimum and maximum latitude and longitude; everything
    >> must now be X distance from that point.
    >>
    >> Now create an index on long/lat (or lat/long) and perform your search
    >> operation, with the additional info, i.e.
    >>
    >> something like:
    >>
    >> SELECT
    >> rfc.radioid,
    >> rank,
    >> CONCAT_WS(' ', rfc.fac_callsign, rfc.fac_service, rfc.fac_frequency)
    >> AS callsign,
    >> earth_distance_miles(zipt.latitude,longitude,lat,l on) AS dist
    >> FROM radio_stations_tbl AS rfc, zipcodes_tbl AS zipt
    >> WHERE zipt.zip=92627 AND
    >> latitude >= min_latitude AND latitude <= max_latitude AND
    >> longitude >= min_longitude AND longitude <= max_longitude AND
    >> earth_distance_miles(zipt.latitude,longitude,lat,l on)<50
    >> ORDER BY rank DESC, dist;
    >>
    >> You might be able to get this into a function in MySQL 5.x; I haven't
    >> tried.
    >>[/ref]
    > Thank you for response.
    >
    > In your example I still calculate earth_distance_miles twice though. Is
    > there a way do do it only once. With sub queries, functions or something
    > else. I'm reading about mySQL but i have not found the answer yet :(
    >
    > Ralph[/ref]

    Ralph,

    As I said - no, you can't, as long as you have to do it in the SELECT
    statement. I do that computation after the results are returned to the app.

    But for a 50 mile radius this way you would only be performing the
    calculation for maybe 25-100 zip codes instead of > 40K as in your
    example. Just out of curiosity - would it be faster to do 40K
    calculations once, or 100 calculations twice?


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Can it be done better?


    Jerry Stuckle wrote: 
    > > Thank you for response.
    > >
    > > In your example I still calculate earth_distance_miles twice though. Is
    > > there a way do do it only once. With sub queries, functions or something
    > > else. I'm reading about mySQL but i have not found the answer yet :(
    > >
    > > Ralph[/ref]
    >
    > Ralph,
    >
    > As I said - no, you can't, as long as you have to do it in the SELECT
    > statement. I do that computation after the results are returned to the app.
    >
    > But for a 50 mile radius this way you would only be performing the
    > calculation for maybe 25-100 zip codes instead of > 40K as in your
    > example. Just out of curiosity - would it be faster to do 40K
    > calculations once, or 100 calculations twice?
    >[/ref]
    I think that the route I would take to achive the results I require is
    by using subselects in the FROM clause. You can give the subselect an
    alias and then the calculations only have to be done once. Plus you'd
    be able to use the table alias in the WHERE clause.

    The only catch is that you must be running MySQL 4.2 or higher.

    I am not an expert and might be missing the point as I usually do. I
    post on here daily asking for help, but thought I should give my 2
    cents anyway.

    All the best.

    Daz.

    Daz Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139