Using MySQL with GeoServer

Question

I am storing all my features in MySQL db and I am getting them with ajax and adding to the OpenLayers map. Now I have to many features and it's taking a lot of time to get all of them and adding them, so I want to load only little bit from them and show all the rest with GeoServer in a WMS layer. I found some plugins but nothing really worked, and I didn't find any example. The features are stores in the database as a regular table not as spatial data, only have attributes and a col for WKT for the location.

Is there a way to add this features from mysql db to GeoServer layer?

Answer 1

You would need to use the MySQL spatial functions to cast your WKT or other attributes(such as float X/Y) to geometry objects, then point geoserver(with mysql plugins installed) to those tables and have geoserver render them.

Something like:

UPDATE myPoints SET the_geom = GeomFromText('POINT(long ,lat)'));

You can refer to this question for a more complete answer for adding a geom column and updating it with whatever geographic information you have now.

Answer 2

I haven't tried this but if you need to have the geometry stored as WKT for whatever reason as opposed to a real geometry column, you could create a new layer based on a SQL View and create the geometry column on the fly using WKT->Geometry conversion functions.

The catch is the layer is not modifiable.