I have written before about geospatial data types and the open source libraries which can be used to manipulate them.
Today, I needed to take some geospatial data which was in a database with northing and easting fields (basically ordinance survey gridpoints), and convert it to a WGS-84 coordinate system (latitude, longitude used by most GPS systems these days). This is so that I could spatially match it to another data-set.
The conversion took a few steps and had a couple of ‘gotchas’, so I’ve documented it here for prosperity. Although I’m converting from SRID 27700 to 4326 here, you can of course use the same steps to convert between other types.
The steps below assume you have already installed the GDAL suite.
<OGRVRTDataSource> <OGRVRTLayer name="EastingNorthing"> <SrcDataSource>C:\Example\Location\SourceData.csv</SrcDataSource> <GeometryType>wkbPoint</GeometryType> <LayerSRS>EPSG:27700</LayerSRS> <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/> </OGRVRTLayer> </OGRVRTDataSource>
ogrinfo -ro -al "C:\Example\Location\SourceData.vrt"
ogr2ogr -s_srs EPSG:27700 -t_srs EPSG:4326 -f CSV -lco "GEOMETRY=AS_WKT" "C:\Example\Location\Output\output.csv" "C:\Example\Location\SourceData.vrt"
(s_srs and t_srs set the source and target geography projection; lco instructs GDAL to include a field in the output with the new coordinates; and finally, f sets the target filetype to csv.)
Check your output field. You should see a new field containing data like 'POINT (0.000428633570006 52.700838815264168)'. This is your shiny new WGS-84 output, in the 'well known text' format.
Select "ID", geography::STPointFromText(replace("WKT",'"',''),4326) AS "Coordinate" into clean.LoadedData from raw.LoadedData
select a.ID AS "A_ID", b.ID AS "B_ID", a.Coordinate.STDistance(b.Coordinate) as "Distance" into working.CloseNodes from clean.LoadedData a inner join clean.ReferenceData b on a.Coordinate.STDistance(b.Coordinate) < 50