Introduction

This page is used as a kind of cookbook with tips and trics in PostGIS. The tips and trics are examples encountered during work for the project Marine Indicators. PostGIS2.2 is used. With respect to 1.5 this is a huge difference in functionality.

Update a column based on location of points if within a polygon

The attribute onland of point table eea2012.ices_station has to be updated with True if within a polygon of the table geodata.europe.

Update attribute based on spatial query
update eea2012.ices_station set onland = true
where tblstationid IN (
select tblstationid
from eea2012.ices_station ,geodata.dis_europe
where st_within(the_geom,geom) = true)

Selecting data based on certain distance from a geometry

Imagine 2 geometries in WGS84 (EPSG:4326) (boundaries of countries and points) and it is desirable to select all geometries within 1 km from land boundaries. Now we face the challenge to use a metric distance with a spatial reference in WGS84 (so degrees).

update attribute based on distance within distance of 1 km
update eea2012.ices_station set withinonekm = true
where tblstationid IN (select tblstationid
from geodata.dis_europe, eea2012.ices_station
where st_dwithin(ST_GeographyFromText(st_asEWKT(geom)),ST_GeographyFromText(st_asEWKT(the_geom)),1000)
and onland IS False)

Although the above is more correct, it is a very expensive query to perform. An alternative approach is to convert the geometry data type to geography.

update attribute distance base on distince of 1 km = 0.00899038 degrees at 50 degrees latitude
alter table eea2012.ices_station add column the_geography geography(POINT,4326)
UPDATE eea2012.ices_station SET the_geography = ST_GeographyFromText(st_asEWKT(the_geom))

alter table geodata.dis_europe add column the_geogph geography(MULTIPOLYGON,4326)
UPDATE geodata.dis_europe SET the_geogph = ST_GeographyFromText(st_asEWKT(geom))

update eea2012.ices_station set withinonekm = true
where tblstationid IN (select tblstationid
from geodata.dis_europe, eea2012.ices_station
where st_dwithin(the_geogph,the_geography,1000)
and onland IS False)
  • No labels

Disclaimer