k1uNgRbi

1echo Rolling back schema change #35
2
3BEGIN;
4
5DELETE FROM schema_version WHERE schema_id = 35;
6
7DROP TABLE IF EXISTS location_coordinates;
8DROP FUNCTION IF EXISTS location_coordinates_populate();
9
10END;
11
12echo Applying schema change #35
13
14BEGIN;
15
16INSERT INTO schema_version(schema_id, description) VALUES(35, 'Add location_coordinates table');
17
18CREATE TABLE location_coordinates(
19 location_id INT PRIMARY KEY REFERENCES location(location_id),
20 latitude FLOAT NOT NULL,
21 longitude FLOAT NOT NULL,
22 earth_coordinates earth NOT NULL,
23 box_10miles cube NOT NULL
24);
25
26GRANT SELECT, INSERT, UPDATE, DELETE ON location_coordinates TO ui;
27
28CREATE FUNCTION location_coordinates_populate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS $$
29BEGIN
30  new.earth_coordinates := ll_to_earth(new.latitude, new.longitude);
31  new.box_10miles := earth_box(new.earth_coordinates, 10 * 1609.344);
32  RETURN new;
33END
34$$;
35
36CREATE TRIGGER location_coordinates_populate BEFORE INSERT OR UPDATE ON location_coordinates
37 FOR EACH ROW EXECUTE PROCEDURE location_coordinates_populate();
38
39INSERT INTO location_coordinates(location_id, latitude, longitude)
40 SELECT location_id, latitude, longitude FROM location WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
41
42CREATE INDEX location_coordinates_10miles ON location_coordinates USING gist (box_10miles);
43
44END;
45
46echo Done