Map Coordinates to Neighborhoods from MySQL Table

Question

I have a MySQL database with a set of coordinates and a shape file (Zillow Neighborhood Database for Chicago) containing the boundaries for each neighborhood which I have converted to a spatial database.

How do I map each set of coordinates to the neighborhood that it is in? UbuntuGIS is installed.

Answer 1

If you're willing to give spatialite a try (sorry for drifting away from your mysql tag) then you can do as follows. When you say "map each set of coordinates to the neighborhood" I'm assuming you mean to add a column to the coordinate's attrib table with the name of the neighborhood it's located in.

Start with a new, empty spatialite db:

spatialite chicago.sqlite

Import the shapefiles. The 'srid' entry is the EPSG code (CRS) for each layer

spatialite> .loadshp "coordinates" coords LATIN1 <SRID>;
spatialite> .loadshp "neighborhoods" nhoods LATIN1 <SRID>;

Add a column to the coords table to accept the neighborhood names. (I guess the neighborhoods layer already has a 'name' column). Also create a spatial index for each

spatialite> ALTER TABLE coords ADD COLUMN nhood_name text;
spatialite> SELECT CreateSpatialIndex('coords','Geometry'); 
spatialite> SELECT CreateSpatialIndex('nhoods','Geometry');

Now run an UPDATE query to get the neighborhood names into the coords table

spatialite> UPDATE coords SET nhood_name=(SELECT n.neighborhood_name 
FROM nhoods AS n 
WHERE ST_Within(pts.Geometry, n.Geometry) AND 
coords.ROWID IN 
   (SELECT ROWID FROM SpatialIndex 
   WHERE f_table_name='coords' AND search_frame=n.Geometry));

That should update the nhood_name column in "coords" such that it contains the neighborhood name in which each point is located. HTH, Micha

Answer 2

For the sake of everyone, here was how I was able to do this.

I converted my shape file from the Zillow Neighborhood Database into a spatial table using Shp2MySQL. Then I used the following query SELECT * FROM ZillowNeighborhoodsIL WHERE contains(feature,point(-87.59193621, 41.767749711))=1 which worked well. This was all done with MySQL 1.6.1.