Are there python libraries with spatial support for mySQL?

Question

Is it possible to use sqlsoup or some other python MySQL Library with any of the spatial types like Geometry, Point, Polygon, etc of MySql? If so, could someone show how one would use said python library to perform a SELECT and extract points from a polygon?

Answer 1

I don't know of a single package that both queries MySQL and handles spatial data, but MySQL supports the well-known-text and well-known-binary spatial data formats (which will allow you to store points, lines, polygons, etc).

You would need to make a query to your MySQL using MySQLdb, fetching the data with an SQL statement (examples here). If all you want is the points that make up a polygon, you can do some simple string manipulation to construct an array of points:

input = "POLYGON ((30 10, 10 20, 20 40, 40 40, 30 10))"

def polygon_to_points(polygon):
  geometry = polygon[10:-2]
  coordinates = geometry.split(', ')
  coord_pairs = []
  for c in coordinates:
    pair = c.split(' ')
    pair_num = [float(pair[0]), float(pair[1])]
    coord_pairs.append(pair_num)
  return coord_pairs

This returns [[30.0,10.0],[10.0,20.0],[20.0,40.0],[40.0,40.0],[30.0,10.0]] as intended. If you want to do some more complex spatial querying/analysis, take a look at the Shapely package.