3

I have a MySQL database that contains a large number of polygons, one for each district on a map. They are all quite complicated and it's unrealistic to simply load them all at once. I've looked into using MarkerManager for Google Maps API v3 and this seems to be working as intended. However I'm not sure how to best go about querying my database for polygons that are in the viewport.

I'm thinking that it would be best to store the center of each polygon in a separate column as a simple lat,lng pair so that a query could be run on that - and then return the full polygon.

What is the best way to go about this?

SELECT * FROM polygons WHERE [the center of each polygon is within the bounds of the viewport]

Is this even possible? Am I going about this the wrong way?

And additionally, is there a simple script I can run on my data to return the center of each polygon?

1

6 Answers 6

4

You could migrate your database to PostgreSQL and use PostGIS.

Make sure you have GiST index covering you polygon shape column and use && operator to find the polygons intersecting with the viewport

SELECT * FROM polygons WHERE geometry && ST_MakeBox2D(ST_Point(x1, y1), ST_Point(x2,y2));
1
3

Using this example with Spatial Extensions for MySQL:

SET @bounds = 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';

SELECT *
FROM polygons
WHERE MBRContains(GeomFromText(@bounds), geom);
1

You could use GeoServer (or MapServer) to display your polygons as a WMS layer using OpenLayers to display it over the Google Maps Basemap - then you can use WMS GetFeatureInfo or a WFS GetFeature request to query the database. As jkj says you probably want to use a proper spatial data base like postgis too.

1

You can use MySQL though you need to use php to generate xml for it to be passed though v3 api. A good tutorial - your need to modify it for your requirements http://code.google.com/apis/maps/articles/phpsqlajax_v3.html (though again recommend postgres for gis functions is faster)

1

is there a simple script I can run on my data to return the center of each polygon?

http://mayastation.typepad.com/maya-station/2009/11/where-is-the-center-of-a-polygon.html

to get the N,E,S,W-bounds, use following:

google.maps.event.addListener(map, 'tilesloaded', function() {
    var bounds = map.getBounds(),
        boundsNeLatLng = bounds.getNorthEast(),
        boundsSwLatLng = bounds.getSouthWest(),
        zoom = map.getZoom(),
        boundaries = {
            latN: boundsNeLatLng.lat(),
            latS: boundsSwLatLng.lat(),
            lonE: boundsNeLatLng.lng(),
            lonW: boundsSwLatLng.lng()
        };
        /*
        …now using boundaries.latN, etc. here via AJAX
        */
});

to get all points in that viewport via MySQL:

-- and within MySQL:
SELECT `id` FROM `polylines`
WHERE :latN > `latitude` AND `latitude` > :latS
AND :lonE > `longitude` AND `longitude` > :lonW
0

You could load your data into fusion tables and overlay the fusionlayer over the basemap. There are restrictions, that change as they improve the product so best to check http://www.google.com/fusiontables/.

Is full accuracy of you polygon settings required, perhaps you could simplify before any migrations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.