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

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

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.