Date Archives

April 2019

How to query geo-referenced data in PostGIS

PostGIS is an open-source extension to PostgreSQL that adds support for geo-referenced queries. PostGIS adds a geometry column to your table that indicates the shape and geolocation of your row.

here the geometry column is called ‘geom’

Here are three of the most common queries that I use.

  1. Display the geometry as text

    select ST_AsEWKT(geometry_column) from table;
  2. Select rows from table inside given bounding box

    # Here, (xmin, ymin, xmax, ymax) are the edges of your bounding box
    SELECT * FROM table_name WHERE geometry_column && ST_MakeEnvelope(xmin, ymin, xmax, ymax);
    # Example
    SELECT * FROM rain_gauges WHERE geom && ST_MakeEnvelope(1503535, -174488.9, 1603535.4, -154488.8);
  3. Change the projection of a table

    # This will change the srid of the table table_name table to 2295 from whatever it was before
    SELECT UpdateGeometrySRID('table_name','geometry_column,2295);

    For more info on projections & SRIDs :