How do I get all geometries within area using Google Maps API info?

Question

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?

Answer 1

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);

Answer 2

Sometimes, it's OK to lie to PostGIS.

I'll assume that, since you're working with Google Maps, you don't need survey-level accuracy. In that case, I would just tell PostGIS Google Maps coordinates are in EPSG:4269. The differences between EPSG:4269 and the EPSG:4326 coordinates provided by Google are, for most applications, negligeable. (See https://gis.stackexchange.com/a/170854/18189 for a discussion.)

Your query, then, is just:

SELECT * FROM my_table WHERE ST_Intersects(geom, ST_MakeEnvelope(lngW, latS, lngE, latN, 4269), 4269))