## PGCon 2009 is over… read submitted material online

From 19th to 21st May 2009 the PGCon 2009 took place in the University of Ottawa. Although I could not participate personally, I can attend virtually.

From the website http://www.pgcon.org/2009/ slides of presentations or material of workshops can be obtained. Man, I love the WWW!
Here is a small excerpt of what I found very interesting at first sight:

I can only hope to have the chance to participate next time in 2010.

## Reprojecting / snapping point-geometries onto a given line-geometry using PostGIS

Imagine you have a line — a street for example — and point measurements taken while being on that street. Due to certain circumstances — e.g. GPS-coorrdinates are slightly inaccurate –, the coordinates of your measurements are not exactly on the street.

We had this problem just recently and needed to reproject the points onto the said line, here is what we wanted to achieve:

The points from A-I are being projected onto the blue Line resulting in points A' - I'

Thanks to the PostGIS Mailing list, here is a way to do just that

``````SELECT ST_Line_Interpolate_Point(
line_to_project_onto.the_geom,
ST_Line_Locate_Point(
line_to_project_onto.the_geom,
points_to_project_onto_line.the_geom
)
)
FROM line_to_project_onto, points_to_project_onto_line;``````

Note: “line_to_project_onto” is a table containing one record: the line-geometry, “points_to_project_onto_line” is a table where all points are stored which should be snapped to the line, “points_projected_onto_line” — from the next example — is the table to store the reprojected points in. Here are short definitions from the PostGIS-SVN-Documentation

• ST_Line_Interpolate_Point – Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located (Documentation).
• ST_Line_Locate_Point – Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length (Documentation).

Let’s test it with 10.000 random points:

``````TRUNCATE points_to_project_onto_line;
TRUNCATE points_projected_onto_line;

INSERT INTO points_to_project_onto_line ( the_geom )
SELECT st_makePoint( random() * 10, random() * 10) FROM generate_series(1,10000);

INSERT INTO points_projected_onto_line ( the_geom )
SELECT 	line_interpolate_point(
line_to_project_onto.the_geom,
line_locate_point(
line_to_project_onto.the_geom,
points_to_project_onto_line.the_geom
)
)
FROM line_to_project_onto, points_to_project_onto_line;``````

10.000 reddish points snapped to the yellow line. The reprojected points are displayed as bigger green circles and it shows that there is no maverick.

Seems to work well. As usual with PostgreSQL / PostGIS ðŸ™‚

Changes:
09:10h Updated image captions

## Developing fun: ST_StarAtPoint for PostGIS

As christmas is coming closer, I want to share a small PostGIS-function that generates a star at a given point with some other specified characteristics. This function has not been built with a special use-case in mind, but just for fun. Yet, I hope someone finds this functionality interesting.

OK, so what is a star? When I am talking about a star I mean a polygon consisting of at least 3 spikes which are equally distributed along a circle. The following picture describes best what I want I understand as a “star”:

Anatomy of the stars created by the function

With Postgis I want to create stars that look like the the green polygon above. When one defines the center coordinate (the black spot in the center of the star), the number of spikes, and the length of both inner and outer radius (red lines), and an additional offset (in degrees, see the black arrow on top of the star), we are able to create all needed corners of the polygon (marke yellow above).

I will show two ways to compute everything we need, a readable, “processual” one and a more “geeky” one with only one line of SQL.

Let’s create an empty dummy function with all arguments we need, the logic will be inserted later:

``````
CREATE OR REPLACE FUNCTION ST_StarAtPoint(
IN point geometry,
IN number_of_spikes integer,
) RETURNS GEOMETRY AS
\$body\$
DECLARE
BEGIN
-- For the moment we'll always return a simple point geometry
RETURN 'POINT(0 0)'::geometry;
END;
\$body\$
LANGUAGE plpgsql;
``````

We are now able to call the above function. This does not make too much sense right now, but to test for typos it should do the trick:

``SELECT ST_AsText(ST_StarAtPoint('POINT(0 0)'::geometry, 1, 5, 8, 0));``

Now let’s add code to check if we have been called with valid parameters:

``````
END IF;
IF (number_of_spikes < 3) THEN
RAISE EXCEPTION 'A star must have at least three spikes.';
END IF;``````

Also, we will calculate the radians of the given degrees

``````angle = radians(360/number_of_corners);

For all the variables we need to have a valid declaration. We’ll add four more variables to the declarations, the declartion section now looks like this:

``````
-- the star geometry variable as WKT
star_geometry_wkt text := '';
-- a loop counter
i integer := 0;
-- the angle defined by 360Â° / number of spikes
angle numeric;
-- an optional "offset"
-- the baseline we will rotate around for the inner points
baseline_inner geometry;
-- the baseline we will rotate around for the outerpoints
baseline_outer geometry;
-- the point we rotate around
rotation_point geometry := 'POINT(0 0)'::geometry;
``````
``````
-- construction of the lines to rotate
baseline_outer = ST_RotateZ(
ST_MakeLine(rotation_point, ST_MakePoint(
ST_X(rotation_point),
baseline_inner = ST_RotateZ(
ST_MakeLine(rotation_point, ST_MakePoint(
ST_X(rotation_point),
``````

Now baseline_outer should contain a linestring from Point(0 0) to Point(0 radius_outer) (think twelve o’ clock) and slightly rotated counter-clockwise. The same is true for baseline_inner, but this line is roitated half the general angle agin counter-clockwise, so that inner points always lie exactly in the middle between two outer points.

We are now iterating through all needed spikes and add WKT-strings (not so elegant, I know) two our star_geometry_wkt-variable:

``````
WHILE (i < number_of_corners) LOOP
-- add point to polygon for outer-spike
-- note that we are adding the appropriate coordiantes from the input-geometry
star_geometry_wkt = star_geometry_wkt
|| (ST_X(ST_EndPoint(ST_RotateZ(baseline_outer, angle * i))) + ST_X(point))
|| ' '
|| ST_Y(ST_EndPoint(ST_RotateZ(baseline_outer, angle * i))) + ST_Y(point)
|| ',';
-- add point to polygon for inner-spike
-- note that we are adding the appropriate coordiantes from the input-geometry
star_geometry_wkt = star_geometry_wkt
|| (ST_X(ST_EndPoint(ST_RotateZ(baseline_inner, angle * i))) + ST_X(point))
|| ' '
|| ST_Y(ST_EndPoint(ST_RotateZ(baseline_inner, angle * i))) + ST_Y(point)
|| ',';
-- increment counter
i = i + 1;
END LOOP;
``````

Finally, let’s finish the WKT string and add the first point again so the linestring is closed.

``````
star_geometry_wkt = star_geometry_wkt ||  (ST_X(ST_EndPoint(baseline_outer)) + ST_X(point))  || ' ' ||  (ST_Y(ST_EndPoint(baseline_outer)) + ST_Y(point));
star_geometry_wkt = 'POLYGON((' || star_geometry_wkt || '))';
RETURN star_geometry_wkt::geometry;
``````

Here is the complete code of the star-function.

Of course it is possible to build the function with SQL only (Heres the source of ST_StarAtPoint with 1 SQL query only).

Now let’s see if any of the function works:

``````
CREATE TABLE star_test AS
SELECT
ST_StarAtPoint(ST_MakePoint(random()*400, random()*400),1, 1 + ii, iii, i) AS the_geom
FROM
generate_series(1,50) i,
generate_series(1,3) ii,
generate_series(5,10) iii;
-- add a primary key for QGIS
ALTER TABLE star_test ADD COLUMN id SERIAL;
ALTER TABLE star_test ADD PRIMARY KEY (id);
``````

When viewed in QGIS, one should see a star-field similar to this one:

Overview of the 900 stars inserted

Magnified view of the stars

A detail showing four stars we just created

## “Spatially clean” geometries with ST_SnapToGrid()

Recently I was faced with 2D-geometries that should be “spatially clean” but they weren’t. Postgres/Postgis to the rescue, the data could be cleaned and the problem was solved.

“Spatially clean” in the project meant:

• no geometry should overlap another geometry
• the geometries should touch at least one other geometry

Let’s have a look at an example. First we’ll create a table with polygons and insert 4 triangles that together build a rectangle:

``````
CREATE TABLE translate_polygons (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
-- the data
INSERT INTO translate_polygons (name, the_geom) VALUES (
'A',
'POLYGON((2 1, 0 3, 2 3, 2 1))'::geometry
), (
'B',
'POLYGON((0 3, 2 5, 2 3, 0 3))'::geometry
), (
'C',
'POLYGON((2 5, 4 3, 2 3, 2 5))'::geometry
), (
'D',
'POLYGON((4 3, 2 1, 2 3, 4 3))'::geometry
);``````

Those geometries are displayed in the following image.

the geometries used in the example

The data inserted matches the criterias noted above:

``````
-- no overlapping
SELECT t1.name || ' overlaps ' || t2.name || ' = ' || ST_Overlaps(t1.the_geom, t2.the_geom)::text AS overlap_test
FROM translate_polygons AS t1,
translate_polygons AS t2
WHERE t1.name <> t2.name
ORDER BY t1.name, t2.name;
``````
```     overlap_test
----------------------
A overlaps B = false
A overlaps C = false
A overlaps D = false
B overlaps A = false
B overlaps C = false
B overlaps D = false
C overlaps A = false
C overlaps B = false
C overlaps D = false
D overlaps A = false
D overlaps B = false
D overlaps C = false```
``````
-- in this case every triangle touches all other triangles:
SELECT t1.name || ' touches ' || t2.name || ' = ' || ST_Touches(t1.the_geom, t2.the_geom)::text AS touch_test
FROM translate_polygons AS t1,
translate_polygons AS t2
WHERE t1.name <> t2.name
ORDER BY t1.name, t2.name;
``````
```     touch_test
--------------------
A touches B = true
A touches C = true
A touches D = true
B touches A = true
B touches C = true
B touches D = true
C touches A = true
C touches B = true
C touches D = true
D touches A = true
D touches B = true
D touches C = true```

Let’s use ST_Translate to move two of the triangles:

``````
-- triangle to the lower left
-- move it to "southwest"
-- this produces a gap
UPDATE translate_polygons
SET the_geom = ST_Translate(the_geom, -0.4, -0.2)
WHERE name = 'A';
-- triangle to the upper right
-- move it to "southwest"
-- this produces overlapping geometries
UPDATE translate_polygons
SET the_geom = ST_Translate(the_geom, -0.1, -0.4)
WHERE name = 'C';``````

Now the geometries look like this:

The altered geometries with gaps and overlapping

We now have geometries that overlap and are not touching at least one other geometry:

```     touch_test
---------------------
A touches B = false
A touches C = false
A touches D = false
B touches A = false
B touches C = false
B touches D = true
C touches A = false
C touches B = false
C touches D = false
D touches A = false
D touches B = true
D touches C = false

overlap_test
----------------------
A overlaps B = false
A overlaps C = false
A overlaps D = false
B overlaps A = false
B overlaps C = true
B overlaps D = false
C overlaps A = false
C overlaps B = true
C overlaps D = true
D overlaps A = false
D overlaps B = false
D overlaps C = true```

How can we correct the geometries when we do not want to ST_Translate()-calls with (manually) adjusted Parameters? Postgis provides a handy function ST_SnapToGrid():

Function signatures:

1. geometry ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY);
2. geometry ST_SnapToGrid(geometry geomA, float sizeX, float sizeY);
3. geometry ST_SnapToGrid(geometry geomA, float size);
4. geometry ST_SnapToGrid(geometry geomA, geometry pointOrigin, float sizeX, float sizeY, float sizeZ, float sizeM);

ST_SnapToGrid â€” Snap all points of the input geometry to the grid defined by its origin and cell size. Remove consecutive points falling on the same cell, eventually returning NULL if output points are not enough to define a geometry of the given type. Collapsed geometries in a collection are stripped from it. Useful for reducing precision.

(From the Postgis-SVN-Manuals)

We can snap the geometries to a 1 by 1-grid when calling

``````
UPDATE translate_polygons
SET the_geom = ST_SnapToGrid(the_geom, 1)``````

Now the geometries look neat again:

the corrected geometries.

2008-12-15: fixed typos.

## A look at ST_Intersects(), ST_Overlaps() and ST_Intersection()

In this post I want to have a look at the behaviour of the OpenGIS functions ST_Intersects, ST_Overlaps and ST_Intersection. The database used is of course PostgreSQL which gives us all we need when used together with PostGIS.

First let’s create three tables with geographic data, one for points, lines and polygons each:

``````CREATE TABLE example_points (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
CREATE TABLE example_lines (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
CREATE TABLE example_polygons (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
``````

Now let’s populate those tables with data, we’ll insert 3 points, 3 lines and 4 polygons:

``````-- some points
INSERT INTO example_points (name, the_geom) VALUES (
'A',
'POINT(6 1)'::geometry
), (
'B',
'POINT(5.5 4.5)'::geometry
), (
'C',
'POINT(4.5 5.5)'::geometry
);
-- some lines
INSERT INTO example_lines (name, the_geom) VALUES (
'D',
'LINESTRING(3 1, 5 1)'::geometry
), (
'E',
'LINESTRING(3 7, 5 5)'::geometry
), (
'F',
'LINESTRING(5 6, 5 8)'::geometry
);
-- some polygons
INSERT INTO example_polygons (name, the_geom) VALUES (
'G',
'POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'::geometry
), (
'H',
'POLYGON((2.5 2.5, 2.5 4.5, 4.5 4.5, 4.5 2.5, 2.5 2.5))'::geometry
), (
'I',
'POLYGON((4 4, 4 6, 6 6, 6 4, 4 4))'::geometry
), (
'J',
'POLYGON((6 5, 6 7, 8 7, 8 5, 6 5))'::geometry
), (
'K',
'POLYGON((6 2, 6 4, 8 4, 8 2, 6 2))'::geometry
);
``````

For further examinations let’s create a view containing all the above geometries:

``````CREATE VIEW example_geometries AS (
(
SELECT name, the_geom
FROM example_points
) UNION ALL (
SELECT name, the_geom
FROM example_lines
) UNION ALL (
SELECT name, the_geom
FROM example_polygons
)
);``````

If you cannot imagine the relationship of these entities, here is a picture showing the locations of each geometry:

The geometries we just inserted

Now where do these geometries intersect? Let’s create a table which we can use to diplay the intersection geometries:

``````CREATE TABLE example_intersections_a AS (
SELECT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Intersects(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_a ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_a ADD PRIMARY KEY (id);``````

Here are the intersections:

The inserted geometries and their intersections (in green)

Note that we have 16 intersections of many kinds: Point with line, line with polygon, polygon with polygon, etc. All intersections are listed twice since if geometry X intersects with geometry Y, both X âˆ© Y and Y âˆ© X are counted.

ST_Intersection() actually gave us the geometries of the intersection, which can be of any geometry type, e.g. a polygon/polygon intersection can result in a point intersection, if the geometries share exactly one point (see the intersection of K with I).

Here is a tabular view of all the computation that took place to create the intersection table:

A visualisation of the computed iterations to find intersections

If you only want distinct intersections, use the DISTINCT SQL-Keyword:

``````CREATE TABLE example_intersections_b AS (
SELECT DISTINCT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Intersects(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_b ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_b ADD PRIMARY KEY (id);``````

In our case there are 7 distinct intersections. If you expected 8, thinking X âˆ© Y and Y âˆ© X would only be counted once, you’re only halfway right: since the intersection of C with I and C with E are the same, only one get counted of these.

If you are interested in intersection of the same dimension (see OGC SPEC s2.1.1.1 – dimension is 0 for points, 1 for lines, 2 for polygons) of the geometries you compare, use ST_Overlaps() in the WHERE-clause instead of ST_Intersects():

``````CREATE TABLE example_intersections_c as (
SELECT DISTINCT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Overlaps(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_c ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_c ADD PRIMARY KEY (id);``````