I am doing a road network project. I want to obtain each segment's GPS coordination. I just converted shape file data to mysql database and there is an attribute called
SHAPE and it is "geometry" type. I tried to
select SHAPE from table
and the output is messy.
Does anyone know how to open this "geometry" typed SHAPE attribute?
I also want to know if shape file is the right one to use because there is another type of data: geodatabase. Which one is better if I want to extract road network data.
Best How To :
Mysql supports several geometry functions for editing and querying spatial data. See here for the full list
-- show the WKT representation of the geometry
SELECT ST_AsText(SHAPE) FROM table;
-- show the coordinates of start and end point of the lines
SELECT ST_AsText(ST_StartPoint(SHAPE)), ST_AsText(ST_EndPoint(SHAPE)) FROM table;
-- show how many points build the line
SELECT ST_NumPoints(SHAPE)) FROM table;
-- length of the lines
SELECT ST_Length(SHAPE)) FROM table;
If you need all coordinates of your lines, you can simply parse the result of the
ST_AsText(SHAPE) query. Alternatively, I would recommend this workflow:
1) Query the number of the points of each line with the function
2) iterate over 1 and the number of points and use the function
ST_X(ST_PointN(SHAPE, n)) and
ST_Y(ST_PointN(SHAPE, n)) to get the coordinates of the actual point, where
n is the actual number
The iteration can you implement in a mysql stored procedure or a external program or script.
See here for linestring specific functions.
Geodatabase OR Shapefile? You are currently using neither shapefile nor geodatabase, but MySql. If you only want to import network data in a database, shapefile are ok. On the other hand, with geodatabase you could probably better work in ArcGis, without using external tools.
If you want to use a external database, I would recommend you to import the shape file in PostgreSQL and not in MySql, since it has a more powerful support for spatial data.