2

This question already has an answer here:

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 shapefile.

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 shapefile 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 QGIS, 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.

enter image description here

If I zoom in on them, they are 'there', but just not in East Anglia where they should be!

enter image description here

This is my first attempt at both SQL and mapping - I'm sure there is something really simple that I have missed but I would be so grateful if someone could help me.

marked as duplicate by Ian Turton Sep 6 '13 at 19:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • your, UK Counties Map look like it is in British National Grid still. Look at the bottom of this blog post for visual changes qgis.spatialthoughts.com/2012/04/… – Mapperz Sep 6 '13 at 15:46
  • Either reproject your point to British National Grid or reproject your counties data to lat/lng geographic WGS84. – Mapperz Sep 6 '13 at 15:51
  • In other words, you want ST_Transform(), not ST_SetSRID. – Micha Sep 6 '13 at 19:37