3

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?

Improve this question
4
  • Those coordinates look correct: longitudes should precede latitudes (to retain the positive orientation of the coordinate system: horizontal displacements must precede vertical displacements) and values in the SE US should have longitudes around -86 and latitudes around +30. In what sense, then, is anything "reversed"?
    – whuber
    Dec 28, 2010 at 18:44
  • When selecting the geometry using the MySQL astext() function, they do seem reversed - at least from perspective that I loaded the TIGER/line format previous to them being in n ESRI shape file (i.e., using method described here gtowey.blogspot.com/2009/05/…) and the latitude and longitudes here look correct - the latitude precedes the longitude when selected using the astext() function in MySQL. Also, X is the latitude and longitude (when loaded using the prior format) but when I use ogr2ogr, they are reversed (i.e., I get longitudes as X values).
    – Dave LeJeune
    Dec 28, 2010 at 22:01
  • In the example you reference, the latitude and longitude are explicitly stored in reverse orientation (i.e., lat-lon instead of lon-lat). Perhaps you are storing them the other way? (See the INSERT INTO statement.)
    – whuber
    Dec 28, 2010 at 23:07
  • 1
    @whuber - yes you are right. The example prior did restore them in reverse order and that actually is something I learned from this posting :) Once I changed the MBR query to accommodate the correct order, my new data started working perfectly!
    – Dave LeJeune
    Dec 30, 2010 at 2:39

1 Answer 1

Reset to default
3

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

Improve this answer
4
  • I'd actually rather them be stored as a geometry and not as separate latitude/longitude columns. See my comments above, but I would think when I select out data from the geometry that the latitude would be on X axis and longitude on Y axis, but when loading using ogr2ogr these are reversed.
    – Dave LeJeune
    Dec 28, 2010 at 22:05
  • latitude is y and longitude is x. Long goes around the earth east west. latitude (higher) goes north south or in the y direction. :)
    – Brad Nesom
    Dec 28, 2010 at 23:11
  • @brad - many thanks - this is something I learned from this posting. The example I had found for a prior version (2002) of TIGER/line data was before they adopted the ESRI Shapefile format and as such they loaded it in the 'incorrect' order. Their MBR query was based on this and when I ran the same query against data loaded correctly, it of course did not work! Thanks again to everyone for helping. Makes total sense to me now and I'm downloading the complete set of edge data from the census website for 2010 to load the 'correct' way into MySQL!
    – Dave LeJeune
    Dec 30, 2010 at 2:41
  • I almost asked that about the title of your question. I'm glad you are going well now.
    – Brad Nesom
    Dec 30, 2010 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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