Create a template_postgis database Some might find this useful for creating PostGIS databases without having to be PostgreSQL super users. The idea is to create a template_postgis database, install plpgsql and postgis into it, and then use this database as a template when creating new PostGIS databases.

Now non-super users can create PostGIS databases using template_postgis:

$ createdb my_geo_db -W -T template_postgis

It appears the rotate function in PostGIS only rotates the geometry about the center of the co-ordinate systems. There was a neat little trick that was mentioned on the postgis-list by Bruce Rindhal to rotate a polygon about its center:

CREATE TABLE shapes (gid serial PRIMARY KEY,  name varchar);
SELECT AddGeometryColumn('', 'shapes', 'the_geom', -1, 'POLYGON', 2);
insert into "shapes" ("the_geom", "name") values (
       'POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))', 'laying down');

Next we are going to translate the polygon’s centroid to (0,0). Rotate it by 45 degrees and then move it back to the original centroid.

SELECT
translate(
  rotate(
    translate( the_geom, -x(centroid(the_geom)), -y(centroid(the_geom)) ),
    radians(45)
  ),
  x(centroid(the_geom)), y(centroid(the_geom))
)
from shapes where name = 'laying down';

Without the translate on line 4 and line 2 and 7 it would look like this:

Some might find this useful for creating PostGIS databases without having to be postgresql superusers. The idea is to create a template_postgis database, install plpgsql and postgis into it, and then use this database as a template when creating new PostGIS databases.

Now non-superuser’s can create postgis db’s using template_postgis:

createdb -h host-name my_gisdb -W -T template_postgis