Microsoft Word – Practical Session 5 – 3D GIS part 2 and UCL Facilities Management
Page 1 of 5
3D Queries Practical – Table of Contents
Part 1 – Exploring 3D Queries……………………………………………………………………………………… 2
Exercise 1 – Connect FME Data Inspector to PostgreSQL/PostGIS ……………………………… 2
Exercise 2- Exploring 3D Queries……………………………………………………………………………… 2
Exercise 3 – Constructing the Flying Freehold using SQL ……………………………………………. 4
Part 2 – UCL Facilities Management…………………………………………………………………………….. 5
Part 1 – DDL- Create Table ……………………………………………………………………………………… 5
Part 2 – DDL- Create Constraints ……………………………………………………………………………… 5
Part 3 – DML – Insert Data ………………………………………………………………………………………. 5
Part 4 – SQL Queries………………………………………………………………………………………………. 5
Page 2 of 5
Part 1 – 3D Queries
For this part of the exercise, keep using the practical4
schema you used last week, and also the practical4.txt file
Exercise 1 – Connect FME Data Inspector to
PostgreSQL/PostGIS
Note that you need to be on the UCL VPN for this to work (or working on the UCL Desktop or
Desktop Anywhere systems)
(See separate instruction sheet in Moodle for how to do this)
Exercise 2 – Exploring 3D Queries
This exercise assumes that you have created the 3D objects as per Practical 4. Write the
SQL statements to answer the following questions. You can find a list of helpful 3D
functions here:
http://postgis.net/docs/PostGIS_Special_Functions_Index.html#PostGIS_3D_Functions
1. Depending on how you wrote your insert statements las week, the geometryname
column in your threedbuildings table might not be populated yet (i.e. the values in this
column are null) – update this with the geometry type for each geometry using
st_geometrytype
2. Calculate the 2D and 3D length of all the geometry in the threedbuildings table and
list these with the geometry type (using st_geometrytype). Note which types of
geometry have length values.
3. How far is the 3D point from the line in 2D and 3D.
4. How far is the 3D point from the 3D polygon in 2D and 3D?
5. How far is the point from the polyhedron?
6. What is the area of the polygon?
7. What is the volume of the polyhedron (use st_makesolid if necessary)?
Page 3 of 5
8. Does the point intersect the polyhedron in 2D or in 3D?
9. Use ST_ASTEXT to examine the coordinates of the polyhedral surface and find a
corner of this surface. Insert a new point with coordinates of this corner (you can
select any of the corners). Call the new point ‘Point 2’. Remember that we used
coordinate system 27700 for this dataset.
10. Does Point 2 intersect the polyhedral surface in 2D or 3D. Do you get an error?
11. Modify the above query to only look at 3D intersection.
12. Write the SQL to generate a collection of the different component parts that make up
the polyhedral surface using st_forcecollection.
13. Use the above statement in a WITH clause and force this collection into 2D using
st_force2d
14. Take the 2D data from the above query, and embed it into the WITH statement.
Using this combined WITH statement, check whether a 2D intersection query works
now?
15. You probably got this error in the above query: Relate Operation called with a
LWGEOMCOLLECTION type. This is unsupported. This basically means that you
can’t do an ST_INTERSECTS query (2D) on a geometry collection (3D intersection
works fine!). What needs to happen is that the geometry collection needs to be split
into individual components. You do this by changing the first line of your query
from1:
WITH collection as (select st_forcecollection(location) as location
from practical4.threedbuildings
where geometryname = ‘ST_PolyhedralSurface’)
to
select (ST_Dump(st_forcecollection(location))).geom as location
from practical4.threedbuildings
where geometryname = ‘ST_PolyhedralSurface’
This will return 6 rows – one for each face in the polyhedral surface. Now repeat the
2D intersection query with the modified statement.
1 ST_Dump creates a list of the different parts of the geometry, along with a number for each
part. To get just the geometry, you need to use the .geom statement
Page 4 of 5
16. Write a query that
a. Creates a geometrycollection and then collapses the polyhedral surface to
2D
b. Buffers the result by 5m
c. Cookie cuts the buffered data with the 2D collapsed polyhedral surface
For each stage, make use of a WITH statement. The in class example is very similar
so you can use that as a guide.
17. Visualise the result of the above query by using it in an INSERT statement – first
create a table called practical4.buffer with columns ID and geometryname. Add a
column called location that will hold a 3D geometry.
18. Insert the geometry is 3D from #16 – you will need to use ST_FORCE3D to insert the
geometry as the cookie cut result is 2D. Visualise the result in FME
19. Extend the above SQL to shift the cookie cut surface to the original lower height of
14m (as per the original INSERT statement for the polyhedral surface). You can use
ST_TRANSLATE for this st_translate(st_force3d(location), 0,0,14). Visualise the
result in FME.
20. Extend the above query to:
a. Extrude the cookie cut surface to the original height of the 3D polyhedral
surface by extruding the cookie cut surface by 8m (see the original geometry
creation script from the previous practical – the lower height is 14m and the
upper height is 22m)
Now use FME Data Inspector to visualise this new geometry
21. Use st_area to find out the area of the polyhedral surface – collapse the surface into
a 2D object (st_force2d) first
Note that you will get a double-count on the st_area value (128 instead of 64). This is
because when the 3D geometry is collapsed into 2D the roof surface is shifted to the floor.
st_area(st_envelope(st_force2d(location))) will fix this problem.
22. Calculate the volume of the new geometry and compare it to the area of the 2D
cookie cut buffer * the height of the polyhedral surface (use a UNION ALL statement
so that you can compare the results of two queries at the same time). Remember
that you will need both st_zmax and st_zmin to work out the height as the polyhedron
is off the ground (i.e. lowest height is 14m)
Exercise 3 – Constructing the Flying Freehold using SQL
Last week, we created the 3 objects making up the flying freehold example as a three
separate objects. This week, we will first use UNION and DIFFERNECE operations to see if
we can end up with the 2 buildings that we require (one with the extended room, one with the
gap that is the space that the room from the neighbour takes up. The SQL for this has been
demonstrated in class – run the required SQL and then visualise the results in FME to make
sure that you got the expected objects.
Page 5 of 5
Part 2 – UCL Facilities Management
Create a schema called assets for this part of the practical,
and use a script file called assets.txt
** Note – as the example data is referenced to a LOCAL
coordinate system, you should use SRID = 0 **
Part 1 – DDL- Create Table
Use the logical diagram for the UCL facilities management to write the CREATE TABLE
scripts. The logical diagram does not provide field lengths for any character varying fields, so
make best assumptions based on the information to be stored in the field – e.g. for a name or
surname 25 characters might be suitable, a room number might only require 10.
Remember to add the spatial columns (type geometry) to the tables SEPARATELY.
Part 2 – DDL- Create Constraints
Using the diagram, write the primary key, foreign key, unique and check constraints for the
Facilities Management system.
Part 3 – DML – Insert Data
Use the sketch diagram to write the SQL and insert the data into each table in the system
Part 4 – SQL Queries
Write the SQL queries that will answer the functional requirements set out for the UCL
Facilities Management system