Awesome! and Oh No!
So I finally got it working. The libmygis library in the previous post had a tool called 'mysqlgisimport', that I used to import my shapefiles with. I had to tell it not to use a PRJ file (whatever that is) since I didn't have one, and outputted the results into a temporary file:
./mysqlgisimport --no-prj tr36_d00 -o foo
Notice that the shapefiles' extensions aren't written. After that, I took foo, and inputted it into mysql to run mysql in batch mode:
mysql -upb -p < foo
('pb' is my username, so -upb significates that the user is pb, and -p makes it ask for a password.)
And that worked! All the files went into the mysql database, into a table called tr36_d00! What worked this time vs. using the shp2mysql.pl tool is that a column holding geometry values was created. To test the creation of these geometries, I tried querying:
select AsText(tr36_d00.geo) FROM tr36_d00 limit 1;
and got entries like
POLYGON((-73.519309 45.006876,-73.452584 45.008767,-73.437371 45.009198,-73.411169 45.009655,-73.3883 45.010053,-73.367388 45.010417,-73.357332 45.010592,-73.352228 45.010681,-73.343124 45.01084,-73.350188 44.994304,-73.353355 44.990259,-73.353429 44.990165,-73.354633 44.987352,-73.354112 44.984062,-73.353716 44.982959,-73.352886 44.980644,-73.350218 44.976222,-73.345974 44.971764,-73.34474 44.970468,-73.339451 44.966433,-73.338734 44.965886,-73.338243 44.96475,-73.337906 44.960541,-73.339603 44.94337,-73.338482 44.924112,-73.338939 44.918194,-73.338979 44.917681,-73.339142 44.917447,-73.339534 44.916885,-73.396083 44.911148,-73.396627 44.913411,-73.450909 44.908153,-73.486053 44.904716,-73.507148 44.911338,-73.521172 44.911536,-73.520173 44.941891,-73.519375 44.97616,-73.519309 45.006876))
Awesome! So I tried testing to see if it could correlate between GPS data and census tract. Using a geocoder, I got the lat/long of 116th st and Broadway in NYC (40.8080, -73.963956). Using these examples as help, I constructed this query and ran it:
SELECT t.TRACT
FROM tr36_d00 t
WHERE Contains(t.geo, GeomFromText('Point(-73.963956 40.808502)')) = 1;
and with my tired eyes I saw in slowly-dawning-hope-growing-turning-into-joy:
+-------+
| TRACT |
+-------+
| 0315 |
| 0205 |
| 0203 |
+-------+
YAY HOORAY AWESOME OH BOY Oh boy oh oh....
...unfortunately, as you can see, there are three TRACT answers, which doesn't make sense, as the census tracts don't overlap. Alas, the function Contains, which checks to see if an object contains another objects, is not accurate. As this page of documentation says, it instead returns the value from the MBRContains function. And the MBRContains function doesn't actually check, it checks to see if the smallest bounding box of an object contains the smallest bounding box of another's.
Damn! Because Manhattan doesn't run exactly north-south, but its census tracts do, the bounding rectangles (which presumably have vertical and horizontal lines) are probably woefully inaccurate:
Here are the minimal bounding rectangles; there's a third one you can't see because the census block is thin and tall, and so its MBR is too large. The black dot is the location of the lat/long coordinates on the map. Since the dot is within both MBRs, the function treats it as being contained, and that's why we get three census tracts back -- because the coordinates are within the MBRs of three census tracts: 315, 205, 203.
GAH! so what now? Some guy wrote a function to check for true 'contains' by getting the coordinates of the census tract, drawing a line from the desired point outwards, and counting how many times the line crosses the boundaries of the census tract? If the number of times is odd, its contained; if not, then it isn't. Described here and implemented in PHP(why?) here.
But no, that's sloppy. I don't want to do that! I think I'll end up installing PostGIS and using that instead, which unlike MySQL has excellent spatial/GIS support -- or so I hear.
All that time spent on MySQL! Wasted! AHHHH!
update:
I fixed this eventually; check the solution process here: PostgreSQL & PostGIS! , here: PostGIS continued, but..., and finally fixed it here: success.