CODEWITHSUNDEEP
mysqlsqlcoordinatespolygonspatial
Michael
Michael

Reputation: 11

How can I determine a coordinate is within a mutilpolygon or not?

We have a database in CSV format with all the counties (in US) and their boundaries defined by SRID and MULTIPOLYGON. How can I determine for any given coordinate (decimal latitude and longitude), which county it falls into. We plan to use MySQL to store the data and do the query.

Upvotes: 1

Views: 1240

Answers (3)

Spacedman
Spacedman

Reputation: 94317

Try the geo extensions for mysql

http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

Although Postgres with PostGIS is a more mature geo database and I'd use it if you have the choice.

Upvotes: 2

Jeff Dege
Jeff Dege

Reputation: 11720

If you're shapes are numerous and complex, you'd be best served by using a spatial database. PostGIS is free and runs on most platforms that MySQL will. Spatial databases have spatial datatypes for storing polygons, spatial queries that include querying the database as to whether a point is in a polygon, and spatial indexes that make everything reasonably efficient.

OTOH, if you are dealing only with a fairly small number of fairly simple polygons, you can manage with an ordinary database. I've done this once. My approach:

Have a polygon table. Each polygon record contains primary key, whatever information you need to store with each polygon, and the bounding box for the polygon - max and min x and y values.

The points for the polygon are stored in a points table, that is child of the polygon. Each point record contains the primary key of the polygon it is a member of, a sequence number, and an x and a y value.

To find the polygons that could contain a point, you query the polygon table looking for polygons that might contain the point - those for which the point is within their bounding boxes. These candidate polygons are read into memory, and each is checked to see if it contains the point.

There are a number of algorithms. http://en.wikipedia.org/wiki/Point_in_polygon

I used the ray-casting method, because it is simple. Draw a line from the point to infinity in any direction, and count the number of line-segments you intercept. If you cross an odd number, your point was inside the polygon.

Upvotes: 3

Randy
Randy

Reputation: 16673

maybe this reference will help.

Upvotes: -1

Related Questions