Saving Google Maps Polygon in MySQL

[Imrana]

Hi all,

 

Thank you for viewing my post. In my application I allow users to draw shapes on google maps using google maps api v3.

I dont know how to store the polygons in MySQL? Im new to google maps api and dont have a clue. Please help.

I was thinking should I store each point seperately in a database field or store the whole polygon as json?

 

This is what I have so far. I have created a json object that stores all the verticles in a list. I was thinking of saving this whole json in one database field?

 

This is the code used to contruct the array:

var vertices = selectedShape.getPath(); // MVCArray           

var pointsArray = []; //list of polyline points  
for (var i =0; i < vertices.getLength(); i++) {
    var xy = vertices.getAt(i); //LatLang for a polyline
    var item = { "lat" : xy.lat(), "lng":xy.lng()};
    pointsArray.push(item);
}

var polygon = {"points" : pointsArray};

This is the constructed json result:

{"points": [ {"lat": 51.51814351604911, "lng": -0.14479637145996094 }, { "lat": 51.51830374452608, "lng": -0.13861656188964844}, { "lat": 51.516194024429446, "lng": -0.13968944549560547}  ]}

Should I have the whole thing in one field? Is it better to save each vertices (lat and lng) in a seperate row, so for the above polygon there will be 3 rows?

 

Kind regards,

Imran

 

[Imrana]

Hi all,

 

Thank you for viewing my post.

 

In my application I allow users to draw shapes on google maps using google maps api v3.

 

I dont know how to store the polygons coordinates in MySQL? Please help.

I was thinking should I store each point as a seperate row or store the whole polygon in one row as json? Is there a better way?

 

Kind regards,

Imran

 

[Barand]

It would depend on what uses you would have for the coordinates. If you need search on coordinates,for example, to match areas with common coordinates to find adjacent areas, then you need to normalize and store in separate rows. If they are purely for drawing polygons then it doesn't really matter. If in doubt, I'd normalize for flexibility.

[Zane]

Well the first step is to figure out how to output such data i.e the coordinates?  If you already know how to retrieve them, then how do they look?

 

To what kind of format do the coordinates output?  I tried googling for it, but I gave up when I found a v3 demo

http://nettique.free.fr/gmap/toolbar.html

 

Still, the author does not allow you to grab the coordinates.

 

Example answers could be JSON, CSV, etc... 

Posting the coordinates (actual or modified) would be more preferred though.

 

EDIT

Looking at your other thread, I see that you have the coordinates in an array
http://forums.phpfreaks.com/topic/281402-google-maps-add-click-listener-to-each-polygon/

 

A quick and dirty way to store an array in a database is to serialize it and then base64_encode it, then insert it.

Storing it this way will keep you from being able to query the database using geospatial algorithms in the select; doing so would allow you to perform unthought of queries like, list all cities within 100 miles of a certain polygon's specific coordinate.

Edited August 20, 2013 by Zane

[kicken]

For an app I've been working on, I chose to store them as rows in a separate table. At the moment all I do is restore them to display the polygon so a simple encoded field would have worked just as well, however I figure it would be best to keep things separate just incase the future of the app needs it. It does not require much additional overhead to re-combine the rows into an array of points. These are my table setups

CREATE TABLE geofences (
	FenceId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
	, Name VARCHAR(100) NOT NULL
);
CREATE TABLE geofence_points (
	FenceId INT NOT NULL
	, PointNumber INT NOT NULL
	, Latitude DOUBLE NOT NULL
	, Longitude DOUBLE NOT NULL
	, PRIMARY KEY (FenceId, PointNumber)
);

To display the fence, combine all the points into an array when pulling them from the DB. The code looks a bit like this:

$sql = '
SELECT	gf.FenceId as fenceId, gf.Name as fenceName, gfp.PointNumber as pointNumber, gfp.Latitude as latitude, gfp.Longitude as longitude
FROM geofences gf
INNER JOIN geofence_points gfp ON gf.FenceId=gfp.FenceId
WHERE gf.FenceId=:id
ORDER BY gfp.PointNumber
';

$stmt = $db->prepare($sql);
if (!$stmt) die('<pre>'.print_r($db->errorInfo(),true).'</pre>');

$stmt->bindValue(':id', $EditId);
if (!$stmt->execute()) die('<pre>'.print_r($stmt->errorInfo(),true).'</pre>');

while ($row=$stmt->fetch()){
	if (empty($FenceData)){
		$FenceData=array(
			'fenceId' => $row['fenceId']
			, 'fenceName' => $row['fenceName']
			, 'path' => array()
		);
	}

	$FenceData['path'][$row['pointNumber']] = array('lat' => floatval($row['latitude']), 'lng' => floatval($row['longitude']));
}

[requinix]

+1 for storing the points. Storing as JSON is compact and all but severely limits what you can do with the data before you have to json_decode() it in some PHP.

[Imrana]

Hi all,

 

I would like you thank everyone for their help. Thank you Kicken for your examples that really helped me picture how to read the data back. 

I just need to draw the shapes and display them. No searching will be needed but I will store the points separately in another table as thats a better design as suggested by everyone.

 

One other thing I need to do it edit the polygon once I get the data from the database and display it. Is there a way of adding a reference to the polygon - adding the primary key from my database table? This way I can check if the shape exists and I need to update instead of insert.

 

Here is my code to assign that created the shape:

var dbpolygon=new google.maps.Polygon({
                path: points, 
                strokeColor: red,
                strokeOpacity: 0.4,
                strokeWeight: 2,
                fillColor: red,
                fillOpacity: 0.4,
                editable: false
            });

            dbpolygon.setMap(map);
            google.maps.event.addListener(dbpolygon, 'click', function() {
                setSelection(this);           
            });   

I dont know how I would add a unique reference? 

  

 

Thank you.

[Imrana]

Ive got it working, it turns out you can add a custom property, see code below:

var dbpolygon=new google.maps.Polygon({
                id: polygon[0].id, /*** custom property*****/
                path: points,
                strokeColor: polygon[0].type,
                strokeOpacity: 0.4,
                strokeWeight: 2,
                fillColor: polygon[0].type,
                fillOpacity: 0.4,
                editable: false
});

I can check to see if this property exists, this is the code

if (typeof selectedShape.get('id') !== 'undefined') {
    //polygon exists
}