Best way to represent trail data in a database?

[santoshamb]

Hello,

I'm currently working on a project where I'm going to be collecting and querying data for hiking trails.  Eventually, I'd like to store all the trails in the US, and import trails from Gov sources like the USGS or the NPS. I'll be overlaying these trails onto a map using a mapping service, so really the only data I need to store will be latitude and longitude for points along the trail.  I currently have a simple table structure:

Trail
  Id
  Name
  Desc
  Ect...

Trailpoint
   Trailid
   Lat
   Lon
   Ect...

Where one trail as many trailpoints.  This works well if I'm accepting trails in GPX format, I can just pull out all the trackpoints and store them into the database.  Unfortunately, it seems that the vast majority of existing trail data is in shapefiles, (like the NPS for instance) and it's not easy to convert over to GPX so that I can import into my simple representation.

Which leads me to question, is my way the best way to represent trails?  Should I be using something more complex like PostGIS?  I'm currently using MySQL and my host doesn't support PostgreSQL so moving over would be difficult.

Thanks for all the help!

Ben

[-Oz-]

postgis/postgresql is pretty easy but there are other options:

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html <-- mysql5 spatial extensions.

I would advise against writing your own because there is the possibility for more errors.  But yes, yours would work for gpx files but all govt data is in at least shapefile.