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/[EMAIL PROTECTED]
