I'm attempting to build a MySQL database that will allow me to reverse geo-code addresses using the MBR functions within MySQL. I've downloaded the latest set (2010) of TIGER edge shapefiles from the US Census website and am trying to load them into MySQL using the GDAL ogr2ogr utility.
A new table (geotest) does get created with a SHAPE column that has the geometry defined as a LINESTRING. However, I am seeing reversed latitude and longitude values that get reversed when running the following command:
ogr2ogr -f "MySQL" MySQL:"geodb,user=myuser,host=localhost,password=mypwd" -nln geotest -nlt LINESTRING -append -a_srs "EPSG:4326" -lco engine=MYISAM tl_2010_01021_edges.shp
What I get when I look at the data are latitudes and longitudes (although correct when verified on Google Maps) are in fact reversed as seen below:
LINESTRING(-86.69863 32.973164,-86.69853 32.97302,-86.69856 32.97287,-86.698613 32.972825,-86.6988 32.972825,-86.6989 32.972892,-86.6989 32.973002,-86.69874 32.97316,-86.69864 32.97318,-86.69863 32.973164)
I've tried this for other shapefiles as well (to include those w/ polygons/etc.) and in all cases the latitude and longitudes appear to be reversed when loaded into the geometry column. I'm hoping it is something as simple as a flag I am missing w/ ogr2ogr that will load them in the proper order.
For what I am doing (building a complete database of roads for the purpose of reverse geo-coding in the US) am I even using the correct set of census shapefiles (i.e., edges) and if so, what am I doing wrong when loading? If there is not a way to correct with ogr2ogr, is there an easy update I could run after loaded to reverse all of the coordinates in the LINESTRING geometry column?
Best Answer
Isn't this as simple as your database storing coordinate pairs in the format of x y and not lat lon (which would be y x).
If the db used the format of lat lon, how would it handle other coordinate systems?
If you look at the MySQL docs, it appears that the point class has an x property and a y property. http://dev.mysql.com/doc/refman/5.1/en/gis-class-point.html