2

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.

  • 1
    what software are you using/do you have access to? – crmackey Nov 9 '15 at 15:11
  • 1
    do you have any idea which coordinate reference system they are in currently? – Ian Turton Nov 9 '15 at 15:15
  • The raw data is in excel. Originally there were over 330,000 records. I have only selected the record type I'm interested in "vehicle accidents". – Jacob Lindberg Sørensen Nov 9 '15 at 15:15
  • This is how it looks in my datafile X Coordinate 6727325 Y Coordinate 1979403 – Jacob Lindberg Sørensen Nov 9 '15 at 15:16
  • 1
    Seems to be California State Plane, but I could be wrong – ed.hank Nov 9 '15 at 17:00
7

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

  • 3
    You can also simply import the delimited text layer into QGIS. Right-click on it and choose save as Choose WGS84 as the CRS. Performs you task with a GUI using ogr2ogr in the background. – HeikkiVesanto Nov 9 '15 at 17:04
  • 1
    @Vesanto, true. But the title of the post suggests a batch conversion is preferred, which to me implies a solution that can be executed over the command line. However, +1 as others may indeed prefer to use the GUI. – elrobis Nov 9 '15 at 17:06
  • I agree, hence not a new answer. It depends what OP means by batch as well. QGIS will do all the points at once, so technically batch. But the method is the same, QGIS just provides a GUI on top of ogr2ogr. – HeikkiVesanto Nov 9 '15 at 17:12
  • I should probably clarify. When I say batch I just mean mass conversion. I have no experience with Geo data, I just need coordinates converted, so I can use them with weather data. It's more complicated that I initially thought, but I think it is fun, so I will give it a go tomorrow. I'm located in Denmark, so its bedtime for me now. I really appreciate the help that I got on this post – Jacob Lindberg Sørensen Nov 9 '15 at 22:17
1

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

  • A javascript solution for a batch conversion? :) – elrobis Nov 9 '15 at 17:04
  • js/php yeah, and I am assuming he just wants an output of a csv with new coordinates. Obviously this wont write a shapefile or anything – ed.hank Nov 9 '15 at 17:06
  • Fair enough. +1 as your approach is certainly "useful" in the true spirit of the upvote button. – elrobis Nov 9 '15 at 17:08
  • Appreciate it =) and +1 for you too hah! – ed.hank Nov 9 '15 at 17:10
0

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.

0

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.

  • ..Sacramento, CA, USA is definitely not UTM 10S. The "S" = south of the equator. But 10N seems like a reasonable guess. – elrobis Nov 9 '15 at 16:06
  • Im pretty positive this is California State Plane, i believe zone 2 but i could be mistaken on that. – ed.hank Nov 9 '15 at 17:11
  • when I put in 2N and NAD-83 as Datum I get coordinates in the pacific ocean just north of Ecuador. imgur.com/4suYAAd I have no experience with Geo data, so chances are that I have overlooked a simple setting. The file seems very useful! – Jacob Lindberg Sørensen Nov 9 '15 at 22:24
  • Your coordinates are not in UTM, they are in State Plane CRS, which is different. This method only goes from UTM to lat/lon. You need to go from California State Plane Zone 2 to lat/lon. – ed.hank Nov 10 '15 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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