Changes between Version 30 and Version 31 of InstallationGuidelines/PostgreSQL

Timestamp:

12/02/10 22:01:50 (15 years ago)

Author:

Fran Boon

Comment:

Add Geometry column to gis_location

InstallationGuidelines/PostgreSQL

@@ -133 +133 @@
 == Add Geometry column to gis_location ==
 If using PostGIS, then once the tables have been created:
+
+Add geometry Column:
 {{{
 su postgres
@@ -139 +141 @@
 SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'GEOMETRY', 2 );
 exit
+}}}
+(if using Windows can use the Query Editor that comes with pgAdmin III)
+
+Populate existing entries:
+{{{
+su postgres
+psql
+\d sahana
+UPDATE public.gis_location SET the_geom = ST_SetSRID(ST_GeomFromText(wkt), 4326);
+exit
+}}}
+
+Ensure that future entries auto-populate properly:
+{{{
+su postgres
+psql
+\d sahana
+CREATE OR REPLACE FUNCTION s3_update_geometry()
+  RETURNS "trigger" AS
+$BODY$
+  DECLARE
+  BEGIN
+
+    if (NEW.wkt != '') then
+        NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326);
+        end if;
+
+    RETURN NEW;
+  END;
+$BODY$
+  LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION s3_update_geometry() OWNER TO sahana;
+CREATE TRIGGER s3_locations_update
+  BEFORE INSERT
+  ON gis_location
+  FOR EACH ROW
+  EXECUTE PROCEDURE s3_update_geometry();
+
+exit
+}}}
+
+Tell Sahana that the DB is spatially-enabled so that routines can make use of this:
+{{{
 vim models/0000_config.py
 deployment_settings.gis.spatialdb = True