![]() It would create art, but not a useful line. In this case it fixed a problem I was having where the query would “connect the dots” in a random order. The cool part is the ORDER BY inside the ST_Makeline aggregate Postgres is full of awesome little tricks like that. SELECT row_number() OVER () As id, ride_date, ST_Length(ST_Transform(ST_Makeline(the_geom ORDER BY ride_time),26916)) As length FROM rides.ride GROUP BY ride_date ORDER BY length DESC To get around it I think I’m going to build a PL/Ruby / Python / Perl function to loop through the rides and split lines based on their time stamp and throw the results into a multiline. Currently if you stop your Garmin and move the query ignores the stoppage and counts the move as part of your ride. I haven’t posted it because it only works for rides where you don’t take a break. I mentioned the query I came up with to “connect the dots” in a post I made about turning my ride data into Chernoff faces. ![]() Thanks! If I put out the raw data the cyclist part would be debateable. SELECT AddGeometr圜olumn('','ride','the_geom',4326,'POINT',2) Īnd then to convert the lat/long data: UPDATE ride SET the_geom = ST_SetSRID(ST_Point( longitude, latitude), 4326) Īnd finally, add a spatial index to speed things up: CREATE INDEX idx_ride_the_geom ON ride USING GIST (the_geom) Īnd you’re done! Later I’ll put together a batch script to handle the conversions, build polylines from the points collected, calculate some medians and run other statistical analysis on my data. Once your data is loaded we need a geometry column so we can convert our lat/long data to geometry. If you’re using Windows for simplicity’s sake you’ll still want to use forward slashes in the path to your csv file, like so: FROM ‘C:/output_file.csv’. ![]() I like to use Postgresql’s COPY command to do this. Next up, loading the data from the CSV file. When the data comes in altitude will be in meters, temperature in Celsius, and speed will be in meters per second, even if your recording device is set to display your data in Imperial units. If you don’t have a cadence sensor you won’t have a cadence field, and if you have a power meter you may have more fields available to you than I have. You’ll need the id field, or another primary key field if you want to display the data in some (most?) GIS applications. CREATE TABLE ride ( id serial primary key, Now you’ll need to create a table in your PostGIS database. Replace input_file.fit and output_file.csv with your input and output file names. From the command line you would do the following: gpsbabel -t -i garmin_fit -f input_file.fit -o unicsv,datum=WGS 84,format=0,filename=0 -F output_file.csv To do this you can either use the GPSBabel GUI or GPSBabel’s command line utility. For our needs we’ll be converting FIT files from a Garmin Edge to a simple CSV text file. GPSBabel is a cross platform tool that can convert GPS data from almost any GPS device to the format used by another GPS device. Now FIT files created on Garmin Edge and other devices can be converted into formats that can be imported into PostGIS. This update fixed GPSBabel’s FIT file compatibility. GPSBabel released an update (1.4.4) on Labor Day.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |