A script/query for creating gtfs_shape_lines table

[daphshez]

We are want to add geometry fields to our database in order to be able to run geoqueries using PostGIS.

The task is to create a script that:

• Creates a new table, gtfs_shape_lines, with two fields: shape_id (INTEGER NOT NULL) and shape_line (geometry)
• Insert data from gtfs_shapes to gtfs_shape_lines. All the records with the same shape_id should in gtfs_shape should be merged into a single record in gtfs_shape_lines. The line data should be a concatenation of the of the shape_pt_lon & shape_pt_lan fields, ordered by shape_pt_sequence.
• Optional for extra credit: before loading the line strings, run shape simplification (https://github.com/hasadna/open-bus/blob/master/gtfs/parser/simplifyshapes.py). This should be useful because the data in gtfs_shapes is over sampled.

The script location should be under the /postgres folder in the source code. If you need Python code, put the script under /gtfs/parser

Additional pointers:

• Use AddGeometryColumn rather than ALTER TABLE (see here why)
• These are probably the correct parameters for add geometry column: srid = 4326 , type = "LINESTRING"
• When inserting the data, shape_pt_lon is the X value and shape_pt_lat is the Y value

What's would you need to implement this?

• SQL, Some PostGIS, probably also Python. If you don't know any PostGIS, these should help:
  • Manual
  • Tutorial
• You'll need to understand the gtfs_shapes table. Read about the GTFS tables here and play with them using the re:dash interface.
• If you need a local dev copy of the database, see Create a database dump for dev environment + docker? #32
• Clone the repository and submit the query using a pull request.

[MYank0]

Query created and saved https://github.com/hasadna/open-bus/blob/master/postgres/postgis_queries.
DB updated with the table.
@daphshez can we close?