# This note assumes

Data Management

Nov 29, 2012 (4 years and 23 days ago)

293 views

1

Lab2

(19
-
Feb
-
2010
)

for

Spatial Databases Course (
DT249,
DT211

& DT249)

Lecturer Pat Browne

In Kevin Street labs make sure that each table and view you create is unique within
the database. Append each table and view you create with your initials or stu
dent
number. You can check what other tables/views exist with the
\
d
\
nd
\
dv command
respectively.

If working on your own machiine y
ou should have installed PostgreSQL/PostGIS,

You should login into postgreSQL and con
nect to PostGIS with
\
c postgis

The basic idea is to get familiar with PostgreSQL/PostGIS/OpenJump
and

to understand
a rang
e

of queries.

If you are doing these labs in college you should be logged into cork.

1. Spatial Reference System

The
Europea
n Petrol
eum Survey Goup (EPSG) provide a name and the unique Integer ID
to each
spatial

reference system. T
he common WGS 84
(
l
atitude, longitude)
has an
identifier EPSG
:4326. The Irish National Grid (ING) is referred to as EPSG:29900
.

Many

database systems use
4
326

for their SRID.

On the course generally we use
WGS84(
EPSG
:4326) and ING(EPSG:29900).
For this lab, we will use mostly use
EPSG:29900.

2
. How to find the SRID of a table

select srid(the_geom) from county;

Hit ‘q’ to stop output.

3
.
How to find all
S
RI
D in PostGIS

select srid from spatial_ref_sys;

select * from spatial_ref_sys where srid=4326;

You can
find details of most

SRIDs on the web.

4. Transforming between reference systems

Here we calculate the
d
istance between Dublin and Limerick in met
res.
The
points are in
well know text (WKT)
long
-
lat format. The points
in the inner brackets
are in WGS84:

4326 and transformed to ING:29900

SELECT st_distance(

transform(GeometryFromText('POINT(
-
6.2294 53.3348)', 4326), 29900),

transform(GeometryF
romText('POINT(
-
8.6251 52.6697)', 4326), 29900));

2

How you get the distance in kilometres.

What happens if you don’t transform?

SELECT st_distance(GeometryFromText('POINT(
-
6.2294 53.3348)',
4326),GeometryFromText('POINT(
-
8.6251 52.6697)',
4326)
);

Not
e

yo
u must either supply an SRID or it must be stored in the database.

5
. What do these SQL statements do?

Run these text based queries in pgAdmin or SQL shell.
Write out in English what the
queries are doing;

for help
browse the web or

consult the
OGC’s
Simple Features for
SQL and the PostGIS

1.5

Manual
.

select name, asText(Centroid(the_geom)) from county;

select name, asText(enve
lope(the_geom)) from county;

select name,
asText(extent(the_geom))

from county group by
name;

To run the above

in OpenJump

change
astext

to

asbinary
.

The following 3 queries have a
purely textual output

and should be typed in at the SQL
shell.

select name from county where GeomFromText('POINT(309612.0
233192.0)', 29900) && the_geom;

SELECT name, area(the_geom)/10000 A
S hectares FROM county
ORDER BY hectares DESC LIMIT 1;

FROM roads AS r, county AS c WHERE r.the_geom && r.the_geom

AND contains(c.the_geom,r.the_geom)

AND c.name = 'Meath
'

GROUP BY c.name

ORDER B

6. Load SQL files into PostgreSQL

3

For details on the

historical (dublin
-
historical
) and geological
(
bedrock
)

data see:

http://www.a

http://www.dcenr.gov.ie/Spatial+Data/Geological+Survey+of+Ireland/GSI+Spa
tial+Data

Copy bedrock.sql,
d
ublin
-
historical.sql,
d
ublin_regions.sq
l from the
DATA
folder on the
CD

to

C:
\
Program Files
\
PostgreSQL
\
8.4
\
bin
.

Run the following programs

in
C:
\
Program Files
\
PostgreSQL
\
8.4
\
bin

psql
-
d postgis
-
U postgres
-
f

d
ublin_
historical.sql

psql
-
d postgis
-
U postgres
-
f
dublin_regions.sql

psql
-
d postgis
-
U postgres
-
f
bedrock.sql

View each of these tables in OpenJump using;

select asbinary(the_geom) from
dublin_
historical
;

Now run the followin
g queries in the s
hell.

How many historical sites within 10 metres of each road in
Fingal?

SELECT
c.name,
townlands
,

r.name, r.class
FROM
d
ublin_
historical h, roads r, county c WHERE

distance(h.the_geom,r.the_geom) < 1
0

AND c.name = '
Dublin Fingal
';

SELECT townlands
,

r.
name, r.class
FROM
dublin_
historical h,

distance(h.the_geom,r.the_geom) < 1
0

AND c.name = '
Dublin County Borough
';

select townlands from
Dublin
-
historical WHERE
distance(the_geom, GeomFromText('POINT(309612.0 233192.0)',
29900))

< 100;

select c.name, h.townlands from county AS c, historical AS
h WHERE distance(h.the_geom, GeomFromText('POINT(317431.0
231704.0)', 29900)) < 1000 and c. Name = 'Dublin';

--

to se the result from this query you will need to create a table to store t
he result

select intersection(b.the_geom, c.the_geom) as the_geom,
b.agebracket as agebracket

4

from bedrock b, county c

where b.the_geom && c.the_geom and intersects(b.the_geom,
c.the_geom) and c.name = 'Dublin';

7
.

spatial
data

from SHP files

in
to PostgreSQL/PostGIS

In this section we use the ‘shape’ file converter program
, called
shp2pgsql,

to convert
an ESRI shape file (with extension .shp) to an SQL file suitable for loading into
PosgreSQL.

We often need to interact with PostgreSQL at

command
prompt
level

from
outside the DBMS.

Note that is a distinct window from the SQL shell.

Change directory
(
cd
) in the command prompt to
:

C:
\
Program Files
\
PostgreSQL
\
8.4
\
bin

We will now

spatial data
from this command line.

Althou
gh, we often talk of “a shape

file” there are a set of auxiliary files required

(
with
extensions
.shp,.dbf,.shx).

Extract

the files
URBANS_region
.dbf
,
URBANS_region
.shp

and
URBANS_region
.shx

from

the CD
:

D:
\
SPATIAL
-
DATABASES
-
CD
\
Data
\
Osmaps
.zip

And put the
three files in:

C:
\
Program Files
\
PostgreSQL
\
8.4
\
bin

These three files constitute what is generally called a ‘shape file’.

We

will

convert from ES
RI’s shape format
to
an
SQL
file
PostgreSQL/PostGIS. Use the

command prompt i
n
PostgreSQL bin

directory
:

C:
\
Program Files
\
PostgreSQL
\
8.9
\
bin

--

convert
URBANS_region

files to SQL
, this will make
urban
.sql

shp2pgsql
-
s 29900
URBANS_region

public.
urban

>
urban
.sql

--

Examine
urban
.sql

see CREATE and INSERT

statements. Try to
get familiar with the basics of an SQL file containing spatial data.

--

To check all the options available in

the conversion program
type:

shp2pgsql
-
?

--

If the file converts successfully

then you should

load the SQL file into the da
tabase.
A
t
PostgreSQL/8.4/
bin

command line,

into PostgreSQL
, you may
be

p
sql
-
d postgis
-
U postgres
-
f
urban
.sql

This

last

step can also be done in pgAdmin III.

8

In
SQL Shell (psql)

you sh
ould
be connected to
postgis
.

Type the following:

\
dt
urban

\
d

urban

5

You should see the
urban

table created by the

psql

command in the previous section.

Note and index and constraints were automatically created.

Note
the_geom

is the column where the geome
try is stored. Geometry is generally only
meaningful when rendered and viewed graphically.
It is not a good idea to query raw
geometry in a
text

interface

(SQL shell)
.

However we can ru
n some queries in a text
window.

9

Please run and explain the followin
g queries.

Use the PostGIS and OGC
-
SFSQL to understand the queries. These were handed out in
class and are available on line.

Before running the queries you should get a feel for these
data sets by viewing them in OpenJump with:
select asbinary(the_geom)
f
rom your_table;

The next query returns
stored

data, the results are not calculated.

select name, area, perimeter

from
urban
;

The columns
area
and

perimeter

are the
stored

value of the area
and perimeter
respectively. We will now
compare the calculated

and stored values

for
Dublin City
:

select name, area, perimeter from urban where name = 'Dublin City';

select perimeter(the_geom)
,

area(the_geom
)

from
urban

where name =

'Dublin City'
;

Do the stored values differ from the calculated values?

The next qu
ery
calculates

the

sum of the area for
3 Dublin regions.

Note basic
the unit

for Irish maps in ING is metres, there are 1000000 square metres in a square kilometre

(10
6

sqm)
.

select sum(area(
u.
the_geom))/1000000
as

sq

Kms

from
urban

AS u where
u
.name
=

'
'

OR u.
name = 'Dublin City'

OR u.name = 'Howth
'
;

SELECT townlands FROM
dublin_
c WHERE

distan
ce(h.the_geom,r.the_geom) < 100

AND c.name like

'
%
Dublin
%
';

select townlands from
dublin_
historical WHERE
distance
(the_geom, GeomFromText('POINT(309612.0 233192.0)',
29900)) < 100;

select c.name, h.townlands from county AS c,
dublin_
historical AS h WHERE distance(h.the_geom,
6

GeomFromText('POINT(317431.0 231
704.0)', 29900)) < 10
0 and
c.n
ame
like

'
%
Dublin
%
';

The follo
wing query should be run from OpenJump

select
asbinary(
intersection(b.the_geom, c.the_geom)
),

b.agebracket as agebracket

from bedrock b, county c

where b.the_geom && c.the_geom and intersects(b.the_geom,
c.the_geom) and c.name = '
Dublin County Borough
';

We can also create a table as a result of a query. Do not run this query, it illustrates how
to create a table on the fly. This technique is useful when building queries.

create table
d
ublin_
bedrock1 as

select intersection(b.the_geom, c.the_geom) a
s the_geom,
b.agebracket as agebracket

from bedrock b, county c

where b.the_geom && c.the_geom and intersects(b.the_geom,
c.the_geom) and
c.name
= '
Dublin County Borough
'
;