A script/query adding vehicle_location_point to siri_arrivals

[daphshez]

(note: this task is a little similar to #28, but is slightly simpler because indexing isn't required, and also has higher priority)

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:

• Adds a geometry column named vehicle_location_point to the siri_arrivals table.
• Updates the column to contain the data from vehicle_location_lat and vehicke_location_lon in the same table.

The script location should be under the /postgres folder in the source code.

Some additional pointers:

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

What you need to know to implement this task

• SQL, some PostGIS. If you don't know any PostGIS, these should help:
  • Manual
  • Tutorial
• You'll be touching the siri_arrivals table, but you don't really need to understand much about it except for the vehicle_location_lon & lat fields
• You can probably create the query 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 use pull requests to submit your code.

[LiorDL]

Create column:
SELECT AddGeometryColumn ('siri_arrivals_filtered','vehicle_location_point',4326,'POINT',2);
Populate data:
update siri_arrivals_filtered set vehicle_location_point = ST_SetSRID(ST_MakePoint(cast(vehicle_location_lon as double precision), cast(vehicle_location_lat as double precision)),4326);

[LiorDL]

The populate data query should also have the line:
where vehicle_location_lon !=''
(Otherwise the cast caused it to fail).
Only thing left to do is to add these queries to the loading scripts.

[MYank0]

@daphshez can we close?

[LiorDL]

Yes, I'm done with it =) @daphshez

[LiorDL]

Note that has been changed so that there is no need to actually hold siri_arrivals_filtered column, we added it to the query that populates the data in Add route offset to siri_arrivals