List: General Discussion « Previous MessageNext Message » From: M5 Date: February 4 2007 2:36pm Subject: Re: How to SELECT rows closest to lat/lng -- USING SPATIAL FUNCTIONS View as plain text
```Thanks! That really seems to do the trick. Amazing. Now, for some
stupid questions: The values 69.1 and 57.3... are you aware of some
documentation or tutorial explaining these values and the math? I'd
like to understand why it works.

Also, I would like to figure out how to, if possible, speed this up.
The query takes ~3.5 on a table of 800,000+ records--which is good
considering everything (e.g., that it works, and there's no index here).

Can anyone think of how this might run faster, either via spatial
extensions, or some other silver bullet? (Also, it appears the
distance value is returned as KMs--is that right?)

...Rene

On 3-Feb-07, at 7:27 AM, Lars Schwarz wrote:

> haven't followed the complete thread so i don't know what you're
> after, but maybe this helps?
>
> SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - [startlat]
> ) , 2 ) + POW( 69.1 * ( [startlng] - longitude ) * COS( latitude /
> 57.3 ) , 2 ) ) AS distance FROM foobar ORDER BY distance ASC
>
> where [starlat] and [startlng] is the position where to start
> messuring the distance.
>
> On 2/3/07, M5 <m5@stripped> wrote:
>> Thanks for the reply... It returns results, but not exactly what I am
>> hoping for--basically, I get the nearest latitudes (but not near
>> longitude), and nearest longitude (but not near latitudes). In other
>> words, it doesn't return the closest lat/lng pair... Also, the query
>> takes about 8 seconds to run, so maybe there's a better way?
>>
>> I've been looking at the spatial extensions, and made some progress.
>> I created a new column of type POINT. Here's the table definition:
>>
>> CREATE TABLE `map_data` (
>>    `id` int(10) unsigned NOT NULL auto_increment,
>>    `meridian` tinyint(3) unsigned NOT NULL,
>>    `range` tinyint(3) unsigned NOT NULL,
>>    `township` tinyint(3) unsigned NOT NULL,
>>    `section` tinyint(3) unsigned NOT NULL,
>>    `quartersection` varchar(2) collate latin1_general_ci NOT NULL,
>>    `latitude` decimal(10,8) NOT NULL,
>>    `longitude` decimal(12,8) NOT NULL,
>>    `coordinates` point default NULL,
>>    PRIMARY KEY  (`id`),
>>    KEY `latlng` (`latitude`,`longitude`)
>> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
>> AUTO_INCREMENT=0 ;
>>
>> ...And then reloaded the ~800,000 records into the table (and adding
>> the latitude and longitude to the POINT column)...
>>
>>   INSERT INTO map_data (meridian, range, township, section,
>> quartersection, latitude, longitude, coordinates)
>> VALUES('4','01','001','01','E4','49.00701238','-110.00507933',
>> GeomFromText('POINT(49.00701238 -110.00507933)')  ),
>> ('4','01','001','01','N4','49.01415809','-110.01615511', GeomFromText
>> ('POINT(49.01415809 -110.01615511)')  )
>> ,('4','01','001','01','NE','49.01424023','-110.00508075',
>> GeomFromText
>> ('POINT(49.01424023 -110.00508075)')  );
>>
>> So now that the table has a column that is spatially savvy, how would
>> I write a SELECT that fetches the one record with coordinates closest
>> (in terms of distance) to a given latitude/longitude? I don't mean to
>> be lazy, but there's very little documentation on this. Any help is
>> much appreciated. Thanks!
>>
>> ...Rene
>>
>> On 2-Feb-07, at 8:50 PM, ViSolve DB Team wrote:
>>
>> > Hi,
>> >
>> > Try like this....
>> >
>> > (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
>> > longitude ASC) a
>> > WHERE a.latitude<=givenvalue AND a.longitude<=givenvalue
>> > LIMIT 0,5)
>> > UNION
>> > (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
>> > longitude ASC) b
>> > WHERE b.latitude>givenvalue AND b.longitude>givenvalue
>> > LIMIT 0,5)
>> > ;
>> >
>> > Thanks,
>> > ViSolveDB Team
>> >
>> > ----- Original Message ----- From: "M5" <m5@stripped>
>> > To: <mysql@stripped>
>> > Sent: Saturday, February 03, 2007 7:30 AM
>> > Subject: How to SELECT rows closest to value
>> >
>> >
>> >> Simply put, I have a table of ~800,000 records containing, among
>> >> other things, latitude and longitude values. Given a lat/lng pair,
>> >> I  would like to SELECT from this table the 10 rows containing
>> >> latitude/ longitude values closest to the given lat/lng pair.
>> >> Possible?
>> >>
>> >> ...Rene
>> >>
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe: http://lists.mysql.com/mysql?
>> >> unsub=mysqlsupport@stripped
>> >>
>> >>
>> >>
>> >> --
>> >> No virus found in this incoming message.
>> >> Checked by AVG Free Edition.
>> >> Version: 7.1.411 / Virus Database: 268.17.19/663 - Release Date:
>> >> 2/1/2007
>> >>
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:    http://lists.mysql.com/mysql?
>> > unsub=m5@stripped
>> >
>> >
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?
>> unsub=lars.schwarz@stripped
>>
>>
>
>
> --
> Lars Schwarz
> Gottorpstrasse 20
> 26122 Oldenburg
> T 0441 2171 354 0
> F 0441 2171 354 0
> M 0179 512 4628
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?
> unsub=m5@stripped
>
>
>

```