Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
This is a good question, since MySQL is not able to do this accurately, unless you are running v5.6.1+. QGIS needs to take over the point-in-polygon analysis instead. –  Mike T May 17 '12 at 2:13
    
I have UbuntuGIS installed which has QGIS –  Alex Block May 17 '12 at 2:17
    
Can anyone provide any help? –  Alex Block May 17 '12 at 7:12

2 Answers 2

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.