[GIS] How to get all geometries within area using Google Maps API info

google mapspostgis

I'm using Google Maps Javascript API. My google.maps.Map object is stored in variable GLOBAL_map. I have the following code:

var bounds= GLOBAL_map.getBounds();
var sw=     bounds.getSouthWest();
var ne=     bounds.getNorthEast();
var lngW=   sw.lng();
var latS=   sw.lat();
var lngE=   ne.lng();
var latN=   ne.lat();
var obj=    {
    lngW: lngW,
    latS: latS,
    lngE: lngE,
    latN: latN
};
console.log(obj);

Here's what obj can look like:

{
  latN: 47.66808688012437,
  latS: 47.637501470799144,
  lngE: -117.40694335937502,
  lngW: -117.43440917968752,
}

I have PostGIS table with a geometry column of multipolygons, SRID 4269. How can I use the info from the Google Maps object to make a PostGIS query that returns rows within the map's north, east, south, and west points?

Best Answer

I have PostGIS table with a geometry column of multipolygons, SRID 4269.

Google maps returns all results in SRID 4326. So you need to first either.

  • ST_Transform() your input coordinates to 4326 on the table.
  • Create an index on the result of the ST_Transform.

Let's assume the latter.

CREATE INDEX ON myTable USING GIST (ST_Transform(myGeom, 4326));
VACUUM ANALYZE myTable;

Then what you can do is, is build a bounding box with ST_MakeEnvelope, and run the query. The end query will look something like,

SELECT *
FROM myTable
WHERE ST_MakeEnvelope(xmin,ymin,xmax,ymax) && ST_Transform(myTable.geom,4326);
Related Question