[postgis-users] Problem with mismatched coordinate systems

[Russell Horn]

Hi,

I am very new to PostGIS, please bear with me. I've had a lot of help
from #postgis on IRC but I continue to have trouble and hope someone
here can assist.

I have two data sets from the UK Ordnance Survey which when imported
to PostGIS and plotted are not matching up:

http://exiled.albanach.com/chester.png

One data set contains shape files (no .prj file) which give me an
outline of part of the UK.

The data set is available here:

http://www.ordnancesurvey.co.uk/products/boundaryline/sampledata/Layered_Shape.zip

The documentation states that the coordinate system is National Grid
(NG). I understand this is OSGB1936 / EPSG:27700

The other data set contains a list of place names, together with
Eastings and Northings like this:

CHESTER*CHESHIRE COUNTY*340363*366880

I have then converted that to Long Lat to give me

53.196,-2.894,CHESTER

I can paste my Long Lat values into Google maps and get the right
place, so that conversion seems to have worked.

To import my shape files I did this:

shp2pgsql -s 27700 county_region.shp public.ch_county > /tmp/county.sql

To import my place names, I did this:

ogr2ogr -f "ESRI Shapefile"  gaz/ chester.csv

Where chester.csv looks like this

lat,long,value
53.196,-2.894,CHESTER

I then did

ogr2ogr -f "ESRI Shapefile"  gaz/ chester.csv
ogr2ogr -f "ESRI Shapefile" gaz/ os.vrt

To make my shapefiles where os.vrt looks like this:

<OGRVRTDataSource>
  <OGRVRTLayer name="OS_Gazetteer">
    <SrcDataSource relativeToVRT="1">gaz</SrcDataSource>
    <SrcLayer>chester</SrcLayer>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:27700</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="long" y="lat"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Changing LayerSRS value didn't seem to change anything.

I converted to sql like this:

shp2pgsql OS_Gazetteer.shp public.chesgaz > gaz.sql

I then imported my two SQL files into Postgres.

When  plot my data using qgis I end up with Chester being plotted way
off my map like the image I pointed to earlier:

http://exiled.albanach.com/chester.png

I know I'm doing something wrong here, but I'm at a bit of a loss as
to what it is, and where to go from here.

Any help would be very much appreciated.

Thanks,

Russell