Changes between Version 30 and Version 31 of InstallationGuidelines/PostgreSQL


Ignore:
Timestamp:
12/02/10 22:01:50 (11 years ago)
Author:
Fran Boon
Comment:

Add Geometry column to gis_location

Legend:

Unmodified
Added
Removed
Modified
  • InstallationGuidelines/PostgreSQL

    v30 v31  
    133133== Add Geometry column to gis_location ==
    134134If using PostGIS, then once the tables have been created:
     135
     136Add geometry Column:
    135137{{{
    136138su postgres
     
    139141SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'GEOMETRY', 2 );
    140142exit
     143}}}
     144(if using Windows can use the Query Editor that comes with pgAdmin III)
     145
     146Populate existing entries:
     147{{{
     148su postgres
     149psql
     150\d sahana
     151UPDATE public.gis_location SET the_geom = ST_SetSRID(ST_GeomFromText(wkt), 4326);
     152exit
     153}}}
     154
     155Ensure that future entries auto-populate properly:
     156{{{
     157su postgres
     158psql
     159\d sahana
     160CREATE OR REPLACE FUNCTION s3_update_geometry()
     161  RETURNS "trigger" AS
     162$BODY$
     163  DECLARE
     164  BEGIN
     165
     166    if (NEW.wkt != '') then
     167        NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326);
     168        end if;
     169
     170    RETURN NEW;
     171  END;
     172$BODY$
     173  LANGUAGE 'plpgsql' VOLATILE;
     174ALTER FUNCTION s3_update_geometry() OWNER TO sahana;
     175CREATE TRIGGER s3_locations_update
     176  BEFORE INSERT
     177  ON gis_location
     178  FOR EACH ROW
     179  EXECUTE PROCEDURE s3_update_geometry();
     180
     181exit
     182}}}
     183
     184Tell Sahana that the DB is spatially-enabled so that routines can make use of this:
     185{{{
    141186vim models/0000_config.py
    142187deployment_settings.gis.spatialdb = True