I have a table which is an import of a shp file from www.geofabrik.de/ England > Roads.

I've tried to change the CRS to EPSG 27700 but when I add this back to QGIS along side other layers which are EPSG 27700 (OSGB 1936 / British National Grid) it isn't correct / near these.

I've been using, which runs, but doesn't correct the issue.

UPDATE public.roads_eng SET geom = ST_Transform(ST_SetSRID(geom,4326), 27700);

ALTER TABLE public.roads_eng ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 27700);

Road_Screenshot Table_code

  • Are you sure that data is in 4326?
    – Ian Turton
    Mar 5, 2016 at 13:42
  • @iant It's OpenStreet Maps and that uses WGS 84 so I thought so?
    – Mikey
    Mar 5, 2016 at 14:09
  • The roads.prj file I just got from there seems to be epsg:4326, so that seems right. What's the bounding box of your PG roads layer? Note OSM also uses 3857 (web mercator) but I've not got that.
    – Spacedman
    Mar 5, 2016 at 14:11
  • @Spacedman the other layers are from the ONS open geography portal so just boundaries, all of which are OSGB 1936 (27700). So on transformation the roads should sit nicely in there.
    – Mikey
    Mar 5, 2016 at 14:19
  • Hmmm. I'm a bit unclear about your line: "I've been using, which runs, but doesn't create the issue". What does create the issue?
    – Spacedman
    Mar 5, 2016 at 14:30

1 Answer 1


Solved, thanks.

Needed to being ST_Transform instead of ST_SetSRID (see last comment / link)

ALTER TABLE public.roads_sco ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 27700) USING ST_Transform(ST_SetSRID(geom,4326),27700)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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