Create PostGIS instance on server

[thcrock]

We'll want a PostGIS database on the server to use during graph rebuilds as we start adding in GIS data (road closures, pavement quality data) to the profile

[stevevance]

Here's a sample query to create a table into which street closure data will be added:

Create a table

CREATE TABLE IF NOT EXISTS street_closures (
    id SERIAL PRIMARY KEY,
    geom_4326_start GEOMETRY,
    geom_4326_end GEOMETRY,
    line GEOMETRY,
    date_start DATE,
    date_end DATE
);

Add some indexes

--CREATE INDEX geometry_start ON street_closures USING GIST ( geom_4326_start );
--CREATE INDEX geometry_end ON street_closures USING GIST ( geom_4326_end );
CREATE INDEX line ON street_closures USING GIST ( line );

(The first two indexes aren't actually needed; we only need the index on the line.)

Sample insert query

INSERT INTO street_closures (geom_4326_start, geom_4326_end, date_start, date_end) 
VALUES (ST_SetSRID(ST_MakePoint(fromCoordinates[0], fromCoordinates[1]), 4326), 
ST_SetSRID(ST_MakePoint(toCoordinates[0], toCoordinates[1]), 4326), startDate, endDate);

Update: I still need to add a query that would draw a line between the two points because it's the line that needs to be avoided, not the points.