open "shape" attribute in mysql database

Question

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.

Answer 1

Mysql supports several geometry functions for editing and querying spatial data. See here for the full list

Some example:

-- 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 ST_NumPoints(SHAPE)

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.