Search code examples
sqlgeospatialpostgisspatial-indexspatial-query

PostGis insert Rectangle

Hi all i have a question: how i can insert a box (rectangle) with Ne_latitude, NE_longitude, SW_latitude, SW_longitude inside a postgis database and build an INDEX on top for retrieving the intersection between the different boxes?

Thanks in advance

Solution

  • Use ST_MakeEnvelope to build a rectangle geometry. It can be used to retrieve the intersection of bounding boxes from some_table (with geometry column geom) using the && overlaps operator:

    SELECT *
FROM some_table
WHERE geom && ST_MakeEnvelope(SW_longitude, SW_latitude, NE_longitude, NE_latitude, 4326);

    To insert the rectangle into some_table:

    INSERT INTO some_table (geom)
VALUES (ST_MakeEnvelope(SW_longitude, SW_latitude, NE_longitude, NE_latitude, 4326));

    and in case some_table does not yet have a spatial index on the geom column, create it:

    CREATE INDEX ON some_table USING gist (geog);