Sie sollten nun viele Linien sehen, welche Straßen repräsentieren. All diese Linien sind Bestandteil des von Ihnen soeben geladenen Vektorlayers um eine einfache Karte zu erstellen.
Refer back to the image showing the interface layout and check that you remember the names and functions of the screen elements.
Es sollten fünf Layer auf Ihrer Karte liegen.
Alle vorhandenen Vektorlayer sollten in die Karte geladen werden, obwohl dies wahrscheinlich nicht besonder nett aussehen wird (wir werden uns später um das Beheben der unglücklichen Farbwahl kümmern).
Einstweilen genügt es, nur den water Layer zu verändern. Unterhalb ist ein Beispiel angeführt, welches je nach den von Ihnen gewählten Farben von dieser Abbildung abweichen kann.
Bemerkung
Falls Sie nur an einem Layer gleichzeitig arbeiten möchten und Sie die anderen Layer ablenken, können Sie diese mithilfe eines Klicks auf die Checkbox neben dem Namen des Layers ausblenden. Wenn die Checkbox leer ist, ist dieser Layer versteckt.
Ihre Karte sollte nun folgendermaßen aussehen:
Falls Sie ein Benutzer auf Einsteigerniveau sind, können Sie hier stoppen.
Benutzen Sie die oben genannte Methode um die Farben und Stile für die übrigen Layer anzupassen.
Versuchen Sie möglichst den Objekten entsprechende Farben zu verwenden. So sollte beispielsweise eine Straße nicht Rot oder Blau sein, sondern eher Grau oder Schwarz.
Versuchen Sie bei Polygonen auch unteschiedliche Einstellungen für Fill Style und Border Style .
Passen Sie den buildings Layer nach Ihrem Ermessen an, aber bedenken Sie dabei, dass es möglichst leicht sein sollte, unterschiedliche Layer unterscheiden zu können.
Im Folgenden ein Beispiel:
Um das notwendige Symbol zu erstellen, benötigen Sie zwei Symbollayer:
Der unterste Symbollayer besteht aus einer breiten, durchgängigen gelben Linie. Darüber liegt eine etwas dünnere, ebenfalls duchgängige graue Linie.
Falls Ihr Symbollayer zwar der obigen Abbildung entspricht, Sie aber ein anders Endergebnis erhalten, sollten Sie überprüfen, ob Ihre Symbollayer ähnlich dieser Abblidung sind:
Inzwischen sollte Ihre Karte folgendermaßen aussehen:
Passen Sie Ihre Symbollevel entsprechend den folgenden Werten an:
Experimentieren Sie mit verschiedenen Werten um unterschiedleiche Ergebnisse zu erhalten.
Öffnen Sie Ihre ursprüngliche Karte abermals bevor Sie mit der nächsten Übung fortsetzen.
Die Spalte, welche sich als beste für die Anzeige als Beschriftung eignet, ist jene mit dem Titel NAME. Dies rührt daher, dass alle Einträge in dieser Spalte einmalig sind und es unwahrscheinlich ist, dass ein Eintrag einen NULL Wert aufweist. Seien Sie unbesorgt, falls Ihre verwendeten Daten dennoch NULL Werte beinhalten, da zumindest der Großteil der Einträge Namen besitzt.
Your map should now show the marker points and the labels should be offset by
2.0 mm
: The style of the markers and labels should allow both to be
clearly visible on the map:
One possible solution has this final product:
To arrive at this result:
Use a font size of 10
, a Label
distance of 1,5 mm
, Symbol width and
Symbol size of 3.0 mm
.
In addition, this example uses the Wrap label on character option:
Enter a space
in this field and click Apply to achieve the
same effect. In our case, some of the place names are very long, resulting in
names with multiple lines which is not very user friendly. You might find this
setting to be more appropriate for your map.
Still in edit mode, set the FONT_SIZE
values to whatever you prefer.
The example uses 16
for towns, 14
for suburbs, 12
for
localities and 10
for hamlets.
Remember to save changes and exit edit mode.
Return to the Text formatting options for the places
layer and select FONT_SIZE
in the Attribute field of the
font size data override dropdown:
Your results, if using the above values, should be this:
Use the same method as in the first exercise of the lesson to get rid of the borders:
The settings you used might not be the same, but with the values
Classes = 6
and Mode = Natural Breaks
(Jenks) (and using the same colors, of course), the map will look like this:
The symbology doesn’t matter, but the results should look more or less like this:
The exact shape doesn’t matter, but you should be getting a hole in the middle of your feature, like this one:
1
as the source of your
attributes (click on its entry in the dialog, then click the Take
attributes from selected feature button):Bemerkung
1
. Just choose the
feature which has an OGC_FID.Bemerkung
Using the Merge Attributes of Selected Features tool will keep the geometries distinct, but give them the same attributes.
For the TYPE, there is obviously a limited amount of types that a road can be, and if you check the attribute table for this layer, you’ll see that they are predefined.
Set the widget to Value Map and click Load Data from Layer.
Select roads in the Label dropdown and highway for both the Value and Description options:
Click Ok three times.
If you use the Identify tool on a street now while edit mode is active, the dialog you get should look like this:
For the purpose of this exercise, the OSM layers which we are interested in are
multipolygons
and lines
. The multipolygons
layer contains
the data we need in order to produce the houses
, schools
and
restaurants
layers. The lines
layer contains the roads dataset.
The Query Builder is found in the layer properties:
Using the Query Builder against the multipolygons
layer,
create the following queries for the houses
, schools
,
restaurants
and residential
layers:
Once you have entered each query, click OK. You’ll see that the map
updates to show only the data you have selected. Since you need to use again
the multipolygons
data from the OSM dataset, at this point, you can use one of
the following methods:
osm_data.osm
, ORBemerkung
Although OSM’s building
field has a house
value, the
coverage in your area - as in ours - may not be complete. In our test
region, it is therefore more accurate to exclude all buildings which are
defined as anything other than house
. You may decide to
simply include buildings which are defined as house
and all other
values that have not a clear meaning like yes
.
To create the roads
layer, build this query against OSM’s lines
layer:
You should end up with a map which looks similar to the following:
Your buffer dialog should look like this:
The Buffer distance is 1000
meters (i.e., 1
kilometer).
The Segments to approximate value is set to 20
. This is
optional, but it’s recommended, because it makes the output buffers look
smoother. Compare this:
To this:
The first image shows the buffer with the Segments to approximate
value set to 5
and the second shows the value set to 20
. In our
example, the difference is subtle, but you can see that the buffer’s edges are
smoother with the higher value.
To create the new houses_restaurants_500m
layer, we go through a two step
process:
First, create a buffer of 500m around the restaurants and add the layer to the map:
Next, select buildings within that buffer area:
Now save that selection to our new houses_restaurants_500m
layer:
Your map should now show only those buildings which are within 50m of a road, 1km of a school and 500m of a restaurant:
Set your Raster calculator dialog up like this:
For the 5 degree version, replace the 2
in the expression and file
name with 5
.
Your results:
2 degrees:
5 degrees:
"suitable" = 1
.When viewed over the original raster, the areas should overlap perfectly:
You may notice that some of the buildings in your new_solution
layer have
been “sliced” by the Intersect tool. This shows that only part of the
building - and therefore only part of the property - lies on suitable terrain.
We can therefore sensibly eliminate those buildings from our dataset
At the moment, your analysis should look something like this:
Consider a circular area, continuous for 100 meters in all directions.
If it is greater than 100 meters in radius, then subtracting 100 meters from its size (from all directions) will result in a part of it being left in the middle.
Therefore, you can run an interior buffer of 100 meters on your existing suitable_terrain vector layer. In the output of the buffer function, whatever remains of the original layer will represent areas where there is suitable terrain for 100 meters beyond.
To demonstrate:
Go to Vector ‣ Geoprocessing Tools ‣ Buffer(s) to open the Buffer(s) dialog.
Set it up like this:
Use the suitable_terrain layer with 10
segments and a
buffer distance of -100
. (The distance is automatically in meters
because your map is using a projected CRS.)
Save the output in exercise_data/residential_development/
as
suitable_terrain_continuous100m.shp
.
If necessary, move the new layer above your original suitable_terrain
layer.
Your results will look like something like this:
Now use the Select by Location tool (Vector ‣ Research Tools ‣ Select by location).
Set up like this:
Select features in new_solution that intersect features in suitable_terrain_continuous100m.shp.
This is the result:
The yellow buildings are selected. Although some of the buildings fall partly
outside the new suitable_terrain_continuous100m
layer, they lie well
within the original suitable_terrain
layer and therefore meet all of our
requirements.
exercise_data/residential_development/
as
final_answer.shp
.Your map should look like this (you may need to re-order the layers):
Use the same approach as before to add the new server and the appropriate layer as hosted on that server:
If you zoom into the Swellendam area, you’ll notice that this dataset has a low resolution:
Therefore, it’s better not to use this data for the current map. The Blue Marble data is more suitable at global or national scales.
You may notice that many WMS servers are not always available. Sometimes this
is temporary, sometimes it is permanent. An example of a WMS server that worked
at the time of writing is the World Mineral Deposits WMS at
http://apps1.gdr.nrcan.gc.ca/cgi-bin/worldmin_en-ca_ows
. It does not
require fees or have access constraints, and it is global. Therefore, it does
satisfy the requirements. Keep in mind, however, that this is merely an
example. There are many other WMS servers to choose from.
For our theoretical address table, we might want to store the following properties:
House Number
Street Name
Suburb Name
City Name
Postcode
Country
When creating the table to represent an address object, we would create columns to represent each of these properties and we would name them with SQL-compliant and possibly shortened names:
house_number
street_name
suburb
city
postcode
country
The major problem with the people table is that there is a single address field which contains a person’s entire address. Thinking about our theoretical address table earlier in this lesson, we know that an address is made up of many different properties. By storing all these properties in one field, we make it much harder to update and query our data. We therefore need to split the address field into the various properties. This would give us a table which has the following structure:
id | name | house_no | street_name | city | phone_no
--+---------------+----------+----------------+------------+-----------------
1 | Tim Sutton | 3 | Buirski Plein | Swellendam | 071 123 123
2 | Horst Duester | 4 | Avenue du Roix | Geneva | 072 121 122
Bemerkung
In the next section, you will learn about Foreign Key relationships which could be used in this example to further improve our database’s structure.
Our people table currently looks like this:
id | name | house_no | street_id | phone_no
---+--------------+----------+-----------+-------------
1 | Horst Duster | 4 | 1 | 072 121 122
The street_id
column represents a ‘one to many’ relationship between the
people object and the related street object, which is in the streets
table.
One way to further normalise the table is to split the name field into first_name and last_name:
id | first_name | last_name | house_no | street_id | phone_no
---+------------+------------+----------+-----------+------------
1 | Horst | Duster | 4 | 1 | 072 121 122
We can also create separate tables for the town or city name and country, linking them to our people table via ‘one to many’ relationships:
id | first_name | last_name | house_no | street_id | town_id | country_id
---+------------+-----------+----------+-----------+---------+------------
1 | Horst | Duster | 4 | 1 | 2 | 1
An ER Diagram to represent this would look like this:
The SQL required to create the correct people table is:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
The schema for the table (enter \d people
) looks like this:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Bemerkung
For illustration purposes, we have purposely omitted the fkey constraint.
The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.
Bemerkung
It is possible to ‘force’ the streets table to be deleted by using the CASCADE command, but this would also delete the people and any other table which had a relationship to the streets table. Use with caution!
The SQL command you should use looks like this (you can replace the street name with a name of your choice):
insert into streets (name) values ('Low Road');
Here is the correct SQL statement:
insert into streets (name) values('Main Road');
insert into people (name,house_no, street_id, phone_no)
values ('Joe Smith',55,2,'072 882 33 21');
If you look at the streets table again (using a select statement as before),
you’ll see that the id
for the Main Road
entry is 2
.
That’s why we could merely enter the number 2
above. Even though we’re
not seeing Main Road
written out fully in the entry above, the
database will be able to associate that with the street_id
value of
2
.
Bemerkung
If you have already added a new street
object, you might find
that the new Main Road
has an ID of 3
not 2
.
Here is the correct SQL statement you should use:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Result:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Bemerkung
You will notice that we have prefixed field names with table names (e.g. people.name and streets.name). This needs to be done whenever the field name is ambiguous (i.e. not unique across all tables in the database).
The units being used by the example query are degrees, because the CRS that the layer is using is WGS 84. This is a Geographic CRS, which means that its units are in degrees. A Projected CRS, like the UTM projections, is in meters.
Remember that when you write a query, you need to know which units the layer’s CRS is in. This will allow you to write a query that will return the results that you expect.
CREATE INDEX cities_geo_idx
ON cities
USING gist (the_geom);
alter table streets add column the_geom geometry;
alter table streets add constraint streets_geom_point_chk check
(st_geometrytype(the_geom) = 'ST_LineString'::text OR the_geom IS NULL);
insert into geometry_columns values ('','public','streets','the_geom',2,4326,
'LINESTRING');
create index streets_geo_idx
on streets
using gist
(the_geom);
delete from people;
alter table people add column city_id int not null references cities(id);
(capture cities in QGIS)
insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
values ('Faulty Towers',
34,
3,
'072 812 31 28',
1,
'SRID=4326;POINT(33 33)');
insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
values ('IP Knightly',
32,
1,
'071 812 31 28',
1,F
'SRID=4326;POINT(32 -34)');
insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
values ('Rusty Bedsprings',
39,
1,
'071 822 31 28',
1,
'SRID=4326;POINT(34 -34)');
If you’re getting the following error message:
ERROR: insert or update on table "people" violates foreign key constraint
"people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".
then it means that while experimenting with creating polygons for the
cities table, you must have deleted some of them and started over. Just
check the entries in your cities table and use any id
which exists.
create table cities (id serial not null primary key,
name varchar(50),
the_geom geometry not null);
alter table cities
add constraint cities_geom_point_chk
check (st_geometrytype(the_geom) = 'ST_Polygon'::text );
insert into geometry_columns values
('','public','cities','the_geom',2,4326,'POLYGON');
select people.name,
streets.name as street_name,
st_astext(people.the_geom) as geometry
from streets, people
where people.street_id=streets.id;
Result:
name | street_name | geometry
--------------+-------------+---------------
Roger Jones | High street |
Sally Norman | High street |
Jane Smith | Main Road |
Joe Bloggs | Low Street |
Fault Towers | Main Road | POINT(33 -33)
(5 rows)
As you can see, our constraint allows nulls to be added into the database.