· 8 years ago · Jun 02, 2018, 01:38 PM
1DROP TABLE if exists locations;
2
3CREATE TABLE locations
4(
5 location_id integer PRIMARY KEY,
6 poi_id integer references pois(poi_id),
7 freguesia text NOT NULL,
8 concelho text NOT NULL,
9 distrito text NOT NULL,
10 coord geometry NOT NULL
11);
12
13CREATE SEQUENCE api_seq
14 START WITH 1
15 INCREMENT BY 1
16 MINVALUE 1
17 NO MAXVALUE
18 CACHE 1;
19
20COMMIT;
21
22INSERT INTO locations (location_id, poi_id, freguesia, concelho, distrito, coord)
23
24WITH
25 initial_points
26 AS
27 (
28 SELECT DISTINCT caop.freguesia,
29 caop.concelho,
30 caop.distrito,
31 taxi_services.initial_point as location
32 FROM taxi_services, caop
33 WHERE ST_WITHIN(taxi_services.initial_point,caop.geom)
34 ),
35 final_points
36 AS
37 (
38 SELECT DISTINCT caop.freguesia,
39 caop.concelho,
40 caop.distrito,
41 taxi_services.final_point as location
42 FROM taxi_services, caop
43 WHERE ST_WITHIN(taxi_services.final_point,caop.geom)
44 )
45
46 SELECT nextval('api_seq') as location_id,
47 NULL,
48 ip.freguesia,
49 ip.concelho,
50 ip.distrito,
51 ip.location
52
53 FROM initial_points ip
54
55UNION
56
57 SELECT nextval('api_seq') ,
58 NULL,
59 fp.freguesia,
60 fp.concelho,
61 fp.distrito,
62 fp.location
63
64 FROM final_points fp;