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" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> 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?
>> [EMAIL PROTECTED]
>>
>>
>>
>> --
>> 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?
> [EMAIL PROTECTED]
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
