I'm out of my element here so please forgive me if I dont ask this correctly/clearly.
I have a table of users with lat/long for each one.
I get a query from a federal agency that provides either a polygon or a circle like this:
<polygon>38.47,-120.14 38.34,-119.95 38.52,-119.74 38.62,-119.89 38.47,-120.14</polygon>
<circle>32.9525,-115.5527 0</circle>
Given either of these two inputs, I need to return all users within the polygon or circle.
I've looked all over, read all the mysql spatial docs etc. and I am either missing a step or just not grokking the query method.
ANY help would be appreciated!
Have you read http://howto-use-mysql-spatial-ext.blogspot.com/ ?
For a circle, you would need to compute the distance from the circle center to each of the lat/lon's in your persons list. If the distance is less than the radius of the circle, that person is inside the circle. Typical formula for computing distances between 2 lat/lon's along the 'great circle' is called the haversine formula. The website below shows this formula in javascript.
http://www.movable-type.co.uk/scripts/latlong.html
For a general polygon, this is much harder. For general point in a polygon, the Point Inclusion Test seems to work assuming a trivial Equirectanglular projection (x = lon, y = lat).
This is the answer that I came up with and appears to be working:
First, I altered the table by adding a field to hold the spatial points created from the users lat/lon:
ALTER TABLE users ADD location POINT NOT NULL;
UPDATE users set location = PointFromText(CONCAT('POINT(',lat,' ',lon,')'))
CREATE SPATIAL INDEX location ON users(location);
From there, I'm using this to query to find the users within the polygon:
SET @bbox = 'POLYGON((38.47,-120.14 38.34,-119.95 38.52,-119.74 38.62,-119.89 38.47,-120.14))';
SELECT * , AsText( location )
FROM users
WHERE Intersects( location, GeomFromText( @bbox ) ) ;
For new users I created a trigger to update the location from the lat/lon (user can't be added without a lat\lon):
DROP TRIGGER IF EXISTS `users_insert_defaults`;
DELIMITER //
CREATE TRIGGER `users_insert_defaults` BEFORE INSERT ON `users`
FOR EACH ROW SET NEW.location = PointFromText(CONCAT('POINT(',NEW.lat,' ',NEW.lon,')'))
//
DELIMITER ;