3

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

Improve this question
16
  • Are you sure that data is in 4326?
    – Ian Turton
    Commented Mar 5, 2016 at 13:42
  • @iant It's OpenStreet Maps and that uses WGS 84 so I thought so?
    – Mikey
    Commented 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
    Commented 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
    Commented 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
    Commented Mar 5, 2016 at 14:30

1 Answer 1

Reset to default
3

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)
Improve this answer

Your Answer

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

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