470 | | # PostGIS the Sahana DB |
471 | | cat << EOF > "/tmp/geometry.sql" |
472 | | UPDATE public.gis_location SET wkt = 'POINT (' || lon || ' ' || lat || ')' WHERE gis_feature_type = 1; |
473 | | SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'GEOMETRY', 2 ); |
474 | | GRANT ALL ON geometry_columns TO sahana; |
475 | | UPDATE public.gis_location SET the_geom = ST_SetSRID(ST_GeomFromText(wkt), 4326); |
476 | | EOF |
477 | | su -c - postgres "psql -q -d sahana -f /tmp/geometry.sql" |
478 | | cat << EOF > "/tmp/autopopulate.sql" |
479 | | CREATE OR REPLACE FUNCTION s3_update_geometry() |
480 | | RETURNS "trigger" AS \$$ |
481 | | DECLARE |
482 | | BEGIN |
483 | | if (NEW.wkt != '') then |
484 | | NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326); |
485 | | end if; |
486 | | |
487 | | RETURN NEW; |
488 | | END; |
489 | | \$$ LANGUAGE 'plpgsql' VOLATILE; |
490 | | ALTER FUNCTION s3_update_geometry() OWNER TO sahana; |
491 | | CREATE TRIGGER s3_locations_update |
492 | | BEFORE INSERT |
493 | | ON gis_location |
494 | | FOR EACH ROW |
495 | | EXECUTE PROCEDURE s3_update_geometry(); |
496 | | |
497 | | EOF |
498 | | su -c - postgres "psql -q -d sahana -f /tmp/autopopulate.sql" |