NOTE: Some UI elements in the Dataset Management Experience have been updated since 12/7
This article will show you how to reproject from a state plane projection to WGS84, enabling web mapping and easier consumption of the data. If you haven’t read the article about transforming and validating data, it will help give the background required for this article, you can read it here.
State plane projection is a class of Geospatial projection used by many states to provide high accuracy geospatial data. Unfortunately, thousands of projections exist, so web mapping tools and services generally require the projection to be re-projected into WGS84, which allows the data to be plotted on a flat map.
The re-projection process is a set of mathematical calculations applied to a geospatial datum. Reprojection is often an unpleasant process due to the complexity of the software libraries involved. Fortunately, the Socrata platform now provides a simple way to reproject Point, Line, Polygon, Multipoint, Multiline and Multipolygon data.
In this article, we will build a custom SoQL transformation expression that will reproject our CSV containing x and y coordinates in the NAD83 projection (used primarily in the pacific northwest) into WGS84.
Adding Data and Launching Transformation
Start by adding your data file to the ingress revision using the “Add Data” button. Upload or import a file from a URL.
By reprojecting data, some precision can be lost. If you want to maintain precision, we can simply add a new column which will have the reprojected value, and leave the state plane columns unmodified. Let’s go this route, so we will choose “Add Column” from the left sidebar. We can name our new column something like “WGS84 Point”. We don’t need to choose a Source Column now, simply hit save and we will see a message that our column has been added. Now let’s head back to the data table by clicking “Preview Data Table” on the left sidebar.
We’ll see our new column added to the very end of our dataset. Select the “Data Transforms” option from this column’s option dropdown.
Transforming the Data
This will bring up the data transform editor. We’ll want to make a POINT column out of our longitude and latitude columns. We can use the “make_point” function to make our point out of our source columns.
Well, that’s odd, we get a compilation error telling us we can’t pass a value of type text to make_point. Sure enough, if we double click on the “make_point” function, the documentation says “make_point” takes two numbers as arguments.
The reason for this is that all CSV data is text; CSV column data only takes on a non-text data type when we explicitly convert it to something other than text. So lets convert the x_coord and y_coord to numbers using the “to_number” function. We’ll see that we get a compilation success message, so now we can hit the “Run” button and see our data.
By clicking the green map icon at the top of the column, a map preview is shown. Remember, at this point, our data is still in a NAD83 projection, so it doesn’t render properly in a web map, but we’re making progress!
Reprojecting the Data
A consequence of this file coming from a CSV is that the platform has no way of knowing the projection. File formats like ESRI Shapefiles package the projection information along with the data, but most formats don’t do this. So we’ll need to explicitly tell the data transformer, “Hey, I know all this data is in NAD83 projection!”. We can use the “set_projection” function to do this.
We’ll need the Proj4 string from https://spatialreference.org/. If we look up NAD83 projection on https://spatialreference.org/ (If you’re not using NAD83, https://spatialreference.org/ will likely have the projection you are using, so you should be able to search for the proj4 string there) and then click on the “Proj4” link, we get a string that looks like this:
+proj=aea +lat_1=34 +lat_2=47 +lat_0=43 +lon_0=-120 +x_0=0 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs
We can copy-paste this string as the second argument of the set_projection function, with the first argument being our state-plane point.
We see that our expression compiles. Now we can reproject to WGS84 by wrapping our point in the “reproject_to_wgs84” function. When that expression compiles, try hitting run, and clicking on the map icon.
We expect our points to be in the Seattle area, and sure enough, they are! Let’s hit the “Save” button and return to our data table preview. If we scroll all the way to the right, we can see that our original state plane columns, latitude, and longitude columns have remained unchanged, while we have a new one in the WGS84 projection.
Publish Your Dataset!
Now we can save and publish our dataset, and our users will be able to create maps on the web, while GIS users will still have access to the unmodified data!
When you add more data to you dataset (either updating or replacing) through the UI or using a script generated by the “Automate This” button at the bottom of the screen, the data transformation you just created will be automatically applied to your incoming data.
It’s important to note that if you use the legacy Publisher API, data transformations will not take place. Because it is an asynchronous, transactional process, you will likely want to use the legacy API if your changes to the data are frequent, well-formed, and don’t need to be transformed.