Developing Applications With Postgis Mapserver User Meeting: Presented by
Developing Applications With Postgis Mapserver User Meeting: Presented by
Developing Applications With Postgis Mapserver User Meeting: Presented by
Presented By:
June 7, 2003
1 GOALS
After completing this workshop, you will have gained the skills
to:
create a PostGIS database
create a geometry table with indexes to use with Mapserver
insert and manipulate PostGIS geometries using SQL
display the contents of a PostGIS table using Mapserver
use spatial operators and functions to select specific
geometries from a PostGIS table and display them in
Mapserver
use SQL joins to select data from multiple PostGIS tables for
display in Mapserver
-2-
-3-
The SRID number references a row in the spatial_ref_sys table, and all
the geometries in one column are required to have the same SRID.
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
-4-
-5-
-6-
LAYER
CONNECTIONTYPE postgis
NAME "test"
CONNECTION "user=theuser password=thepass dbname=thedb
host=theserver"
DATA "geom FROM test"
STATUS ON
TYPE LINE
CLASS
COLOR 0 0 0
END
END
The example map-file provided includes a similar layer definition; you should
only need to edit the connection string in order to get it to work. In addition,
make sure you set your map extents to default to reasonable values in order to
see the data you inserted.
Note that point objects won't be displayed if your layer is defined to be of type
"line", and polygons also will not be filled you can change the type to point or
polygon in order to see this. Normally, you would define a table to store only one
type of geometry you can do this with the "AddGeometryColumn" function.
-7-
-8-
-9-
- 10 -
10 A SPATIAL FUNCTION
HOW FAR IS THE NEAREST HOSPITAL?
The previous query does a simple mathematical calculation to generate values for
the new table. This time, we will use a spatial function to calculate the new table.
Let's color-code the roads based on how far they are from the nearest hospital.
Here is the data definition that will do it:
DATA "the_geom from
(SELECT
roads.the_geom AS the_geom,
roads.gid AS gid,
min(distance(roads.the_geom, hospitals.the_geom)) AS dist
FROM roads, hospitals
GROUP BY roads.oid, roads.the_geom)
as foo using srid=-1 using unique gid"
The "distance()" function takes two geometries as arguments and calculates the
distance between them. The query calculates the distance between each road
segment and all of the hospitals, and keeps the minimum distance for each road
segment calling it "dist". The "using" clauses are again very simple, but
required.
- 11 -
11 AN SQL JOIN
ROAD NETWORK LABELING
Now we are going to show how to do a simple join to display values from multiple
tables. First we need to imagine that for some reason (probably normalization)
some of the data in our roads table is actually stored in another table the
roads_attr table. First we need to create this table:
CREATE TABLE roads_attr AS
SELECT gid, street, fromleft, toleft, fromright, toright,
type
FROM roads;
CREATE INDEX roads_attr_gid_idx ON roads_attr( gid );
The roads_attr table now has all of the attribute information for each road
feature, while the roads table has only the geometry and the gid (we will imagine).
In order to display labels on our roads, we need to join the roads table to the
roads_attr table. Here is the DATA definition that does it:
DATA "the_geom from
(SELECT roads.gid as gid,
roads.the_geom as the_geom,
roads_attr.street
FROM roads LEFT JOIN roads_attr ON roads.gid =
roads_attr.gid)
as foo using SRID=-1, using unique gid"
This statement gets the labels - "street" - from the roads_attr table, and the
geometries - "the_geom" - from the roads table. Mapserver uses the geometry to
decide where to place the labels. This definition is used in the "road_labels" layer
of the example mapfile.
Notice that each road segment is labeled, not each road. Labeling all of the
segments often causes unnecessary repetitions, and the repeated labels often
cause some of the roads not to be labeled at all (due to label collisions with other,
sometimes repeated, labels). The solution follows.
- 12 -
12 SOME TRICKINESS
NICER ROAD NETWORK LABELING
This last example uses neither a "spatial" function, nor a "normal" function. The
"collect" function provided in PostGIS allows you to clump a bunch of geometries
together into a single geometry. For example, if you have a bunch of lines, you
can use "collect" to group them together into a multi-line. This is similar to the
built-in SQL "sum" function for numeric types. Here is the data definition for
nicer road labels:
DATA "the_geom from
(SELECT street, collect(the_geom) as the_geom
FROM roads
GROUP BY street)
as foo using SRID=-1 using unique street"
This collects all of the geometries with the same road name into a single
geometry, ensuring that it is only labeled once. This DATA definition is used in
the "road_labels_grouped" layer of the example mapfile. We are ignoring the
roads_attr table for this example because it would over-complicate things (but it
could still be done this is left as "an exercise for the reader").
- 13 -
- 14 -