March 21, 2022 at 4:30 am
Hi all,
I am looking for help.
My ultimate goal is to build a platform that allows me to record all my hiking trips with a my Garmin GPS unit, and Garmin smartwatch.
When I return home from an activity, I would then upload the GPX file to the platform and into database.
Next step is to build a great website/app ui to see and analyse the data. Basically just like starve, runkepper, Garmin connect do now.
The thing is I then want to have the platform compare say my user profile to other users profile based on my walking speed, pace, fitness, age etc walking the same track. So it takes me 4 hours to walk this track with x elevation and x distance but may take user two 5 hours based on their age, fitness speed etc.
So, first step is to get coding the be able to take attached GPX file data and break it into all the relevant information like total time, distance, speed, elevations gain/loss heart rate, total time, moving time, average speed etc and also to produce the map of the walk. I would like to automate and much as possible rather than manual entry. Would appreciate any help to try do this. Thanks Bryan
The server version is 8.0.28 (MySQL Community Server - GPL) I is running on my older 1Mac that has been upgraded running macOS Catalina
Have attached a GPX file of the a recent walk. This is from my Garmin Forerunner 245 Smartwatch. Also attached a few pictures of the ui I would eventually look like to get built
Appreciate any help. Thanks
March 21, 2022 at 4:32 am
Will try upload GPX file again. Thanks
March 21, 2022 at 9:52 pm
Personally, I wouldn't put the GPX file on there. I have never looked at those, but I have a feeling that it contains GPS data in there. People could use that information to find where you live (for example).
Your project sounds like it is ripe full of information that hackers would LOVE to get access to. My opinion, I would NOT work on a project like this, especially with the risk of data breach. If I put my GPX file in there and from that someone finds out where I live and actively targets me for things, I would be coming at you to prove to me that you were not hacked and that there was no data breach.
On top of that, lets say I see someone I think is attractive on my run, so I load up your app/webpage and see all of the people on there that did the same track as me at or around that time. I can now narrow down who that person was. Repeat this for a few days/weeks and I can probably rule it down to an individual (assuming they are on your app), and now it is trivial for me to start stalking them!
If your app was run on my local machine with no personal data being sent across the internet to be processed/analyzed/compared with others, the app would be more interesting and promising, but the way you describe it sounds like a haven for stalkers!
Now, to add to that, this community is for SQL Server, which is not the same thing as "MySQL". MySQL and SQL Server are owned by 2 completely different entities and SQL Server 2019 is a specific version of that tool.
So, the rest of my reply will be directed at SQL Server 2019, not mySQL. I am not 100% certain what you are asking in your question, but I think you are asking if anybody on the forum knows how to parse a Garmin GPX file and use that to populate a SQL database where they could build a front end to parse the data and compare results between users. If I am reading that right, you are asking a LOT of a free forum. I would recommend first checking if GPX is an open format OR has a method of converting it to a consumable format (CSV, JSON, XML, Excel, etc) and once you have that, you can use SSIS (or similar) to import the data into the database. From there, you just need to build a tool that can parse the data that was pulled in so you can build what you need. Again though, this is a HUGE ask for a free platform/tool. There is a reason why the companies that offer these "free" services can do it - they have a team of developers working on it AND they make money on it. You need their GPS devices to make use of their services. And that being said, I would not be surprised if these services are subscription based in which case they are not really free services.
I doubt you will find anyone on the forum willing to build this for you or even research it for you! I would start by researching what you can do to parse a GPX file, but I would not be surprised if these are proprietary and possibly even encrypted per user account in order to protect the data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 22, 2022 at 5:47 am
Thanks Brian for the great reply and insight. Appreciate the honest feedback.Your reply has certainly opened my eyes and help me bring some things into perspective.
Let me try reply to your points:
Safety:
I certainly never considered hackers and personal location data. To stop home addresses, we will only focus on actual public walking tracks and never use tracks that show your personal home. Yes, data privacy is a huge concern to me. We will make sure we have the best privacy and security as we can.
The app won't be live as per-say. It won't show a live view of current people walking like Uber does showing cars everywhere. Walks will be uploaded later.
User names and user profiles will be as secure as we can
Yes, I am asking for help in the forum to try understand how I can get xml data from the GPX file into the database.
Yes, I am asking if anyone in the forum can help take the Garmin GPX file and use that to populate a SQL database where they could help then build a front end to parse the data and compare results between users as you accurately worded it.
Thanks Brian, I never considered trying to convert the file to a csv or excel then import that to database. Will look into that more. Yes, as you again nicely word it, "from there, you just need to build a tool that can parse the data that was pulled in so we can build what we need"
I am certainly not trying to get free help to build the whole thing. The database size is pretty much taken care of. What I do need help with is extracting the data from GPS xml file, and finding a way to import it into database. We will then work on frontend to show the resulting data in a nice way
I guess, I can only ask and see if anyone will help. I am keen to pursue this and make it the best it can be. Thanks so much for the great input Brian 🙂
March 22, 2022 at 4:38 pm
Personally, I think that sounds like a lot to work on, but I am hoping that you do get some interest in it. One concern an end user may have though is how you are going to parse the GPX file to remove PII and where that operation will occur. If it happens on your server, there is risk and if I was a client, I'd be hesitant to use your service in the event some PII wasn't properly removed.
For how to parse a GPX file, I think that either reaching out to Garmin OR finding some project on github (if any exists) to parse and convert the GPX into a more consumable format would be your best bet. Doing a quick google, there are a lot of ways to convert a GPX to Excel, I even found an excel addon that will do it for you - https://gisxl.com/HowTo/Special/ImportGPX. The main reason I would start by converting it to Excel is Excel data is MUCH easier to parse and consume in a database (MySQL, SQL Server, etc). I personally would use something a little beefier than MySQL (such as SQL Server, but Oracle DB works too) so that you can scale it easier and have support in the event of bugs in the software, and so you have better encryption options, especially when you are storing data that COULD be seen as PII. Anytime you are storing data that can potentially be identified as PII, you want to make sure your data is encrypted in motion and encrypted at rest, which includes your backups!
Now, if you don't want to convert it, any relational database should be able to take in XML data as it is just plain text. An NVARCHAR(MAX) column (SQL Server naming, but MySQL probably has something similar) could handle that with ease, but then you need to parse it at the application layer or presentation layer, and searching and comparing results will be painful to manage. If you do consume it as plain XML, I would recommend having some automated process to break that XML up into consumable chunks and toss it into one or more tables (depending on your requirements and what data is actually being captured).
I would also be looking at getting someone to host your database and website. I wouldn't host it myself as I don't have the infrastructure or the bandwidth to support too many users on my system at one time. Plus, I wouldn't have good uptime... I'm definitely not at 5 9's... I'm not even at 2 9's LOL. At best, I'd say I am at 80% uptime and that uptime isn't very fast speeds - 0.5Mbps for my upload bandwidth. Plus I would want some hardware between my web host and the internet so I could block hackers before they got into my system. I also don't have any spare computers that I would want running 24/7 for hosting it, nor any that are beefy enough to handle the workload required for that, and I would hope that I wouldn't need to upgrade and scale up the servers within the first year of release; I should plan to have the servers beefy enough to handle year 1 at a minimum so I can gauge interest and profitability of the service.
As for the presentation layer, that is a bit easier once you have the data in a nicely consumable format. Just need to either have some fancy javascript or ASP.NET or something in place to present it nicely to the end user. Since the data is static upon presentation, you could even have some magic on the back end generating JPG's or PNG's that get displayed to the end user and you just generate them on page load. Or you may want something more powerful on the presentation layer and put something like SSRS or PowerBI or Tableau or something to present the data. Alternately, if you want it to be dynamic (ie data can change while end user is viewing it), ASP.NET or JavaScript should be able to tackle that.
Now, all of the above being said, this isn't going to be an easy project. My first step with it wouldn't be "how will I get the GPX data into the database", it would be "what data points do I need and how can I structure the database to provide those". Mind you, I tend to jump into projects without always knowing the requirements and scope. So, it may make sense to determine what ALL of the requirements are and what will be in scope and out of scope and then build up a prototype for the UI (looks like you may already have that based on the images you attached), then find investors. Then, I'd build up a mock version of the tool inside Excel so you can show the investors what things will look and feel like and make sure they are still interested. Once you have that, you can take the Excel data and translate that to the database and then all that is left is the web interface AND a method for GPX imports. Mind you, I am assuming that this is a web app. It could be a desktop application in which case the presentation side can be a lot easier and a lot harder. Easier in that you have a LOT more options as to what you can present and how you can present it, but harder because you now have a lot more variables in how the code is being run and may be more limited in supported OS's. But once you have the data in place (structure and sample data), building the UI should be easier to do as you have the data points. You just need to read those and parse them into a fancy presentation layer. I personally would start by building the authentication layer, then I'd build up the text-only pages (including user administration), and finally do the graphical pages. Then I'd test things out to ensure that it is secure like is it possible to impersonate a user by changing the URI or the cookie? Can I clone the cookie to impersonate a user? Is it possible to bypass the login screen (SQL injection attack or HTML source code modifications)? Is there any PII or potentially exploitable code present to the end user (if they view source, could they compromise the system in any way)? Are you using any 3rd party tools for this that MAY have their own license agreement that you are violating?
Next, I'd test out things that MAY happen - hackers attack your system. What if the sysadmin account to your system is compromised, what can a hacker do from outside your network? What about if a user account is compromised, how are you detecting and alerting the end user(s) or are you? What about if someone compromises the web host, what can they access directly from that web host? Is your SQL instance hosted on the same physical machine as the web host? If so, a compromised web host means a compromised database. Once they have a copy of the MDF OR the backup file, they have all the time in the world to try to get into it. IF your backups AND live data are encrypted properly, it could be a very long time before they get in in which case it is not a big deal. What about if they do a MITM attack on your website OR between the web server and the database? Is there any risk or is that all encrypted? What about DDOS attacks? can your server handle those or will the website crash completely?
Now, if it isn't a web-based thing and is application based, how is the application talking to the database? I am HOPING you have a server accepting API calls that would write back to a database on the back end so you are not exposing your database to the internet. But how secure is that API? is it possible for someone to compromise it and modify data or request data they shouldn't have access to? Is there any PII that YOU could have access to that end users may not want you knowing?
Now, the next fun part - data removal. If you have users from the EU and they request to have their data scrubbed from your server (for example), can you do that or is it going to cause the server to screw some calculations up or screw up some stats for existing users by scrubbing one or more users from your system?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 23, 2022 at 6:34 am
nice info i learn good info
March 23, 2022 at 11:31 am
This was removed by the editor as SPAM
March 23, 2022 at 8:56 pm
This was removed by the editor as SPAM
March 25, 2022 at 8:36 pm
Thanks Brian for the amazing help. This is awesome. Really appreciate the info.We will look into making sure PII is properly removed.
Yes I am reaching out to Garmin and others regarding getting the GPX information into a better format like excel. I appreciate your shared link for converting to excel. I am looking for a windows computer or laptop that's good enough to do all this work as my current machine is a mac. Yeah, sorry lol:) Can't use that link at the moment, but will soon. My son is helping also as he has a windows machine and is learning coding at uni.
Yes, I realised the other day I really need to look at website and database hosts. I certainly don't have the knowledge, information and machines to host all this. I am an ideas guy with a mission. I have some knowledge but need a company to do that. They will also help with making sure things are secure and running all the time.
Yes, I have been trying to understand how and what steps I need to make the project happen. Funny you mention the excel version. I have literally already created an excel version of this project. It has all the fields, tables and information we have at the moment. Ever changing and adding things. It's a good way to see it all and what fields are actually needed in the database. I now want to build an interactive version of this excel data to try to get it to do things like what the finished website and app will do. It's hard but still looking around to figure out how. I have the data, just need to present it without a full blown front end website!
Yes, once all this stage is looking good and we have the right fields, relationships etc we will move to get the frontend website built and test a lot.
Did not think about testing website front and back end for hackers. Thanks for the heads up. I dont have the knowledge as mentioned earlier about all that computer side. I have already added this great point to our development plan.
To be honest, I am not sure about the server accepting API calls and how secure it is. Again this point has been added to our development plan to make sure we don't miss this from my lack of knowledge point of view. I'm sure the developer we get building this will know lots of these things, but glad you ask so I can learn and plan for it.Thanks
Again another great point regarding data removal. Who would have thought lol. Yes you got it, I have added that to and will make sure we discuss and plan for this. Thanks again.
btw Brian, I am looking at using Microsoft SQL Server 2019, currently using MYSQL as you know. I am not sure working with open source MYSQL is the right path for us. Still looking at what's best but at this point once I have the windows PC, I will shift to Microsoft SQL Server 2019
Would love your thoughts, help and any web links to find the best information, coding help and where to get downloads. This project is at the very beginning and will be very small so guessing only one machine will do fine for now. It will just be my data with a few selected friends contributing to the information and GPX files. Once we get to a stage that we think can go open make then will scale the infrastructure as needed.
Love the help and information Brian. Really do appreciate it.
Still trying to find best GPX to database path 🙂
Yes I know I might sound ignorant which I actually am. I am trying to learn and understand the best I can to get this project built. Thanks again for the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply