Carry out the following steps to create the functions:
- Create a new schema named chp12 to store the data for all the recipes in this chapter using the following command:
postgis_cookbook=# create schema chp12;
- The implementation of Rand requires the creation of a PLPGSQL function that receives the radius parameter, which defines the maximum distance, and the geometry the_geom to be altered.
The ST_Project function will move the point to a given distance and angle from its original location. In order to simplify the expression, we will use polar noise generation. Execute the following SQL command:
postgis_cookbook=# CREATE OR REPLACE
FUNCTION chp12.rand(radius numeric, the_geom geometry)
returns geometry as $$ BEGIN return st_Project(the_geom, random()*radius,
radians(random()*360)); END; $$ LANGUAGE plpgsql;
- The implementation of N-Rand requires the n parameter, the number of trials to look for the longest distance from the original point, and the radius parameter, which defines the maximum distance, and the geometry the_geom to be altered. Execute the following SQL command:
postgis_cookbook=# CREATE OR REPLACE FUNCTION chp12.nrand(n integer,
radius numeric, the_geom geometry)
returns geometry as $$ DECLARE tempdist numeric; maxdist numeric; BEGIN tempdist := 0; maxdist := 0; FOR i IN 1..n LOOP tempdist := random()*radius; IF maxdist < tempdist THEN maxdist := tempdist; END IF; END LOOP; return st_Project(the_geom,maxdist, radians(random()*360)); END; $$ LANGUAGE plpgsql;
- The implementation of Pinwheel requires the n parameter, the number of trials to look for the longest distance from the original point, and the radius parameter, which defines the maximum distance, and the geometry the_geom to be altered. Execute the following SQL command:
postgis_cookbook=# CREATE OR REPLACE FUNCTION chp12.pinwheel
(theta numeric, radius numeric, the_geom geometry)
returns geometry as $$ DECLARE angle numeric; BEGIN angle = random()*360; return st_Project(the_geom,mod(
CAST(angle as integer), theta)/theta*radius, radians(angle)); END; $$ LANGUAGE plpgsql;
- Now we will replicate part of the steps in Chapter 3, Working with Vector Data – The Basics, but for the schema chp12. Create the chp12.rk_track_points table in PostgreSQL by executing the following command lines:
postgis_cookbook=# CREATE TABLE chp12.rk_track_points
(
fid serial NOT NULL,
the_geom geometry(Point,4326),
ele double precision,
"time" timestamp with time zone,
CONSTRAINT activities_pk PRIMARY KEY (fid)
);
- As an example, let's use the nrand function to create a trigger for all the new points inserted in the rk_track_points table. In order to simulate this, we will create a new table that we will use.
This function will return a new geometry:
CREATE OR REPLACE FUNCTION __trigger_rk_track_points_before_insert(
) RETURNS trigger AS $__$
DECLARE
maxdist integer;
n integer;
BEGIN
maxdist = 500;
n = 4;
NEW.the_geom = chp12.nrand(n, maxdist, NEW.the_geom);
RETURN NEW;
END;
$__$ LANGUAGE plpgsql;
CREATE TRIGGER rk_track_points_before_insert
BEFORE INSERT ON chp12.rk_track_points FOR EACH ROW
EXECUTE PROCEDURE __trigger_rk_track_points_before_insert();
- Create the following script to import all of the .gpx files in the chp12.rk_track_points table using the GDAL ogr2ogr command.
The following is the Linux version (name it working/chp03/import_gpx.sh):
#!/bin/bash
for f in `find runkeeper_gpx -name \*.gpx -printf "%f\n"`
do
echo "Importing gpx file $f to chp12.rk_track_points
PostGIS table..." #, ${f%.*}"
ogr2ogr -append -update -f PostgreSQL
PG:"dbname='postgis_cookbook' user='me' password='mypassword'"
runkeeper_gpx/$f -nln chp12.rk_track_points
-sql "SELECT ele, time FROM track_points"
done
The following is the Windows version (name it working/chp03/import_gpx.bat):
@echo off
for %%I in (runkeeper_gpx\*.gpx*) do (
echo Importing gpx file %%~nxI to chp12.rk_track_points
PostGIS table...
ogr2ogr -append -update -f PostgreSQL
PG:"dbname='postgis_cookbook' user='me' password='mypassword'"
runkeeper_gpx/%%~nxI -nln chp12.rk_track_points
-sql "SELECT ele, time FROM track_points"
)
- In Linux, don't forget to assign an execution permission to it before running. Run the following script:
$ chmod 775 import_gpx.sh
$ ./import_gpx.sh
Importing gpx file 2012-02-26-0930.gpx to chp12.rk_track_points
PostGIS table...
Importing gpx file 2012-02-29-1235.gpx to chp12.rk_track_points
PostGIS table...
...
Importing gpx file 2011-04-15-1906.gpx to chp12.rk_track_points
PostGIS table...
In Windows, double-click on the .bat file, or run it from the command prompt using the following command:
> import_gpx.bat
- Once the insertion command is invoked, the trigger call the nrand function, alter the incoming geometry in the row and store the new version of the data. If we compare the first 10 values of the original table chp03.rk_track_points with the chp12.rk_track_points, it can be seen that they are slightly different, due to the added noise. Execute the following query in order to see the results:
select ST_ASTEXT(rk.the_geom), ST_ASTEXT(rk2.the_geom)
from chp03.rk_track_points as rk, chp12.rk_track_points as rk2
where rk.fid = rk2.fid
limit 10;
The results of the query are as follows:
- In order to evaluate the impact of the noise in the data, we will create two tables to store the obfuscated data with different noise levels: 500 m and 1 km. We will use the previously defined function rand. Execute the following SQL commands to create the tables:
CREATE TABLE chp12.rk_points_rand_500 AS (
SELECT chp12.rand(500, the_geom)
FROM chp12.rk_track_points
);
CREATE TABLE chp12.rk_points_rand_1000 AS (
SELECT chp12.rand(1000, the_geom)
FROM chp12.rk_track_points
);
- Load the tables in QGIS or your favorite Desktop GIS. The following figure shows the comparison of the original data and the obfuscated points by 500 m and 1 km:
