[GIS] How to store Google Maps DrawingManager Rectangle overlay in PostgreSQL Postgis GEOMETRY(GEOMETRY, 4326)

google mapsmap-drawingpostgispostgresql

What is the correct way to store a Google Maps DrawingManager rectangle overlay? The API basically provides me with NE & SW lat/long coordinates. Should I use BOX()? Is it possible to create a BOX() from a GeoJSON object?

Best Answer

At some point you'll have to convert from Google native overlays to WKT. You can either do it in your frontend, using for example Wicket.js or in your backend, using postgis's ST_FromGeoJson. Keep in mind that there's no geoJSON spec for rectangles. They're just polygons.

If you use Wicket.js you will be able to ingest a WKT BOX to generate a google.maps.Rectangle 

var wicketReader = new Wkt.Wkt();
var rect=wicketReader.read( 'BOX(-71 -21,-70 -20)').toObject();
rect instanceof google.maps.Rectangle;
> true

However, for the sake of consistency, when you export it as WKT, you'll get the equivalent polygon:

var wicketReader = new Wkt.Wkt();
var newRectangle = new google.maps.Rectangle(
    {bounds: new google.maps.LatLngBounds(
        new google.maps.LatLng(-21,-71), 
        new google.maps.LatLng(-20,-70)
    )}
);
wicketReader.fromObject(newRectangle).toString();
> "POLYGON((-71 -20,-70 -20,-70 -21,-71 -21,-71 -20))"

This is because you could also want to export it as geoJSON and it wouldn't make sense to output a different object

wicketReader.fromObject(newRectangle).toJson();
> {type:"Polygon", coordinates:[
    [
      [-71,-20],
      [-70,-20],
      [-70,-21],
      [-71,-21],
      [-71,-20]
    ]
 ]}

So, you see, this wont work to export your Rectangles to boxes. But nothing stops you from extending the google.maps.Rectangle class

google.maps.Rectangle.prototype.toBox = function () {
    var bounds = this.getBounds();
    var SW = bounds.getSouthWest();
    var NE = bounds.getNorthEast();
    return ['BOX(', SW.lng(), ' ', SW.lat(), ' , ', NE.lng(), ' ', NE.lat(), ')'].join('');
};

So you can do:

newRectangle.toBox();
> "BOX(-71 -21,-70 -20)"

Keep in mind that in PostGIS, the Box2D type is a geometry by itself, as opposed to WKT that is just text. This means BOX2D is treated differently. For example:

INSERT INTO myrectangles (the_geom) values (box2d('BOX(-71 -21,-70 -20)');

Stores a geometry. whereas

SELECT box2d(the_geom) from myrectangles;

Yields a string.

If you used ST_GeomFromText on a BOX string, it would result in an error.

If you used ST_AsText in a BOX geometry, it would yield the equivalent polygon.

This means you'll have to adapt your logic to apply the Box2D function both when storing and retrieving a box geometry.

Disclaimer: I'm a humble contributor to Wicket.

Related Question