sql query for points in polygon/rectangle

问题:

Ok, so I have a very large database of property information, complete with geographic coordinates. What I need to be able to do is run a query that asks, "give me all properties that are inside a given rectangle or polygon." The user interface would allow the user to define the points of the rectangle or polygon (using a map system). These coordinates would go into the query and sql should return a list of properties in that area. If polygon isn't possible, at least a rectangle would be helpful. I already know how to do the radius search.

Appreciate any help you can provide.

回答1:

MySQL support geometrical function and queries. Reference document would be helpful for you : https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions.html

回答2:

Your sql code must be something like this
SQL Query

SELECT 
    * 
FROM areas area
WHERE 
    ST_CONTAINS(`area`.`polygon`, GEOMFROMTEXT('POINT(34.262714 56.262714)')) = 1

Table

CREATE TABLE IF NOT EXISTS `areas` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `polygon` polygon NOT NULL,
  `area_name` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `polygon` (`polygon`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Also be careful, for large table you must use MyISAM, because InnoDB not support SPATIAL indexes.
Note MySQL support ST_CONTAINS from 5.6+ version.

For more take a look at this, useful article http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

回答3:

Use Within(geom1, geom2) function to check if geom1 is spatially within geom2. Your query will look something like,

select some_thing from your_table where Within( some_point,
        GeomFromText('POLYGON( some_polygon )'));