2

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.

3

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
                    ]
                ]
            ]
        }
    }
  ]
}
  • Thank you! My data was already a feature collection so adding the crs data you specified was easy and solved the problem in 2 minutes. I had actually tried specifying the coordinates as OSGB1936 EPSG:4277 in QGIS which, thanks to you, I now know is wrong. I didn't realise there were multiple OSGB 1936 coordinate systems. – crantok Apr 17 '15 at 8:56
  • You are welcome. CartoDB docs don't make it totally clear that you can do this nor that data should be in 4326 not 3857. Coordinate systems are the source of a lot of confusion. – John Powell Apr 17 '15 at 9:08

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.