DatabaseProject/init.sql at main · Carlos-Fletes/DatabaseProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS places (
    place_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    place_name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(50) NOT NULL,
    latitude DECIMAL(9, 6) NOT NULL,
    longitude DECIMAL(9, 6) NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL
);

INSERT INTO places (
    place_name,
    category,
    address,
    city,
    state,
    latitude,
    longitude,
    location
)
VALUES
    (
        'Statue of Liberty',
        'Landmark',
        'Liberty Island',
        'New York',
        'NY',
        40.689247,
        -74.044502,
        ST_SetSRID(ST_MakePoint(-74.044502, 40.689247), 4326)::GEOGRAPHY
    ),
    (
        'Willis Tower',
        'Skyscraper',
        '233 S Wacker Dr',
        'Chicago',
        'IL',
        41.878876,
        -87.635915,
        ST_SetSRID(ST_MakePoint(-87.635915, 41.878876), 4326)::GEOGRAPHY
    ),
    (
        'Griffith Observatory',
        'Observatory',
        '2800 E Observatory Rd',
        'Los Angeles',
        'CA',
        34.118434,
        -118.300393,
        ST_SetSRID(ST_MakePoint(-118.300393, 34.118434), 4326)::GEOGRAPHY
    ),
    (
        'Space Needle',
        'Landmark',
        '400 Broad St',
        'Seattle',
        'WA',
        47.620422,
        -122.349358,
        ST_SetSRID(ST_MakePoint(-122.349358, 47.620422), 4326)::GEOGRAPHY
    ),
    (
        'The Alamo',
        'Historic Site',
        '300 Alamo Plaza',
        'San Antonio',
        'TX',
        29.425967,
        -98.486142,
        ST_SetSRID(ST_MakePoint(-98.486142, 29.425967), 4326)::GEOGRAPHY
    )
ON CONFLICT DO NOTHING;