1

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!

Pete
  • 31
  • 3
  • 2
    Is that `0` in the `` the radius? If so, then you'll have zero people inside that circle, unless they're ultra-anorexic. – Marc B Jul 12 '11 at 19:20
  • For Circles, I hope it helps... http://stackoverflow.com/questions/11586708/get-results-that-fall-within-marker-radiuses-from-database/ – jsist Aug 03 '12 at 18:34

3 Answers3

0

Have you read http://howto-use-mysql-spatial-ext.blogspot.com/ ?

Brian
  • 108
  • 8
0

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).

TreyA
  • 3,339
  • 2
  • 20
  • 26
  • Thanks Trey.. I'm already using the haversine to pull all users with 'x' miles of a starting lat/long... it's really a problem of dealing with the polygon/circle as spatial shapes. – Pete Jul 14 '11 at 11:53
0

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 ;
Pete
  • 31
  • 3