Batch conversion of XY coordinates to latitude and longitude

Question

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.

Answer 1

[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:

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.

Answer 2

You want to use Proj4js, this tool can batch several state plane coordinates and convert them to California State Plane. You just need the correct EPSG codes and a little bit of scripting knowledge. I wrote a similar script here http://gislab.net/transform/ that will take a batch of Texas State Plane and convert to Lat/Lon. I can give you the source if you wish but the meat of the program is this:

points = new Array();
noaa_points = new Array();
var p;
var thispoint;

Proj4js.defs["'.$zone_id.'"] = "'.$zone_vars.'";

var dest = new Proj4js.Proj("EPSG:4326");  /* Lat and Lon projection */
var source = new Proj4js.Proj("'.$zone_id.'");

';

##############################

if ($_REQUEST['n'] && $_REQUEST['e']) {

    echo '

 p = new Proj4js.Point('.$_REQUEST['e'].','.$_REQUEST['n'].');
 Proj4js.transform(source,dest,p);
  points.push({num:1,north:"'.$_REQUEST['n'].'",east:"'.$_REQUEST['e'].'",zone:"'.$zone_name.'",elev:0,desc:"Single Converted Point",lat:p.y,lng:p.x,noaalat:0,noaalng:0});

Obviously your source ID parameter will be different, but this is the easiest way to batch alot of coordinates to convert.

http://proj4js.org/

alternatively:

http://www.earthpoint.us/BatchConvert.aspx - i think this costs money for large batches

http://www.agc.army.mil/Missions/Corpscon.aspx

Answer 3

Possibly Web Mercator (EPSG:3857)?

You want a library that will transform from EPSG:3857(x/y) to EPSG:4326 (lat/long). I'm working on a web-app that uses ol3 (OpenLayers3), but you might want to consider other geo tools.

Maybe gdal?

Maybe QGIS? It's fairly complicated with a heavy user interface, but valuable if you're doing projection / mapping work.

Please be sure to verify the projection of the data is indeed in Web Mercator.

Answer 4

I never had to do quite so many calculations, but if you could split them up into smaller groups (~1,000 or so, which is what I did) I have used this spreadsheet in the past (posted from University of Wisconsin):

archived version: www.uwgb.edu/dutchs/usefuldata/utmconversions1.xls

You just type/copy in your lat/long and paste into the spreadsheet on the correct page (batch convert UTM to Lat/Long) and the result is created for you on the other side. You also have to know what longitude zone you are in; a quick Google search tells me you are in 10N, so make sure you put that in front of all of your coordinates. Edit: Corrected as elrobis is definitely right about it being N, not S. Not 100% sure if it's in 10, but looking at the UTM zone map it seems that it could be.