Loading in 5 sec....

EnterpriseDB CorporationPowerPoint Presentation

EnterpriseDB Corporation

- 117 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' EnterpriseDB Corporation' - beck-russell

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

### EnterpriseDB Corporation

EnterpriseDB is the leading provider of enterprise-class products and services based on PostgreSQL, the world's most advanced open source database.

Over 600 customers including: Sony, FTD, British Telecom, TDAmeritrade

Installation – Tutorial - Exercises

- Like Oracle Spatial, DB2 Spatial, and SQL Server Spatial, PostGIS adds spatial capabilities to an existing relational database, in this case, PostgreSQL.
- It could be renamed to “PostgreSQL Spatial” as it functions in the same way as proprietary database extensions.

- Adds support for geographic objects to the PostgreSQL object-relational database
- PostgreSQL already has “geometric types” but native geometries are too limited for GIS data and analysis

- PostGIS adds an indexing mechanism to allow queries with spatial restrictions or filters (“within this bounding box”) to return records very quickly from large tables.

- PostGIS adds a “geometry” data type to the usual data types (ie. varchar, integer, date, etc.)
- PostGIS adds spatial predicates and functions that use the geometry data type.
- ST_Distance(geometry, geometry)
- ST_Area(geometry)
- ST_Intersects(geometry, geometry))

- Open source
- General Public License (GPL)
- Open development and support

- History
- 2001: First release, Mapserver support
- 2002: Improved functions, indexes
- 2003: GEOS support, many functions
- 2004: SFSQL conformance
- 2005: Lightweight geometries
- 2006: OpenGIS SFSQL compliance
- 2007: SQL/MM, curves & performance
- 2008-2010: Performance enhancements

- Because databases are better than files
- Unified Storage, Management, Access
- SQL Everywhere

- Transactional Integrity
- Multiple Users, Multiple Edits

PostGIS in the Spatial Stack

LAN

Internet

Mapserver

uDig

OpenIMF

GeoServer

GRASS

WebClient

QGIS

MapGuide

ArcGIS

OpenJUMP

uDig

- Lots of people …
- 1790 mailing list members
- 14K visits/month10K visitors/month
- 130 source code downloads per day
- 970K Google search results
- Great Google trends …

- North Dakota State Water Commission in Bismark uses PostGIS as the cost-effective foundation for managing the use of state-wide water resources. PostGIS tied unobtrusively into their existing desktop systems and pre-empted a costly migration to an ArcIMS/ArcSDE system.

- The Ministry of Sustainable Resource Management (British Columbia, Canada) uses PostGIS to store, manage and analyze their Digital Road Atlas, a large and complex road network database.

- Institut Géographique National (IGN France) uses PostGIS to underpin a system containing more than 100 million spatial objects, including a 3D national database of roads, rails, hydrography, vegetation, buildings and administrative boundaries.

- InfoTerra (United Kingdom) uses PostGIS to store >600 million features of topographic data. Sits at the back-end of their online DataStore. Notable load times of 12 hours for the entire data-suite ~14000 features per second. More notable savings of ~£1000000 per annum.

- Introduction to Spatial Concepts
- Installation (PostgreSQL and PostGIS)
- Creating and Loading Data
- Creating Indexes
- Spatial Operations and Predicates
- Spatial SQL
- System Configuration

Introduction to Spatial Concepts

- A Geographical Information System (GIS) is any system used for capturing, storing, analyzing, managing, or presenting geospatial data.

- Geometric Datatypes
- Geometry Validity

- Each geometry has a Well-Known Text representation (WKT)
- A geometry type
- A comma-separated list of coordinate pairs

- Some examples are:
POINT ( 7 6 )

- Some examples are:
MULTIPOINT ( 1 4, 1 5, -0.5 5.5314 )

- Some examples are:
LINESTRING ( 1 3, 1 1, 3 0 )

- Some examples are:
MULTILINESTRING (( 2 1, 2 2, 3 4 ),

( 4 3, 3 2, 4 1, 3 3 ))

- Some examples are:
POLYGON (( 2 5, 3 8, 6 8, 5 4, 2 5 ),

( 3 6, 4 5, 5 7, 4 6, 3 6 ))

- Some examples are:
MULTIPOLYGON ((( 6 2, 6 4, 8 4, 9 1, 6 2 ),

( 7 3, 8 2, 8 3, 7 3 )),

(( 9 2, 8 5, 10 4, 10 3, 9 2 )))

- Time for some exercises!

- PostGIS is compliant with the Open Geospatial Consortium’s (OGC) OpenGIS Specifications
- Most functions require / assume geometries are valid and in many cases, simple.
- Geometry validity really only applies for areal geometries
- Geometry simplicity applies to point and linear geometies

- A POINT is inherently simple as a 0-dimentional geometry object.
- A MULTIPOINT is simple if no two POINTs are the same.

- By definition, a LINESTRING is always valid
- It is simple if it does not pass through the same point twice.

SIMPLE

NOT

SIMPLE

SIMPLE

NOT

SIMPLE

- MULTILINESTRINGs are simple if all its elements are simple and they only intersect at boundary points.

NOT SIMPLE

SIMPLE

SIMPLE

- By definition, a POLYGON is always simple.
- It is valid if
- The boundary is made up of simpleLINEARRINGs
- boundary rings don’t cross
- holes are completely within the outer ring and touch the outer ring at most one point.
- it does not contain cutlines / spikes

- By definition, a MULTIPOLYGON is valid iff
- All elements are valid
- Element interiors cannot intersect
- Element boundaries can touch, but only at a finite number of POINTs.

- Windows Installer
- PostgreSQL 8.4.4
- Automatically installs itself as a Windows Service

- PgAdmin III
- Psql Interactive SQL Shell

- PostgreSQL 8.4.4

- Directories created during installation:
- \bin - Executables
- \include - Include files for compilation
- \lib - DLL shared library files
- \share - Extensions

- Tools included with the install:
- PgAdmin III
- psql Command Line

- Under Linux
http://www.postgresql.org/docs/8.4/static/installation.html

- ./configure
- gmake
- su
- gmake install
- adduser postgres
- mkdir /usr/local/pgsql/data
- chown postgres /usr/local/pgsql/data
- su - postgres
- /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
- /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
- /usr/local/pgsql/bin/createdb dbname
- /usr/local/pgsql/bin/psql dbname

- As of PostgreSQL 8.3, an Application Stack Builder is used to obtain the latest installers for desired modules.
- Demonstrate Installation …

- The Stack Builder has finished downloading the PostGIS installer.
- Demonstrate PostGIS Installation …

- Under Linux
http://postgis.refractions.net/docs/ch02.html

tar xvfz postgis-1.5.1.tar.gz

cd postgis-1.5.1

./configure

make

make install

createdb yourdatabase

createlang plpgsql yourdatabase

psql -d yourdatabase -f postgis.sql

psql -d yourdatabase -f postgis_comments.sql

psql -d yourdatabase -f spatial_ref_sys.sq

- In order for PostgreSQL to be accessible from the command prompt
- Create a PGHOME environment system variable
- Add %PGHOME%\bin to the system path

- From the command line
createdb -T template_postgis dbname

- Launch pgAdminIII

- Create a new database
- Select “template_postgis” as the template

- Verify the installation
- Check for the spatial system tables
- spatial_ref_sys – stores unique coordinate references
- geometry_columns – stores metadata for a spatial column

- Check for the spatial system tables

3.5 –Spatially-Enable an Existing DB

- From the command line
psql -f "%PGHOME%\share\contrib\postgis-1.5\postgis.sql" dbname

psql -f "%PGHOME%\share\contrib\postgis-1.5\spatial_ref_sys.sql" dbname

3.5 –Spatially-Enable an Existing DB

- Using a User Interface
- Connect to your existing database
- Open the “Execute Arbitrary SQL Queries” window
- Load/run the PostGIS extension (postgis.sql)
- Load/run the PostGIS spatial reference systems (spatial_ref_sys.sql)

- “Manually” create geometries
CREATETABLE points

(pt geometry, name varchar);

INSERTINTO points VALUES

('POINT(0 0)', 'Origin');

INSERTINTO points VALUES

('POINT(5 0)', 'X Axis');

INSERTINTO points VALUES

('POINT(0 5)', 'Y Axis');

SELECT name, ST_AsText(pt),

ST_Distance(pt, 'POINT(5 5)')

FROM points;

name | st_astext | st_distance

--------+------------+------------------

Origin | POINT(0 0) | 7.07106781186548

X Axis | POINT(5 0) | 5

Y Axis | POINT(0 5) | 5

(3 rows)

- Function References:
http://postgis.refractions.net/docs/ch07.html

“Appendix A: Data Dictionaries”

- (included in ‘PostGIS Workshop.doc’)
Or online PostGIS Documentation

http://postgis.refractions.net/docs

- (included in ‘PostGIS Workshop.doc’)

- Time for some exercises!

- Shape File (Misnomer! 3+ Files!)
- .shp = geometry
- .dbf = attributes (string, number, date)
- .shx = utility index

- PostGIS/PostgreSQL Table
- Columns can be geometry
- Columns can be attributes

- One Shape File = One PostGIS Table

- shp2pgsql [opts] shapefile tablename
- shp2pgsql –i –s 3005 –D bc_pubs.shp bc_pubs > bc_data.sql

- Read in .shp file
- Write out .sql file
- Load .sql file into PostgreSQL
- using psql
- using PgAdmin

- As mentioned in the install process, to make life easier, include %PGHOME%\bin\ in your system path:
- Run cmd.exe
- > cd %WORKSHOP%\data\
- > dir *.shp
- > shp2pgsql -s 3005 bc_pubs.shp public.bc_pubs > bc_data.sql

- > notepad bc_data.sql

- Load the data via psql
- > psql –d postgis –U postgres –f bc_data.sql

- Time for some exercises!

bc_pubs100s of points

bc_roads100s of thousands of lines

bc_hospitals10s of points

bc_municipality100s of polygons

bc_habitat_areas100s of polygons

4.3.1 – Indexes and Query Plans

- Databases are fancy engines for speeding up random access to large chunks of data.
- Query plans are the rules used by databases to convert a piece of SQL into a strategy for reading the data.

4.3.1 – Indexes and Query Plans

- In PostgreSQL, these plans can be viewed by prefacing your query with “EXPLAIN”.
- To view the actual run time along side the estimated query plan, preface with “EXPLAIN ANALYZE”.

4.3.1 – Indexes and Query Plans

- In PgAdminIII, view a simple filtered query by pressing the “Explain” button

4.3.2 – When Query Plans Go Bad

- As previously mentioned, a database attempts to minimize disk accesses.
- Indexes can lower disk accesses for queries that only return a few rows, but can actually increase accesses for queries that return a large number of rows.

4.3.2 – When Query Plans Go Bad

- The database builds “plans” based on statistics about data distribution sampled from the tables
- Always tries to be “selective”, to pull the fewest number of records necessary to move on to the next step

- The database builds bad plans when it has bad statistics
- Make sure your database has up-to-date statistics by running ANALYZE

4.3.2 – When Query Plans Go Bad

- It’s important to tune PostgreSQL’s configuration parameters so it can properly chose the best query plan for any given query.
- IE. Is it faster to perform an index scan from a minimally selective query than it is to perform a sequential scan through a table since most of the data is in memory anyway?

- Time for some exercises!

4.4.1 – Creating Spatial Indexes

- PostGIS implements R-Tree indexes on top of the GiST indexing system
- Organizes data into nesting rectangles for fast searching

4.4.1 – Creating Spatial Indexes

- An example index creation command is
CREATEINDEX bc_pubs_gidx ON bc_pubs

USINGGIST (the_geom);

- Time for some exercises!

- Spatial index operator is “&&”
- “Bounding Boxes Interact”

A&&B = TRUE

A&&B = FALSE

- Bounding Boxes are not enough!
- Two pass system required
- Use bounding boxes to reduce candidates
- Use real topological test to get final answer

A&&B = TRUE_ST_Intersects(A, B) = FALSE

_ST_Intersects(A,B)

- Index operations (&&) are built into common functions for automatic use, but you can use them separately if you like.
- ST_Intersects(G1,G2)
- G1 && G2 AND _ST_Intersects(G1,G2)

- ST_Contains(G1,G2)
- ST_Within(G1,G2)
- ST_Touches(G1,G2)
- ST_DWithin(G1,G2,D)
- G1 && ST_Expand(G2,D) AND ST_Distance(G1,G2) > D

- ST_Intersects(G1,G2)

- Time for some exercises!

- A surprising number of traditional GIS analysis questions can be answered using a spatial database and SQL.
- GIS analysis is generally about filtering spatial objects with conditions, and summarizing the results – and that is exactly what databases are very good at.

- Time for some exercises!

- PostGIS permits a user to store invalid or non-simple geometries in a spatial database, unlike Oracle, which require validity upon insertion.

- However, most spatial predicates or functions in PostGIS expect the data to conform to the OGC Simple Features for SQL standard
- Polygon rings don’t cross other rings or self-intersect
- Interior rings on polygons can touch exteriors, but only at a point
- Multi-polygons are non-overlapping

- PostGIS offers ST_IsSimple() and ST_IsValid() functions, useful in determining if geometries conform to the specification.
SELECT count(*)

FROM bc_habitat_areas

WHERENOTST_IsValid(the_geom);

- Time for some exercises!

- It’s easy to write inefficient queries for distances in PostGIS because it’s not always obvious how to use the index in a distance query.

Question: How many wildlife habitats are within 20 kilometers of the municipality of Oliver?

First, find out where Oliver is located.

SELECT ST_AsText(ST_Centroid(the_geom))

FROM bc_municipalities

WHERE name = 'Oliver';

POINT(1470065.29710885 484600.794443135)

Then, use that location to sum all habitats within 20km. The “obvious” way does not use any indexes.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_Distance( the_geom,ST_GeomFromText('POINT(1470065 484600)',3005) ) < 20000;

The “optimized” way is to use ST_DWithin()which silently adds an index into the mix.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_DWithin( the_geom,ST_GeomFromText(

'POINT(1470065 484600)',3005),

20000 );

Here’s the definition of ST_DWithin().

CREATEFUNCTION ST_DWithin(geometry, geometry, float8)RETURNS boolean AS' SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND ST_Distance($1, $2) < $3 'LANGUAGE'SQL'IMMUTABLE;

For complete, here’s how you should never answer a distance query.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_Contains(

ST_Buffer(the_geom, 20000),

ST_GeomFromText('POINT(1470065 484600)',3005)

);

- SRID(): Integer - Returns the Spatial Reference System ID for this geometric object. This will normally be a foreign key to an index of reference systems stored in either the same or some other datastore.
- Is3D(): Integer - Returns 1 (TRUE) if this geometric object has z coordinate values.

- Disjoint(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially disjoint” from anotherGeometry.
- Within(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially within” from anotherGeometry.
- Contains(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially contain” from anotherGeometry.

- Distance(geometry):Double - Returns the shortest distance between any two Points in the two geometric objects as calculated in the spatial reference system of this geometric object. Because the geometries are closed, it is possible to find a point on each geometric object involved, such that the distance between these 2 points is the returned distance between their geometric objects.
- Intersection(geometry): Geometry – Returns the geometric object that represents the Point set intersection of this geometric object with another geometry.

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Approach

make pair-wise tests of the intersections between the Interiors, Boundaries, and Exteriors of two geometries and to represent these relationships in an “intersection” matrix

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Possible values:

{T, F, *, 0, 1, 2}

Where:

T == {0,1,2}

F == empty set

* == don’t care

0 == dimensional 0 – point

1 == dimensional 1 – line

2 == dimensional 2 - area

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Boundary

the set of geometries of the next lower dimension

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Interior

the points that are left when the boundary points are removed

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Exterior

consists of points not in the interior and boundary

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

(b)

(a)

2

1

2

1

0

1

2

1

2

ST_Relate(a, b) = ‘212101212’

- Time for some exercises!

Crosses(geometry):Integer

Crosses relation applies to P/L, P/A, L/L and L/A situations.

Case aP, bL or Case aP, bA or Case aL, bA:

a.Crosses(b)

(I(a) ∩ I(b) ≠∅) (I(a) ∩ E(b) ≠∅)

a.Relate(b, ‘T*T******’)

Case aL, bL:

a.Crosses(b)

dim(I(a)∩I(b)) = 0

a.Relate(b, ‘0********’);

Overlaps(geometry):Integer

The Overlaps relation is defined for A/A, L/L and P/P situations.

Case aP, bP or Case aA, bA:

a.Overlaps(b)

(I(a) ∩I(b)≠∅) (I(a) ∩E(b)≠∅) (E(a) ∩I(b)≠∅)

a.Relate(b, ‘T*T***T**’)

Case aL, bL:

a.Overlaps(b)

(dim(I(a) ∩I(b) = 1) (I(a) ∩E(b)≠∅) (E(a) ∩I(b)≠∅)

a.Relate(b, ‘1*T***T**’)

- Standard joins use a common key
SELECT a.var1, b.var2

FROM a, b

WHERE a.id = b.id

- Spatial joins are based on a spatial relationship
SELECT a.var1, b.var2

FROM a, b

WHEREST_Intersects(a.geom, b.geom)

- The previous distance query can be expressed using a single spatial join.
SELECT count(*) AS num_habitats

FROM

bc_municipalities a,

bc_habitat_areas b

WHERE

ST_DWithin(

a.the_geom,

b.the_geom,

20000)

AND

a.name ILIKE 'Oliver%';

Question: Find all pubs located within 250 meters of a hospital

SELECT bc_hospitals.name, bc_pubs.name

FROM bc_hospitals, bc_pubs

WHEREST_DWithin(

bc_hospitals.the_geom, bc_pubs.the_geom, 250);

name | name

--------------------------------+-----------------------------------

British Columbia Cancer Agency | Holiday Inn Vancouver Centre

The Richmond Hospital | Executive Airport Plaza Hotel ...

St. Paul's Hospital | Holiday Inn Hotel & Suites ...

St. Paul's Hospital | Bosnman's Hotel

St. Paul's Hospital | Sheraton Vancouver Wall Centre ...

St. Paul's Hospital | Burrard Motor Inn

St. Paul's Hospital | Century Plaza Hotel and Spa

St. Paul's Hospital | The Fountainhead Pub

Nanaimo Regional General Hos... | Windward Pub

(9 rows)

Question: Determine the density of roads per municipality.

SELECT

m.name,

(sum(ST_Length(ST_Intersection(

m.the_geom, r.the_geom))) /

sum(ST_Area(m.the_geom))) AS road_density

FROM

bc_municipalities m,

bc_roads r

WHERE m.the_geom && r.the_geom

GROUPBY m.name

ORDERBY road_density DESC;

name | road_density

----------------------------+-----------------------

Silverton | 0.000246331953966186

Slocan | 0.000131565932828081

Montrose | 0.000118947787979122

New Denver | 0.000114169586372941

Clinton | 9.92704482763833e-005

. . . . . . .

- Table-on-table overlays are possible with the ST_Intersection() function
- ST_Intersects(a,b) returns BOOLEAN
- ST_Intersection(a,b) returns GEOMETRY

ST_Intersects() = TRUE

ST_Intersection() =

Question: Create a new table of habitat areas clipped by the Chilliwack municipal boundary.

CREATETABLE ch_habitat_areas AS

SELECT

ST_Intersection(h.the_geom, m.the_geom)

AS intersection_geom,

ST_Area(h.the_geom) AS va_area,

h.*,

m.name AS municipality_name

FROM

bc_habitat_areas h,

bc_municipalities m

WHEREST_Intersects(h.the_geom, m.the_geom)

AND m.name = 'Chilliwack';

- Every geometry in PostGIS has a “spatial referencing identifier” or “SRID” attached to it.
- The SRID indicates the spatial reference system the geometry coordinates are in.

- View the SRID of geometries using the ST_SRID() function
SELECTST_SRID(the_geom)

FROM bc_roads LIMIT 1;

3005

- What’s 3005?
SELECT srtext

FROM spatial_ref_sys

WHERE srid = 3005;

PROJCS["NAD83 / BC Albers",…

- Ah, it’s “BC Albers”

- What’s 3005 again?
SELECT proj4text

FROM spatial_ref_sys

WHERE srid = 3005;

"+proj=aea +lat_1=50 +lat_2=58.5 +lat_0=45 +lon_0=-126 +x_0=1000000 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs“

- PROJ4 is coordinate re-projection library used by PostGIS

- Coordinate Re-projection is done using the ST_Transform() function
SELECT ST_AsEWKT(the_geom)

FROM bc_roads

LIMIT 1;

"SRID=3005;MULTILINESTRING((

1004687.04355194 594291.053764096,

1004729.74799931 594258.821943696,

1004808.0184134 594223.285878035,

1004864.93630072 594204.422638658,

1004900.50302171 594200.005856311

))"

- Coordinate Re-projection is done using the ST_Transform() function
SELECT ST_AsEWKT(ST_Transform(the_geom,4326))

FROM bc_roads

LIMIT 1;

"SRID=4326;MULTILINESTRING((

-125.9341 50.3640700000001,

-125.9335 50.36378,

-125.9324 50.36346,

-125.9316 50.36329,

-125.9311 50.36325

))"

- One of the utility applications that accompany a typical PostGIS installation is the pgsql2shp command-line utility program that can extract a table, view, or any custom query that contains a geometry column into a shapefile.
USAGE:

pgsql2shp [<options>] <database> [<schema>.]<table>

pgsql2shp [<options>] <database> <query>

- Clustering is a concept in PostgreSQL where one physically orders the rows in a table based on some index
- CLUSTER [VERBOSE]<tablename> [USING indexname]
- CLUSTER [VERBOSE]

- PostgreSQL ships with conservative settings
- Uses very little memory
- Runs on very limited hardware

- Database use cases play an important role in configuring certain parameters
- A read-only webserver with 100s of connections vs. a production system with a few users but high write activity.

- Disk access is slow, so higher performance can be gained by using more memory to cache data!
- shared_buffers
- effective_cache_size

- Sorting is faster in memory
- Increase work_mem

- Disk clean-up is faster with more memory
- Increase maintenance_work_mem

- Allocated per connection
- Also
- Increase checkpoint_segments
- Decrease random_page_cost

1. What is the perimeter of the municipality of Vancouver?

SELECTST_Perimeter(the_geom)

FROM bc_municipalities

WHERE name = 'Vancouver';

57321.7782018085

2. How many points were used to digitize the road named “Cartmell Rd”?

SELECTST_NPoints(the_geom)

FROM bc_roads

WHERE name = 'Cartmell Rd';

28

3. How many holes or interior rings does the habitat area with hab_id = 891 (“Marbled Murrelet”) have?

SELECTST_NumInteriorRings(the_geom)

FROM bc_habitat_areas

WHERE hab_id = 891;

5

4. What is the length in kilometers of all roads named ‘Main St’?

SELECTsum(ST_Length(the_geom)) / 1000

AS total_length

FROM bc_roads

WHERE name = 'Main St';

44.8489926296202

5. What is the total area of all municipalities in hectares?

SELECTsum(ST_Area(the_geom)) / 10000

AS total_length

FROM bc_municipalities;

1186482.22780945

6. What is the average road length (treat every entry in the bc_roads table as a separate road)?

SELECTavg(ST_Length(the_geom))

FROM bc_roads;

434.230644730819

1. How many roads are completely within the municipality of 'Hope‘?

SELECT count(*)

FROM bc_roads r,

bc_municipalities m

WHEREST_Within(r.the_geom, m.the_geom)

AND m.name = 'Hope';

280

2. What is the latitude of the most southerly hospital in the province?

(Hint: consider using a lat/long projection)

SELECT min(ST_Y(

ST_Transform(the_geom, 4269)

)) AS lat

FROM bc_hospitals;

48.4657953714625

3. The residents in the municipality of “Sicamous” should have a watchful eye out for what native wildlife animal?

SELECT b.name

FROM bc_municipalities a,

bc_habitat_areas b

WHERE a.name = 'Sicamous'

ANDST_Intersects(a.the_geom, b.the_geom);

Grizzly Bear

4a. According to the datasets, is there a hospital in “Sicamous”?

SELECT count(*)

FROM bc_municipalities a,

bc_hospitals b

WHERE a.name = 'Sicamous'

ANDST_Contains(a.the_geom, b.the_geom);

0

4b. Is there a pub?

SELECT count(*)

FROM bc_municipalities a,

bc_pubs b

WHERE a.name = 'Sicamous'

ANDST_Contains(a.the_geom, b.the_geom);

1

5. Extract the bc_municipalities polygonal table to a shapefile, except add a new attribute called “perimeter” populated accordingly.

C:\> pgsql2shp -f municipalities.shp -u postgres postgis "SELECT *, ST_Perimeter (the_geom) AS perimeter FROM bc_municipalities"

1. What is the name of the closest hospital from Sicamous and how far away is it?

SELECT a.name, b.name,

ST_Distance(a.the_geom, b.the_geom)

FROM bc_municipalities a,

bc_hospitals b

WHERE a.name = 'Sicamous'

ORDER BY ST_Distance ASC

LIMIT 1;

name | name | st_distance

---------+-------------------------------+------------

Sicamous | Shuswap Lake General Hospital | 22943.88318

2. What are the names of the pubs located within 100 meters from “Granville St”, located in downtown Vancouver?

SELECTDISTINCT c.name

FROM bc_municipalities a,

bc_roads b,

bc_pubs c

WHERE a.name = 'Vancouver'

AND b.name = 'Granville St'

AND ST_Contains(a.the_geom, b.the_geom)

AND ST_DWithin(b.the_geom, c.the_geom, 100)

ORDER BY c.name;

2. What are the names of the pubs located within 100 meters from “Granville St”, located in downtown Vancouver?

name

----------------------

Cecil Hotel

Dufferin Hotel

Fraser Arms Hotel

Howard Johnson Hotel

St. Helen's Hotel

St. Regis Hotel

The Lennox Pub

Yale Hotel

(8 rows)

3. What is the total length (in kilometers) of roads named “Douglas St” in Victoria?

(Hint: sum the intersected length)

SELECT sum(ST_Length(

ST_Intersection(r.the_geom, m.the_geom)

)) / 1000 AS sum

FROM bc_roads r,

bc_municipalities m

WHERE r.name = 'Douglas St'

AND m.name = 'Victoria'

ANDST_Intersects(r.the_geom, m.the_geom);

4.47269806771667

4. What road would you travel on in BC if you wanted to sight-see BC’s native “Spotted Owl”?

(Hint: try to find the name of the road with the longest intersecting length)

SELECT a.name, sum(ST_Length(

ST_Intersection(a.the_geom, b.the_geom)))

FROM bc_roads a, bc_habitat_areas b

WHERE b.name = 'Spotted Owl'

ANDST_Intersects(a.the_geom, b.the_geom)

GROUPBY a.name

ORDERBY sum DESC;

4. What road would you travel on in BC if you wanted to sight-see BC’s native “Spotted Owl”?

(Hint: try to find the name of the road with the longest intersecting length)

name | sum

-----------------------+------------------

Pemberton-Lillooet Rd | 25798.0268482952

Chilliwack Lake Rd | 4937.85768862766

Highway 1 | 4401.56275978918

W Paulsen Rd | 1411.2595495739

E Paulsen Rd | 1226.83009075469

Z005 | 277.194003115705

...

5. In the municipality of Kelowna, are the road segments named “Hillaby Ave” disjoint or continuous?

(Hint: consider merging the lines together)

SELECTST_NumGeometries(

ST_LineMerge(ST_Union(the_geom)))

FROM bc_roads

WHERE name = 'Hillaby Ave';

st_numgeometries

------------------

2

- Kevin [email protected]
- PostGIShttp://www.postgis.org

Download Presentation

Connecting to Server..