Convert Well-known text (WKT) from MySQL to Google Maps polygons with PHP

Question

I have searched all over the net and i havent found any solution for this...Any solution I found doesnt work for me... I have a row with VARCHARS and an other with these GEOMETRY types, eg:

POLYGON((23.953261 37.733372,23.953623 37.733202,23.953572 37.733132,23.954075 37.732896,23.954126 37.732966,23.954550 37.732767,23.954566 37.732698,23.954467 37.732603,23.954258 37.732476,23.953007 37.733041,23.953207 37.733303,23.953261 37.733372),(23.953695 37.732771,23.954243 37.732524,23.954427 37.732635,23.954496 37.732702,23.954484 37.732757,23.954133 37.732921,23.954087 37.732859,23.953525 37.733122,23.953577 37.733192,23.953292 37.733326,23.953080 37.733050,23.953695 37.732771))

and

MULTIPOLYGON(((23.949134 37.734540,23.948811 37.734215,23.948775 37.734238,23.949077 37.734541,23.948689 37.734820,23.948809 37.734747,23.949134 37.734540)),((23.948536 37.734531,23.948449 37.734447,23.948414 37.734470,23.948472 37.734526,23.948420 37.734560,23.948449 37.734588,23.948536 37.734531)))

and simple polygons without outer rings....

I want to: query mysql parse data, pass them to js and draw them to Google Maps. Any help with that?

http://gis.stackexchange.com/questions/33650/how-to-extract-lat-long-from-wkt-data and http://stackoverflow.com/questions/12368586/how-to-handle-wkt-data-in-android — kozer, May 10 '13 at 13:34
And what problems did you have with those solutions? Or should we just mark it as a duplicate? — geocodezip, May 10 '13 at 13:37
In the first one reffers to multilines and points and not in polygons and multipolygons,and the second one is for java.These are the most close enough posts for my problem.Also I ve found an other post here in stackoverflow thats asking the same thing,but there is no answer at all...Im searching almost a week,this post is my last chance,there is nowhere a "clear" way for doing this.. :) — kozer, May 10 '13 at 15:01

score 10 · Accepted Answer · answered May 11 '13 at 22:29

Sounds to me like this really comes down to a regular expression question as you simply don't know how to correctly pull all the coordinate sets out of those strings, correct? Regular expressions are your friend here, do some googling about them to understand as I shall only demonstrate here.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>Some Title</title>
</head>
<body>
<div id="map_canvas" style="width:800px; height:600px;">
</div>
<?php

$polyString1 = 'POLYGON((23.953261 37.733372,23.953623 37.733202,23.953572 37.733132,23.954075 37.732896,23.954126 37.732966,23.954550 37.732767,23.954566 37.732698,23.954467 37.732603,23.954258 37.732476,23.953007 37.733041,23.953207 37.733303,23.953261 37.733372),(23.953695 37.732771,23.954243 37.732524,23.954427 37.732635,23.954496 37.732702,23.954484 37.732757,23.954133 37.732921,23.954087 37.732859,23.953525 37.733122,23.953577 37.733192,23.953292 37.733326,23.953080 37.733050,23.953695 37.732771))';

$polyString2 = 'MULTIPOLYGON(((23.949134 37.734540,23.948811 37.734215,23.948775 37.734238,23.949077 37.734541,23.948689 37.734820,23.948809 37.734747,23.949134 37.734540)),((23.948536 37.734531,23.948449 37.734447,23.948414 37.734470,23.948472 37.734526,23.948420 37.734560,23.948449 37.734588,23.948536 37.734531)))';

echo '<script type="text/javascript">';
//note the quote styles below, Important!
echo "var polys=['$polyString1','$polyString2'];";
echo '</script>';

?>
<script src="https://maps.googleapis.com/maps/api/js?v=3&sensor=false"></script>
<script type="text/javascript">

function parsePolyStrings(ps) {
    var i, j, lat, lng, tmp, tmpArr,
        arr = [],
        //match '(' and ')' plus contents between them which contain anything other than '(' or ')'
        m = ps.match(/\([^\(\)]+\)/g);
    if (m !== null) {
        for (i = 0; i < m.length; i++) {
            //match all numeric strings
            tmp = m[i].match(/-?\d+\.?\d*/g);
            if (tmp !== null) {
                //convert all the coordinate sets in tmp from strings to Numbers and convert to LatLng objects
                for (j = 0, tmpArr = []; j < tmp.length; j+=2) {
                    lat = Number(tmp[j]);
                    lng = Number(tmp[j + 1]);
                    tmpArr.push(new google.maps.LatLng(lat, lng));
                }
                arr.push(tmpArr);
            }
        }
    }
    //array of arrays of LatLng objects, or empty array
    return arr;
}

function init() {
    var i, tmp,
        myOptions = {
            zoom: 16,
            center: new google.maps.LatLng(23.9511, 37.7337),
            mapTypeId: google.maps.MapTypeId.ROADMAP
        },
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
    for (i = 0; i < polys.length; i++) {
        tmp = parsePolyStrings(polys[i]);
        if (tmp.length) {
            polys[i] = new google.maps.Polygon({
                paths : tmp,
                strokeColor : '#FF0000',
                strokeOpacity : 0.8,
                strokeWeight : 2,
                fillColor : '#FF0000',
                fillOpacity : 0.35
            });
            polys[i].setMap(map);
        }
    }
}

init();

</script>
</body>
</html>

Thank you very much!!With your help i manage to do what i wanted!!Thx!! :) — kozer, May 17 '13 at 08:18
Your latitude and longitude are reversed in the parsing (WKT is longitude then latitude). — geocodezip, Jul 03 '17 at 10:39
Is there any reason to do this client-side in Javascript rather than having PHP do the parsing on the server, other than keeping load off the server? I don't like pushing load off on the users, especially since there is no potential for caching. For fields that rarely change but are displayed frequently (common for things like political boundaries, a major use for multipolygons), the parsing could be done once and cached. Load here could be substantial if dealing with detailed datasets. — cazort, Apr 24 '19 at 15:45

score 10 · Answer 2 · answered Aug 07 '13 at 19:44

10

You can use the open-source libary Wicket to directly translate WKT strings into Google Maps API geometry objects. Here's a demo:

http://arthur-e.github.io/Wicket/sandbox-gmaps3.html

answered Aug 07 '13 at 19:44

Arthur

345
5
12

score 2 · Answer 3 · answered Nov 01 '15 at 09:41

@astupidname is almost correct, but something that will trip you up is that spatial databases use longitude, latitude, whereas mapping systems use latitude, longitude. Thus the WKT input is in x,y (longitude, latitude).

lat = Number(tmp[j]);
lng = Number(tmp[j + 1]);

should be

lng = Number(tmp[j]);
lat = Number(tmp[j + 1]);

score 1 · Answer 4 · answered Jan 13 '16 at 13:01

1

As well as Wicket (as mentioned by Arthur) there is also wellknown from Mapbox which handles such Well Known Text in clientside JavaScript.

answered Jan 13 '16 at 13:01

James Milner

879
2
11
16

Convert Well-known text (WKT) from MySQL to Google Maps polygons with PHP

4 Answers4

Linked