[GIS] Batch conversion of XY coordinates to latitude and longitude

coordinate systemcoordinatesxy

I'm working on a school project. I have this dataset from Sacramento, California

I want to convert the XY coordinates to latitude and longitude, in order to import it into tableau and use its mapping features.

I also have the address, if that makes it easier.

I have about 10,000 records, so I need a powerful tool to perform this conversion.

Best Answer

[EDIT] The original source data is California State Plane Zone 2, i.e. EPSG:2226.


The following approach using ogr2ogr will perform a CSV to Shapefile conversion, including the coordinate transformation to Lat/Long (i.e. WGS84) that you're desiring. Basically I'm just adapting the approach demonstrated in this post.

For GDAL/OGR < 2.1 you'll need to create a simple VRT tile to model the CSV content for OGR. (You can do this in notepad, just make sure to save the file extension as .vrt) This is the exact contents of the dispatch.vrt file I created for this exercise.

<OGRVRTDataSource>
    <OGRVRTLayer name="93305-sacramento-dispatch-data-from-one-year-ago">
        <SrcDataSource>C:/xGIS/Other/dispatch/93305-sacramento-dispatch-data-from-one-year-ago.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding="PointFromColumns" x="X Coordinate" y="Y Coordinate"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

A couple notes..

  • I reused the name of the downloaded .csv file, without the .csv extension, as the layer name value.

  • I looked in the CSV for the X Coordinate and Y Coordinate field names, specifying those values, as OGR needs to know which fields represent X and Y to resolve the geometry.

Next, I used the following ogr2ogr instruction to perform the conversion:

ogr2ogr -f "ESRI Shapefile" "C:/xGIS/Other/dispatch/sac-dispatch-4326.shp" "C:/xGIS/Other/dispatch/dispatch.vrt" -s_srs EPSG:32610 -t_srs EPSG:4326"

The -f "ESRI Shapefile" means I'm exporting to a shapefile, and the next value appearing in double-quotes is the shapefile I want to output.

The next file, the input, is the .vrt we create in the first step.

Finally, -s_srs means, "the coordinate system of the source data" and the -s_srs means, "the coordinate system for the output data".

Ultimately the most difficult part of this exercise was identifying the correct projection of the source data. I used a process of elimination stemming from educated guesses. First I applied UTM10 as @Masjo suggested. But after plotting the resulting .shp it was obviously in a different source. The next best bet, I thought, was to go for the most appropriate CA State Plane, in this case Zone 2. And as you can see in the image here, the points appear about where I would expect to find Sacramento:

enter image description here

For the fact-hunters out there, the source csv I downloaded from the OP's site had 329543 records in it, and ogr2ogr performed the conversion in just a few moments ..definitely less than a complete minute.

Related Question