ST_SetSRID doesn’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_Transform is 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
ALTER TABLE mytable ALTER COLUMN geom TYPE geometry(MultiPolygon,4326) 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
ALTER TABLE mytable ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) 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
ALTER TABLE mytable ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) USING ST_Transform( ST_SetSRID( geom,4326 ) , 2163 );
This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten
ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible
ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
ST_Reclass does the same thing but orders of magnitude faster.
PostGIS 2.2.0 came out this month, and the SFCGAL extension that offers advanced 3D and volumetric support, in addition to some extended 2D functions like
ST_ApproximateMedialAxis became a standard PostgreSQL extension
and seems to be a fairly popular extension.
I’ve seen several reports on GIS Stack Exchange of people trying to install PostGIS SFCGAL and getting things such as
ERROR: could not open extension control file /Applications/Postgres.app/Contents/Versions/9.5/share/postgresql/extension/postgis_sfcgal.control
as in this question: SFCGAL in PostGIS problem.
There are two main causes for this:
I’ve been asked this question in some shape or form at least 3 times, mostly from people puzzled why they get this error. The last iteration went something like this:
I can’t use
ST_AsPNG when doing something like
SELECT ST_AsPNG(rast) FROM sometable;
Gives error: Warning: pg_query(): Query failed: ERROR: rt_raster_to_gdal: Could not load the output GDAL driver.