Query polygons within a map-view boundary in PostGIS

Question

I am new to PostGIS. I've got a table which contains boundaries of area in Polygon type. I would like to display the polygon on Google map. Each time, when google map view boundary is changed, the application should query PostGIS for any polygons in that map view. Can someone guide me to start?

ST_Intersects(viewbbox, geom) where geom is the geometry column in your polygons table is the spatial function you are looking for. However, you will need to give more information about how you are displaying this, querying this, etc, from Google Maps. — John Powell
Thanks! When I display, I'll simply draw Polygons using the Lat/Lng pairs. So, the input to my query is the lat/lng of NE and SE coordinates. — JMC

John Powell John Powell · Accepted Answer · 2014-06-29T15:11:00

Select * from Polygons where ST_Intersects(
    ST_MakeBox2D(ST_MakePoint(lon1, lat1), ST_MakePoint(lon2, lat2)),
    geom);

assuming that you have a table called Polygons and that your geometry field is called geom. You also need to make sure that you have a spatial index on the geometry field with:

Create index ix_spatial_geom on Polygons using gist (geom);

If you are planning on loading data from Postgis into google maps, have a look at the AS_GeoJSON function in Postgis, which allows you to create GeoJSON directly from Postgis, which you can then load into Google Maps directly, see this example: load GeoJSON in Google Maps

EDIT:

You can set the SRID when you import the data using the -s switch. It is highly advisable to explicitly set the SRID on a column either when you create the column or by running an update with UpdateGeometrySRID afterwards, as this helps with both enforcing integrity, and enable conversion from one coordinate system to another.

Once you have you data in a specified coordinate system, you can also use ST_Transfrom to convert from one coordinate system to another, on the fly, such as from 4326 to 3857 (the projected metres of Google Maps tiles).

Query polygons within a map-view boundary in PostGIS

2 Answers