import route data with GIN topology fixes into ECGA Carto account

[clhenrick]

• [x] Import both the lines and points shapefiles into Carto
• [x] Make copies of both tables and lock them
• [x] Add db trigger so that when a route segment is updated / created pline_id value is added and the meters value is calculated and added
• [x] Test editing with QGIS CartoDB plugin
• [x] Set app configuration to use these tables

[clhenrick]

I imported both shapefiles into the ECGA Carto account and named them:

• ecg_route_cues
• ecg_route_lines

These are the tables that will be edited by the ECGA staff from within QGIS using the QGIS CartoDB plugin.

I copied the ecg_route_lines table, named it ecg_route_lines_prod, and locked it so it that it can't be edited. Same for ecg_route_cues, it's called ecg_route_cues_prod. We'll use these as the "production" tables with the app for now while we continue building out its features, most noticeably the geo routing. The ECGA team will be able to edit the ecg_route_cues and ecg_route_lines tables from QGIS.

[clhenrick]

Noting that for the ecg_lines table the "meters" column to "shape_leng" and I'm renaming it back to "meters". The values were also rounded for this column, that SQL is:

UPDATE ecg_route_lines SET meters = round(meters)::integer

[clhenrick]

Noticed that deleting a table in the new Carto UI and then attempting to rename a table to the same of the deleted one's name results in a number getting appended to the end of the table. So now the production table is called ecg_route_lines_prod_2. Took me two attempts to figure that out, argh.

[clhenrick]

I implemented the database trigger so that when inserts happen:

• length of the segment is calculated and added to it's "meters" field
• datetime_created is updated to the current date time

Separate from the db trigger, I modified the pline_id column so that it's now sequential and will auto update when new rows are inserted.

However, I have no way to test the trigger and tables with QGIS as we don't yet have an API Key from Carto. Waiting to hear back from @nilesbarnes to see if he sent the signed contract to Tyler over at Carto.

@gregallensworth also brought up a good point that we should add a trigger for when line segments are updated:

• recalculate the line segment length in case it was modified
• update the "datetime_created" column to indicate when it was modified (probably makes sense to call "datetime_created" to something else like "date_revised"

Will open a separate issue for creating the new db trigger.

[danrademacher]

Just a note that some of this is being handled by our GIS staff, and some by Chris.

[clhenrick]

@danrademacher think this is safe to close?

[danrademacher]

Yep