How to upload GeoJSON to CartoDB in a CRS that is not lat/lon?

Question

I'm pretty sure I need to convert the coordinates in my GeoJson file to EPSG:3857 for them to play nicely with CartoDB, but I don't know what coordinate system they are currently in. The original data is from Ordnance Survey. Here's how I got that data and how I transformed it:

• I downloaded the UK OS shapefile for "lower layer super output area" boundaries.
• I used the mapshaper command line tool to heavily simplify the polygons and export the result as GeoJson.
• (I then used a Ruby script to discard all the features that were not in Greater Manchester, but I hope that's not relevant.)
• Finally, I imported the result as a CartoDB dataset but saw no polygons on the map view.

Here is an example feature from my GeoJson file:

{
"type": "Feature",
"properties": {
    "LSOA11CD": "E01004766",
    "LSOA11NM": "Bolton 005A",
    "LSOA11NMW": "Bolton 005A"
},
"geometry": {
    "type": "Polygon",
    "coordinates": [
        [
            [
                370664.009,
                411365.069
            ],
            [
                370452.531,
                411428.156
            ],
            [
                370344.061,
                411733.869
            ],
            [
                370510.978,
                411885.617
            ],
            [
                370774.826,
                411942.479
            ],
            [
                370795.626,
                411526.363
            ],
            [
                371172.854,
                411677.763
            ],
            [
                371456.953,
                411961.328
            ],
            [
                371525.9,
                411866.915
            ],
            [
                371452.615,
                411272.858
            ],
            [
                371336.156,
                411156.394
            ],
            [
                371161.151,
                411170.972
            ],
            [
                371052,
                411185.906
            ],
            [
                370664.009,
                411365.069
            ]
        ]
    ]
  }
}

If I can find out what the current coordinate system is then I am confident that I can convert using QGIS.

Answer 1

There is another option than converting in QGIS, which is to add the CRS property to the GeoJSON before uploading to CartoDB, see the GeoJSON spec. This means you need to convert you GeoJSON to a feature collection, which may be of use anyway, if you wish to upload more than one geometry. So, at the start of your GeoJSON, you put the CRS of EPSG:27700 (the UK projection), and CartoDB will read this and convert under the hood to 4326 (lat/lon). To be clear, while the tiles are in Web Mercator (3857), uploaded vector data is expected to be in 4326, and this will be assumed, unless otherwise specified. A hidden column, the_geom_webmercator is also created, but this will be wrong, and your data will not appear in the correct place, unless the uploaded vector data is in 4326 or the CRS is specified, as in the example below.

You can check that this is the case by running

SELECT ST_Srid(the_geom) FROM bolton

in the SQL panel of CartoDB on your original GeoJSON without the CRS set, and it will return 4326. CartoDB allowing values such as (370664.009, 411365.069) in a column that is implicitly 4326 without prompting the user for a CRS, could be considered a bug.

Obviously, you can go the QGIS route also, but this approach is probably quicker. Save the following as bolton.json, and CartoDB will happily ingest it.

{
"type": "FeatureCollection",
"crs": {
    "type": "EPSG",
    "properties": {
        "code": 27700
    }
},
"features": [
    {
        "type": "Feature",
        "properties": {
            "LSOA11CD": "E01004766",
            "LSOA11NM": "Bolton 005A",
            "LSOA11NMW": "Bolton 005A"
        },
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [
                        370664.009,
                        411365.069
                    ],
                    [
                        370452.531,
                        411428.156
                    ],
                    [
                        370344.061,
                        411733.869
                    ],
                    [
                        370510.978,
                        411885.617
                    ],
                    [
                        370774.826,
                        411942.479
                    ],
                    [
                        370795.626,
                        411526.363
                    ],
                    [
                        371172.854,
                        411677.763
                    ],
                    [
                        371456.953,
                        411961.328
                    ],
                    [
                        371525.9,
                        411866.915
                    ],
                    [
                        371452.615,
                        411272.858
                    ],
                    [
                        371336.156,
                        411156.394
                    ],
                    [
                        371161.151,
                        411170.972
                    ],
                    [
                        371052,
                        411185.906
                    ],
                    [
                        370664.009,
                        411365.069
                    ]
                ]
            ]
        }
    }
  ]
}