Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Are you using the spatial extensions? –  Mike T Mar 13 '11 at 23:22
add comment

6 Answers

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);
share|improve this answer
add comment

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));
share|improve this answer
2  
Nobody should really use MySQL anyway: arstechnica.com/civis/viewtopic.php?f=20&t=92525 –  jkj Mar 13 '11 at 16:51
add comment

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.

share|improve this answer
add comment

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)

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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