[GIS] MySql select points contained in polygon

mysql-spatial

I am extremely new to mysql, but I cannot find the error in this query

SELECT latitude, longitude from mytable where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((47 -67, 47 -123, 21 -78, 47 -67)'), point(longitude, latitude))

The polygon is a triangle that covers most of the US. All of the lat/lng of the points should be inside the above polygon wkt, yet I get no rows. I have reversed the wkt coords in case I had it backwards, but still no luck. I am hoping I have an obvious mistake.

Best Answer

You are missing a closing parentheses at the end:

SELECT latitude, longitude from mytable where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((47 -67, 47 -123, 21 -78, 47 -67)'), point(longitude, latitude)))

Related Solutions

[GIS] Data types for storing lng/lat in MySQL

This question was also asked on StackOverlow.

The top answer suggests the MySQL Spatial Extensions. There are a load of links on working with these extensions here.

If you don't want to use spatial types and you are getting values from a GPS unit, or geocoding service then you can match your decimal precision to the data source. A general rule of thumb is to store data to an accuracy of two places greater than you will be displaying it in an application.

In a code example from Google displaying points on a map, they state:

When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal.

To keep the storage space required for our table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees

I wouldn't worry about performance differences between numeric types. Decent indices will have a far greater effect.

[GIS] MySQL Polygon (Spatial) Insert Statement works sporadically

By default, MySQL has a maximum packet size of 1 MB. This can easily cause INSERT statements with complex polygons to fail. You can check your maximum size in bytes using

SHOW VARIABLES LIKE 'max_allowed_packet'

The MySQL docs explain a few different ways to adjust this parameter.

Best Answer

You are missing a closing parentheses at the end:

Related Solutions

[GIS] Data types for storing lng/lat in MySQL

[GIS] MySQL Polygon (Spatial) Insert Statement works sporadically

Related Question