Updating SRID (Update_GeometrySRID) does not alter polygons (when it should)

Question

I am trying to change the SRID of polygons layer, but I'm having trouble visualizing the result.

I the SRID is misreported the shapefile I used to import into PostGIS. The metadata says the reference system is SIRGAS2000(srid 4674). However looking how the data is "drifted" (see image) I suspect the true SRID could be 4618 (SAD69)

So I tried updating the SRID (without changing the underlying points, etc) in Postgis:

CREATE TABLE setor_censitario_2 as select * from setor_censitario

SELECT UpdateGeometrySRID('setor_censitario_2','geom',4618)

and compared the results of the SRID update polygons to to the original polygons in Qgis.

Nothing changed, both layers seem identical.

This puzzled me. Shouldn't they have changed? The parameters of the two "data" (SIRGAS2000 and SAD69) are indeed different in a non-negligible way. So, just to make sure the change of SRID was not supposed to be innocuous, I ST_Tranformed the geom:

create table setor_censitario_4 as 
  select geom, ST_Transform(geom,4291) as geom4291Transf, ST_Transform(geom,4618) as geom4618Tranf
    from setor_censitario

now the new geom is different from the original one. ST_Transform, of course, is not the correction I want (in fact I doubled the magnitude of the problem).

So why is Update_GeometrySRID not changing the location of the polygons in Qgis?

The shapefile is available from: ftp://geoftp.ibge.gov.br/malhas_digitais/censo_2010/setores_censitarios/ba.zip after unziping I imported with:

shp2pgsql -c -s -I -W LATIN1 file public schema.table ...

UPDATE:

• following the UpdateGeometrySRID help I also tryed:

   CREATE TABLE setor_censitario_3 as select * from setor_censitario
   ALTER TABLE setor_censitario_3 
     ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326) 
        USING ST_SetSRID(geom,4326);
  

results are the same (polygons in Qgis do not move)

UPTADE 2:

I also tried updating the SRID directly in PostGIS, but that did not change the position of the polygons.

To reproduce the situation directly in Ggis, download this file: ftp://geoftp.ibge.gov.br/malhas_digitais/censo_2010/setores_censitarios/ba.zip unzip and open 29SEE250GC_SIR.shp in Qgis.

Then add an Open Street Maps Streets layer to the background (I haven't done it but I think it can be done, either by Vector>Open Street Map > Download Data of with the Open Layers Plugin). Or a satellite image from google (also Open Layers).

Zooming to around this location: lon -38.47761, lat -13.01077, Scale 1:8000 you should see something similar to this image:

• Green: polygons I want to correct. The perimeters of those should be defined by some of streets (polygons can include more than one city block though).
• Red: underlying OSM streets (which I assume are correct)

notice how the green layer is dislocated "northeast" from the red one.

UPDATE 3: changing the SRID to other projections does alter the polygons position. For example, changing the SRIDs from 4674 (Sirgas 2000) to 4225 (Córrego Alegre, also an older Brazillian projection) shifts the polygons south some 50m. It is only in the particular case of 4674 to 4618 that they don´t move.

Answer 1

A better way to explore changing projections is just to work in QGIS.

Load both your datasets, and ensure your 'correct' one has the right coordinate system defined. Do this by double clicking on the layer, going to the general tab of the properties, and ensuring the coordinate reference system is set correctly.

Now ensure that you have enabled 'on the fly' reprojection. You can do this down the bottom right of the main view, or in the project properties. Check the box enabling on the fly reprojection, and use the same coordinate reference system as you did for your 'correct' layer.

Now you've got a workspace that will reproject any added data into the same CRS as your main data. So add your 'uncertain' layer, and if you change the CRS of that layer (double click the layer, go to general, change the CRS) then it will be shifted into the same CRS as your workspace. And, so, if you choose the correct CRS, it will align.

Once you know what CRS it should be, then go to PostGIS and update it there. Or go back and load it with the '-s 4618' argument.