Changes between Version 47 and Version 48 of InstallationGuidelines/PostgreSQL


Ignore:
Timestamp:
03/11/11 21:10:43 (11 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • InstallationGuidelines/PostgreSQL

    v47 v48  
    150150If using PostGIS, then once the tables have been created:
    151151{{{
    152 su postgres
    153 psql
    154 \d sahana
    155 # Ensure all POINTs have WKT entries
     152cat << EOF > "/tmp/geometry.sql"
    156153UPDATE public.gis_location SET wkt = 'POINT (' || lon || ' ' || lat || ')' WHERE gis_feature_type = 1;
    157 # Add geometry Column
    158154SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'GEOMETRY', 2 );
    159 # Populate existing entries
    160155UPDATE public.gis_location SET the_geom = ST_SetSRID(ST_GeomFromText(wkt), 4326);
    161 exit
     156EOF
     157su -c - postgres "psql -q -d sahana -f /tmp/geometry.sql"
    162158}}}
    163159(if using Windows can use the Query Editor that comes with pgAdmin III)
     
    165161Ensure that future entries auto-populate properly:
    166162{{{
    167 su postgres
    168 psql
    169 \d sahana
     163cat << EOF > "/tmp/autopopulate.sql"
    170164CREATE OR REPLACE FUNCTION s3_update_geometry()
    171   RETURNS "trigger" AS
    172 $BODY$
     165  RETURNS "trigger" AS \$$
    173166  DECLARE
    174167  BEGIN
    175 
    176168    if (NEW.wkt != '') then
    177         NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326);
    178         end if;
     169        NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326);
     170        end if;
    179171
    180172    RETURN NEW;
    181173  END;
    182 $BODY$
    183   LANGUAGE 'plpgsql' VOLATILE;
     174\$$  LANGUAGE 'plpgsql' VOLATILE;
    184175ALTER FUNCTION s3_update_geometry() OWNER TO sahana;
    185176CREATE TRIGGER s3_locations_update
     
    189180  EXECUTE PROCEDURE s3_update_geometry();
    190181
    191 exit
     182EOF
     183su -c - postgres "psql -q -d sahana -f /tmp/autopopulate.sql"
    192184}}}
    193185