ST_Transform and ST_SetSRID: To project or not to project?
People often get confused between the
ST_SetSRIDdoesn't change the coordinates but adds meta data to state what spatial reference system the coordinate actually are. If you stamped your WGS 84 long lat data as a meter based projection. Guess what? Its still long lat. A spade by any other name is still a spade so don't use ST_SetSRID and expect to magically get meter coordinates.
ST_Transformis used to change the underlying coordinates from a known spatial reference system to another known spatial reference system.
In PostGIS 2+ it's pretty easy to correct mistakes you've made with standard ALTER TABLE commands. We'll demonstrate a couple of scenarios
You forgot to specify the spatial reference system of your data or specified it wrong, but you know its WGS 84 long lat
1ALTER TABLE mytable 2 ALTER COLUMN geom TYPE geometry(MultiPolygon,4326) 3 USING ST_SetSRID(geom,4326);
Your data is WGS 84 long lat, and you tagged it correctly but you want it in US National Atlas meters
1ALTER TABLE mytable 2 ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) 3 USING ST_Transform(geom,2163);
You brought your data in as unknown, you know its wgs 84 lon lat, but you want to convert it to US National Atlas meters
1ALTER TABLE mytable 2 ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) 3 USING ST_Transform( 4 ST_SetSRID( geom,4326 ) 5 , 2163 6 );