Put points on the map, longitude/latitude not where they should be although SRID the same

Question

Thank you for looking at this - I'm sure it's a simple question but this is all very new to me and after a lot of internet searching my points are still in the sea!

I have a set of UK postcode data that I have converted into latitude and longitude. I also have a UK counties shape file.

The code that I have used to convert the latitude/longitude to a geometry point column is as follows:

ALTER TABLE colic ADD COLUMN longlat geometry(POINT,27700);
update colic set longlat = st_setsrid(st_point( longitude, latitude), 27700);

And this produces a column that has values as follows (I've only included a couple!):

"0101000020346C000048BF7D1D3867E83FC05B2041F1334A40" "0101000020346C0000F085C954C1A8F7BFA1F831E6AE954A40" "0101000020346C000020D26F5F07CEF4BFE3361AC05B504A40" "0101000020346C00007D3F355EBA49F73FB6847CD0B3614A40"

The .prj from my shape file is:

PROJCS["British_National_Grid", GEOGCS["GCS_OSGB_1936", DATUM["D_OSGB_1936", SPHEROID["Airy_1830",6377563.396,299.3249646]], PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]], PROJECTION["Transverse_Mercator"], PARAMETER["False_Easting",400000.0], PARAMETER["False_Northing",-100000.0], PARAMETER["Central_Meridian",-2.0], PARAMETER["Scale_Factor",0.9996012717], PARAMETER["Latitude_Of_Origin",49.0], UNIT["Meter",1.0]]

And I set its SRID to 27700 when I upload it using the PostGIS shapefile uploader.

In PG Admin, when I ask:

select st_srid(geom) from counties limit 1;
select st_srid(longlat) from colic limit 1;

I get 27700 for both.

But...when I try any spatial query I get no response, and when I load the layers into quantum, the point data are off the tip of Cornwall in the north Atlantic, and only represented as one dot on the scale of the counties map.If I zoom in on them, they are 'there', but just not in east anglia where they should be!

This is my first attempt at both SQL and mapping - I'm sure there is something really simple that I have missed.

Answer 1

You are mixing up spatial references (SRS). The SRS in the .prj file (aka SRID=27700) is projected eastings and northings, which have units of metres. This is not latitude and longitude! Furthermore, it is a deception to call a column longlat when it isn't for longitude/latitude coordinates.

If you try to insert latitude/longitude in a column with SRID=27700, the points will not behave as expected, e.g., they will be in the far bottom left corner of a map.

If you have lat/long data from WGS84 (SRID=4326), you can transform this to eastings and northings:

UPDATE colic SET
  geom = ST_Transform(ST_SetSRID(ST_Point(longitude, latitude), 4326), 27700);