[OpenLayers-Users] Creating Vector layer from MySql database

[Mike Ryan]

I'll answer the last part first:

1. Create the Vector layer and add it to the map.

var map = new OpenLayers.Map("map");
var my_vector_layer = new OpenLayers.Layer.Vector("My Vector Layer");
map.addLayer(my_vector_layer);

2. Add features to the layer by first using your method of choice to get the lat/lons into javascript and then looping through the lat/lons. My example below adds a graphic of your choice at the point:

var feature = new OpenLayers.Feature.Vector(
			new OpenLayers.Geometry.Point(YourLon, YourLat),
			{externalGraphic: '/path/to/your/graphic/image/'});
	
my_vector_layer.addFeatures(feature);

Before you do 1. & 2.  you need your data, of course.

3. In order to get your data from MySQL to javascript -- there is no direct path, no OpenLayers function or object that will magically do that. That's the nature of javascript, it can't access your database. 

So, you'll have to use something else in order to get the data out of MySQL. To me, the easiest thing to do is write a little script that queries your database and call it with a XMLHttpRequest. OpenLayers provides an XMLHttpRequest function http://docs.openlayers.org/library/request.html.

I recommended looking at the TileStitching example, because I figured out how to use XMLHttpRequest through that: http://trac.osgeo.org/openlayers/wiki/TileStitchingPrinting

Focus on this part of that example:

OpenLayers.Request.POST(
      { url:'path/to/your/PHPScript.php',
        data:OpenLayers.Util.getParameterString({width:size.w,height:size.h,tiles:tiles_json}),
        headers:{'Content-Type':'application/x-www-form-urlencoded'},
        callback: function(request) {
           window.open(request.responseText);
        }
      }
    );


The url points to your script. And if your database is static, or you know you will always query the same data set, you don't even need the data parameter.

Have your script print out the query results at the end of the script, and they will be returned in the request object in its responseText property. Now you have the data from your MySQL database. 

Note that you have to do all the work to take what your script outputs and make it useable by javascript. JSON might be the way to go. But you could even send it as a comma delimited list. The problem is javascript doesn't care if you put your data in a Python List or a PHP Array, it won't know what to do with that. That's where JSON can be handy, because it will translate things like that for you. 

Hopefully, this has not made things more confusing. It's just that getting information from a database into javascript is surprisingly more complicated than you would think. 

On May 4, 2012, at 6:07 PM, Smaran Harihar wrote:

> Hi Mike,
> 
> Thanks for the reply. Presently I am only using OpenLayers and python
> for extracting info from rasters.
> 
> Now I have a new task in which the data is present in MySql database.
> So i need to extract the info and create a Vector Layer (basically
> points or polygons(bounding box)).
> 
> So should I create the layer first or should I create it on the fly?
> And also how?
> 
> 
> -- 
> Thanks & Regards
> Smaran Harihar
> 
> 
> On Fri, May 4, 2012 at 12:03 PM, Michael Ryan <mr at mry4n.net> wrote:
>> Hi Smaran,
>> 
>> If you're using something like PHP, you can dump the results either
>> visibly or invisibly on your page, and then scoop up those results
>> with javascript.
>> 
>> Or, if you want to be really cool (and possibly go a little crazy),
>> you can try using OpenLayers.Request.POST and JSON, which will still
>> require something like PHP to query the database.
>> 
>> Check out the TileStitching example for using the second method.
>> 
>> -Mike