Is this query the same as that query?

Report · Jun 12, 2009

I am using zipfinder.cfc which utilizes the following query to retrieve zip codes within a certain bounds of another zip code. This query is the original query that was included in the cfc:

<cfquery name="TEST" datasource="TEST">
            select       zipcode as zip, state, city,
                             SQRT(
                       SQUARE(#lat_miles# * (ipLATITUDE - (#z1.latitude#)))
                            +
                            square(#lon_miles# * (ipLONGITUDE - (#z1.longitude#)))
                            ) as dist
            from      ZipCodes
            where latitude between #lat1# AND #lat2#
            AND longitude between #lon1# AND #lon2#
            order by dist asc
        </cfquery>

However, that query seems to always throw this error:

Error Executing Database Query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(69.1 * (latitude - (38))) + SQUARE(54.5375842556 * (longitude - (-' at line 3 <br>The error occurred on line 167.

The error seems to stemming from the "SQUARE" and "square" uses within the sql, because when I remove them I have no errors. So I assume that both SQUARE and square, are squaring the equation that follows. So I changed the query to:

    <cfquery name="TEST" datasource="TEST">
             select       zipcode as zip, state, city,
                              SQRT(
                      (#lat_miles# * (ipLATITUDE - (#z1.latitude#))) * (#lat_miles# * (ipLATITUDE - (#z1.latitude#)))
                             +
                             (#lon_miles# * (ipLONGITUDE - (#z1.longitude#)))* (#lon_miles# * (ipLONGITUDE - (#z1.longitude#)))
                             ) as dist
             from      ZipCodes
             where latitude between #lat1# AND #lat2#
             AND longitude between #lon1# AND #lon2#
             order by dist asc
         </cfquery>

So now instead of it using a SQUARE function it just multiples the equation by itself, effectively squaring it. Now, this seems to be working with the exception that my results are not always accurate and often times missing many local zip codes within the bounds. So I am somewhat confused as to why I even had to change SQUARE in the first place. Is that an outdated function in mysql? So I suppose my question is, are these two queries the same? If not, how can I change it so I can utilize the SQUARE function in mysql so my results are more accurate. If they are the same, any ideas why my results would be inaccurate?

Thanks in advance,

Mark

Report · Jun 12, 2009

10 seconds of searching on Google seems to indicate that the SQUARE() function is a MSSQL function. It may not be implemented or at least not implemented under the same name in or database management systems such as MySQL. The being said, I would be a bit surprised that there is not a similar function available.

How ever your logic should properly do the same thing since yes squaring a function would be to just multiple it by itself. I would just carefully work through all your parenthesis sets to make sure all the functions are being evaluated in the exact order you need them to be.

As for not getting the results you want,

It was many many moons, at least a couple of years ago, I read a long article about doing this type

Report · Jun 12, 2009

That would explain why everytime I searched for "mysql square" I was returned junky results!

Yea, I don't know why my results are so inaccurate. Ill search 20002 as a zip code and it wont return 20007 as a close zipcode even though its about a mile away, yet it will return NJ zip codes 25 miles away. Strange. I will continue looking into the accuracy issue and see if there are any updated methods of doing this.

Thanks for the help!

Report · Jun 16, 2009

I suggest that you look closely at the MySQL function library reference material for the version of MySQL that you are running ... and, consider upgrading if you need to.

In any case, you might be able to get more precision by asking for it, e.g. with the CAST() and CONVERT() functions.

The MySQL documentation (as recently as 5.0) discusses precision math functions.

Most significantly, though, it seems that MySQL now offers a library of geo-spatial (GIS) functions which might make it possible for you to go well beyond the Pythagorean Theorem in addressing your problem. The boundaries of each ZIP Code are, of course, a closed polygon, and what you are really looking for here is to determine containment of a chosen point within this and/or adjacent polygons. MySQL 5.0, for instance, appears to have considerable direct support for this. You do have to purchase these data sets.

See: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html