spatial databases: lecture 3 dt249 semester 2 2014-15 pat browne

Spatial Databases: Lecture 3Spatial Databases: Lecture 3DT249 DT249 Semester 2Semester 2

2014-15 Pat Browne2014-15 Pat Browne

OutlineOutline

We will look in more detail on what happens We will look in more detail on what happens when a spatial table is constructed in when a spatial table is constructed in PostgreSQL/PostGIS.PostgreSQL/PostGIS.

We will describe how construct a table for a We will describe how construct a table for a sub-set of the historical data setsub-set of the historical data set11..

We take a closer look at a range of OGC queries We take a closer look at a range of OGC queries that can be used in PostGISthat can be used in PostGIS22..

A reminder of viewing and querying using A reminder of viewing and querying using OpenJumpOpenJump

We will look at map accuracy. We will look at map accuracy.

PostgreSQL-PostGISPostgreSQL-PostGIS

An object-relational DBMS with PostGIS spatial extensions.An object-relational DBMS with PostGIS spatial extensions. Is completely Open Source Is completely Open Source Compliant with OGS’s Simple Features for SQLCompliant with OGS’s Simple Features for SQL Has a spaghetti-like spatial data model Has a spaghetti-like spatial data model Spatial indexing Spatial indexing Supports OGC types and PostgeSQL’s ‘native types’: point, line, Supports OGC types and PostgeSQL’s ‘native types’: point, line,

box, path, polygon, and circle geometric types box, path, polygon, and circle geometric types Topology is under development.Topology is under development. Can perform overlay function Can perform overlay function Simple features are based on 2D geometry with linear Simple features are based on 2D geometry with linear

interpolation between vertices.interpolation between vertices.

PostGIS levels of representationPostGIS levels of representation

PostGIS S/W componentsPostGIS S/W components PostGiS provides PostGiS provides Open Database Connectivity Open Database Connectivity ODBC connectivity. ODBC connectivity.

PostGIS includes extensions to the underlying PostgreSQL ODBC PostGIS includes extensions to the underlying PostgreSQL ODBC drivers which allow transparent access to GIS objects from PostGIS drivers which allow transparent access to GIS objects from PostGIS via the ODBC protocol. ODBC connectivity is part of the OGC via the ODBC protocol. ODBC connectivity is part of the OGC standard.standard.

PostGIS also provides Java Database Connectivity (JDBC), which PostGIS also provides Java Database Connectivity (JDBC), which is not part of the OGC standard. is not part of the OGC standard.

GiST (Generalized Search Tree) provides high speed spatial GiST (Generalized Search Tree) provides high speed spatial indexing.indexing.

PROJ.4 is an open source library that provides PROJ.4 is an open source library that provides coordinate coordinate reprojection reprojection to convert between geographic coordinate systems.to convert between geographic coordinate systems.

GEOS (Geometry Engine, Open Source) is a library used by GEOS (Geometry Engine, Open Source) is a library used by PostGIS to perform all the operations in the OpenGIS Simple PostGIS to perform all the operations in the OpenGIS Simple Features for SQL Specification. The GEOS library is used to provide Features for SQL Specification. The GEOS library is used to provide geometry tests (ST_Touches(), ST_Contains(), ST_Intersects()) and geometry tests (ST_Touches(), ST_Contains(), ST_Intersects()) and operations (ST_Buffer(), ST_Union(),ST_Intersection() operations (ST_Buffer(), ST_Union(),ST_Intersection() ST_Difference()) within PostGIS.ST_Difference()) within PostGIS.

PostGIS and OGC standardPostGIS and OGC standard

1.1. PostGIS implements and is compliant with the PostGIS implements and is compliant with the OGC’s SimpleOGC’s Simple Features for SQL standard.Features for SQL standard.

2.2. PostGIS supports all OGC types: Point, Line, PostGIS supports all OGC types: Point, Line, Polygon, MultiPoint, MultiLine, MultiPolygon, Polygon, MultiPoint, MultiLine, MultiPolygon, GeometryCollection and operations on those GeometryCollection and operations on those typestypes

3.3. PostGIS uses OGC well-known text format on PostGIS uses OGC well-known text format on the SQL command-line to represent GIS the SQL command-line to represent GIS features.features.

Creating a table.Creating a table.

The basic steps to create a new spatially The basic steps to create a new spatially enabled table are:enabled table are:

Create a table with the desired non-spatial Create a table with the desired non-spatial attributes.attributes.

Add a spatial column with as Add a spatial column with as PostGIS/OGC extension PostGIS/OGC extension AddGeometryColumnAddGeometryColumn

Insert the geometry with a SQL Insert the geometry with a SQL insert insert && selectselect statements. statements.

Creating a spatial tableCreating a spatial table

We can also create a spatial table from an existing table.We can also create a spatial table from an existing table. On the following slides we will describe how to make a On the following slides we will describe how to make a

table containing a subset of the historical data set from table containing a subset of the historical data set from National Monuments ServiceNational Monuments Service11. We will make a table with . We will make a table with the historical information for Dublin. We assume that the the historical information for Dublin. We assume that the countycounty22 table exists and that Dublin is a single region in table exists and that Dublin is a single region in the the countycounty table (Dublin consists of four regions). table (Dublin consists of four regions).

Note a system generated identified (Note a system generated identified (gidgid) is used as the ) is used as the primary key.primary key.

It is possible to include a geometry column at table It is possible to include a geometry column at table creation time, but the system would not generate creation time, but the system would not generate integrity constraints so we will stick with this method.integrity constraints so we will stick with this method.

Creating a spatial table, step 1Creating a spatial table, step 1

CREATE TABLE "public"."dublin_historical“CREATE TABLE "public"."dublin_historical“ (gid serial PRIMARY KEY,(gid serial PRIMARY KEY,"rmp_prop" int8,"rmp_prop" int8,"map_symbol" int8,"map_symbol" int8,"entity_id" varchar(7),"entity_id" varchar(7),"co_id" int8,"co_id" int8,"smr_val0" numeric,"smr_val0" numeric,"nat_grid_e" numeric,"nat_grid_e" numeric,"class_desc" varchar(255),"class_desc" varchar(255),"nat_grid_n" numeric,"nat_grid_n" numeric,"objectid" int8,"objectid" int8,"townlands" varchar(255),"townlands" varchar(255),"scope_n1" varchar(255),"scope_n1" varchar(255),"smrs" varchar(255));"smrs" varchar(255));


PostgreSQL/PostGIS will respond:PostgreSQL/PostGIS will respond: NOTICE: CREATE TABLE will create NOTICE: CREATE TABLE will create implicit sequence implicit sequence "dublin_historical_gid_seq" for "dublin_historical_gid_seq" for serial column serial column "dublin_historical.gid""dublin_historical.gid"

NOTICE: CREATE TABLE / PRIMARY KEY NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index will create implicit index "dublin_historical_pkey" for table "dublin_historical_pkey" for table "dublin_historical""dublin_historical"


Examine the table (\d):Examine the table (\d):

Table "public.dublin_historical"Table "public.dublin_historical"

Column | Type | ModifiersColumn | Type | Modifiers

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

gid | integer | not null default gid | integer | not null default nextval('dublin_historical_gid_seq'::regclass)nextval('dublin_historical_gid_seq'::regclass)

Reset of dataReset of data

Indexes:Indexes:"dublin_historical_pkey"PRIMARY KEY,btree (gid)"dublin_historical_pkey"PRIMARY KEY,btree (gid)

Creating a spatial table, step 2Creating a spatial table, step 2 Here we add the geometry column, PostGIS will Here we add the geometry column, PostGIS will

automatically generate integrity constraints.automatically generate integrity constraints. SELECT SELECT AddGeometryColumn('public','dublin_historiAddGeometryColumn('public','dublin_historical','the_geom','29900','POINT',2);cal','the_geom','29900','POINT',2);

Do not execute this command in the lab, Do not execute this command in the lab, this table this table already existsalready exists..

We use an version of the Irish National Grid with We use an version of the Irish National Grid with SRID SRID = 29900= 29900. The type of the geometry to be stored in the . The type of the geometry to be stored in the column must be included, in this case it is column must be included, in this case it is POINTPOINT..

This command accesses This command accesses geometry_columnsgeometry_columns system system table (details later).table (details later).


First system generated constraintFirst system generated constraint

ALTER TABLE ALTER TABLE dublin_historical dublin_historical DROP DROP CONSTRAINT CONSTRAINT enforce_dims_the_geom;enforce_dims_the_geom;

ALTER TABLE ALTER TABLE dublin_historicaldublin_historical

ADD CONSTRAINT ADD CONSTRAINT enforce_dims_the_geom CHECK enforce_dims_the_geom CHECK (ndims(the_geom) = 2);(ndims(the_geom) = 2);


Second system generated constraintSecond system generated constraint


ADD CONSTRAINT ADD CONSTRAINT enforce_geotype_the_geom CHECK enforce_geotype_the_geom CHECK (geometrytype(the_geom) = (geometrytype(the_geom) = ‘POINT'::text OR the_geom IS ‘POINT'::text OR the_geom IS NULL);NULL);


Third system generated constraintThird system generated constraint


ADD CONSTRAINT ADD CONSTRAINT enforce_srid_the_geom CHECK enforce_srid_the_geom CHECK (srid(the_geom) = 29900);(srid(the_geom) = 29900);


The Primary Constraint was created in The Primary Constraint was created in step1 step1

CONSTRAINT CONSTRAINT dublin_historicaldublin_historical_pkey _pkey PRIMARY KEY(gid);PRIMARY KEY(gid);


Next we insert the data from the all Ireland Next we insert the data from the all Ireland historicalhistorical table into the newly created table into the newly created dublin_historicaldublin_historical table. table.

Only data is contained in Dublin is inserted Only data is contained in Dublin is inserted into the new table. Check the OGC & into the new table. Check the OGC & PostGIS documentation on the PostGIS documentation on the containscontains predicate AKA ‘a spatial relationship predicate AKA ‘a spatial relationship function’.function’.

Creating a spatial table, step 3Creating a spatial table, step 3 INSERT INTO dublin_historical INSERT INTO dublin_historical (rmp_prop,map_symbol, entity_id, (rmp_prop,map_symbol, entity_id, co_id,smr_val0, co_id,smr_val0, nat_grid_e“ ,class_desc,nat_grid_n,objectinat_grid_e“ ,class_desc,nat_grid_n,objectid, townlands,scope_n1, smrs, the_geom) d, townlands,scope_n1, smrs, the_geom)

SELECT rmp_prop, map_symbol, entity_id, SELECT rmp_prop, map_symbol, entity_id, co_id,smr_val0, nat_grid_e, class_desc, co_id,smr_val0, nat_grid_e, class_desc, nat_grid_n, objectid, townlands, scope_n1, nat_grid_n, objectid, townlands, scope_n1, smrs, h.the_geom smrs, h.the_geom

FROM county c, historical h FROM county c, historical h WHERE contains(c.the_geom,h.the_geom) AND WHERE contains(c.the_geom,h.the_geom) AND c.name = 'Dublin';c.name = 'Dublin';

PostGIS system tables PostGIS system tables

The next few slides describe the built-in The next few slides describe the built-in PostGIS meta-tables that provide the PostGIS meta-tables that provide the spatial functionality. We only outline the spatial functionality. We only outline the main features. For further details, please main features. For further details, please see the PostGIS manual.see the PostGIS manual.

spatial_ref_sysspatial_ref_sys table table

Displaying a spherical earth on a flat surface Displaying a spherical earth on a flat surface requires a requires a projectionprojection. This table uses a standard . This table uses a standard numbering, called the EPSGnumbering, called the EPSG11, to describe , to describe various projections. Using PostgreSQL’s various projections. Using PostgreSQL’s expanded display we can examine the details for expanded display we can examine the details for a particular projection representing the Irish a particular projection representing the Irish National Grid:National Grid:

\x\xselect * from spatial_ref_sys where srid=29900;select * from spatial_ref_sys where srid=29900;

OGC – Metadata tablesOGC – Metadata tables

FOSS Relational Database and GeoDatabase Part III Marco Ciolli, Fabio Zottele :FOSS Relational Database and GeoDatabase Part III Marco Ciolli, Fabio Zottele :

Valid Geometry?Valid Geometry?

select IsValid(the_geom) from lakes;select IsValid(the_geom) from lakes;

NOTICE: Self-intersection at or near point 114275 271699NOTICE: Self-intersection at or near point 114275 271699NOTICE: Self-intersection at or near point 124552 240642NOTICE: Self-intersection at or near point 124552 240642NOTICE: Self-intersection at or near point 121283 305664NOTICE: Self-intersection at or near point 121283 305664 isvalidisvalid------------------ tt tt ff ff ff tt tt tt

Finding the centre point of a countyFinding the centre point of a county

To find the centre point of each county:To find the centre point of each county: select name, select name, asText(Centroid(the_geom)) from asText(Centroid(the_geom)) from county;county;

Finding a bounding boxFinding a bounding box

To find the bounding box of each county:To find the bounding box of each county: select name, select name, asText(envelope(the_geom)) from asText(envelope(the_geom)) from countycounty;;

OrOr select name,extent(the_geom) from select name,extent(the_geom) from county group by name;county group by name;

Must use ‘Must use ‘group bygroup by’ which can handle multiple ’ which can handle multiple polygons, also polygons, also extentextent returns the geometry as returns the geometry as text, so no need for asText.text, so no need for asText.

Finding the dimensions of a Finding the dimensions of a bounding boxbounding box

select name, extent(the_geom) from county select name, extent(the_geom) from county where name= 'Dublin County Borough' group where name= 'Dublin County Borough' group by nameby name;;

ReturnsReturns Dublin County Borough | BOX(307445.9 228925.0 ,325443…)Dublin County Borough | BOX(307445.9 228925.0 ,325443…)

Using the above, we can measure the Using the above, we can measure the dimensions of the bounding box:dimensions of the bounding box:

select distance(select distance(geomFromText(‘Point….)’),geomFromText(‘Point….)’),geomFromText(‘Point….)’));geomFromText(‘Point….)’));

Point in Bounding BoxPoint in Bounding Box

We can use a non-OGC function We can use a non-OGC function &&&& to find to find objects. objects.

SELECT name FROM county where SELECT name FROM county where GeomFromText('POINT(309612.0 GeomFromText('POINT(309612.0 233192.0)', 29900) && the_geom; 233192.0)', 29900) && the_geom;

See result on next slide.See result on next slide. When constructing a query it is important to When constructing a query it is important to

remember that only the bounding-box-based remember that only the bounding-box-based operators such as operators such as &&&& can take advantage of the can take advantage of the PostgreSQL’s GiST spatial index.PostgreSQL’s GiST spatial index.

Result Point in Bounding BoxResult Point in Bounding Box

SELECT name, asbinary(the_geom) FROM county WHERE GeomFromText('POINT(309612.0 233192.0)', 29900) && the_geom;


SELECT asbinary(a.the_geom), a.name FROM county a, county bWHERE (b.the_geom && a.the_geom ) and b.name like 'Meath';

Find things near a pointFind things near a point select townlands,class_desc,scope_n1,

asbinary(the_geom) from dublin_historical WHERE distance(the_geom, GeomFromText('POINT(309612.0 233192.0)', 29900)) < 100;

When we submit such a query we should have some idea of the type of data in the result set. To find out the expected return type use PostGIS manual or the OGC standard for SQL.

Find things near a pointFind things near a point

SELECT c.name, h.townlands

FROM county AS c,dublin_historical AS h WHERE distance(h.the_geom, GeomFromText('POINT(317431.0 231704.0)', 29900)) < 1000 and c. Name = 'Dublin County Borough';

Do we know the type of data in the result set here?

Find things near a pointFind things near a point

SELECT c.name, h.townlands

FROM county AS c,

dublin_historical AS h

WHERE st_dwithinst_dwithin(h.the_geom, PointFromText('POINT(317431.0 231704.0)', 29900),1000) and c. Name = 'Dublin County Borough';

Similar to previous query but using st_dwithinst_dwithin.

Find things near a pointFind things near a point SELECT gid,townlands, distance(the_geom, GeomFromText('POINT(317431.0 231704.0)',29900))

FROM dublin_historical WHERE st_dwithin(the_geom, GeomFromText('POINT(317431.0 231704.0)',29900),500)

ORDER BY distance(the_geom, GeomFromText('POINT(317431.0 231704.0)',29900))

LIMIT 100;

Finding the largest county in Finding the largest county in IrelandIreland

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

Leaving out Northern Ireland SELECT name, area(the_geom)/10000 AS hectares FROM county

WHERE name != 'Northern Ireland‘

ORDER BY hectares DESC LIMIT 1;

Stored and calculated areasStored and calculated areas

Area in square KMs SELECT name, SELECT name, area(the_geom)/1000000 AS area(the_geom)/1000000 AS Calculated, area_km2 AreaStored Calculated, area_km2 AreaStored FROM county ;FROM county ;

What is the length of roads fully contained within Dublin County

Borough?SELECT c.name,

sum(length(r.the_geom))/1000 as roads_km 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 = 'Dublin County Borough'

GROUP BY c.name

ORDER BY roads_km;

What historical objects are near Dublin roads?

SELECT townlands FROM dublin_historical h, roads r WHERE

distance(h.the_geom,r.the_geom) < 200;

Overlays Overlays

We should distinguish the overlay We should distinguish the overlay operationoperation and and teststests for overlap. The OGCSFSQL use two for overlap. The OGCSFSQL use two similar keyword. Table-on-table overlays are similar keyword. Table-on-table overlays are possible with the ST_Intersection() possible with the ST_Intersection() functionfunction ST_Intersects(a,b) returns BOOLEAN ST_Intersects(a,b) returns BOOLEAN ST_Intersection(a,b) returns GEOMETRY ST_Intersection(a,b) returns GEOMETRY

ST_Intersects(a,b)=TRUE | FALSE

ST_Intersection()=GEOMETRY

Efficiency of SearchEfficiency of Search11

It is expensive to process the exact geometry of an object.Therefore approximations such as bounding boxes (BB) or a convex s (CH) are used to help to examine candidate objects and decided whether a candidate fulfils the query or not. The are used as ‘geometric filter’

Efficiency of SearchEfficiency of Search11

Network QueriesNetwork Queries

In order to execute network queries, we In order to execute network queries, we need to augment the spatial information need to augment the spatial information used in the OGC Simple Features for SQL used in the OGC Simple Features for SQL standard. We will use pgRoutingstandard. We will use pgRouting11..

Network QueriesNetwork Queries11

Shortest Path from Dublin to Waterford

What two houses within 500 meters of the What two houses within 500 meters of the Chester Beatty Library have the most Chester Beatty Library have the most

residents?residents? SELECT b1.residentsSELECT b1.residents FROM buildings_geodir b1, buildings_geodir b2FROM buildings_geodir b1, buildings_geodir b2 WHERE WHERE b2.name = 'CHESTER BEATTY LIBRARY' andb2.name = 'CHESTER BEATTY LIBRARY' and ST_DWithin(b2.the_geom,b1.the_geom, 500) ST_DWithin(b2.the_geom,b1.the_geom, 500)

ORDER BY residents DESC LIMIT 2;ORDER BY residents DESC LIMIT 2; Note residents column will need to be added to Note residents column will need to be added to

the the buildings_geodirbuildings_geodir table table

Counties that have exactly 1 neighbour

SELECT c1.name FROM county c1, county c2 WHERE touches(c1.the_geom,

c2.the_geom) = 'TRUE' GROUP BY c1.name HAVING count(c2.name) =1;

Proportions

SELECT saps_label,primary_degree10_4,(male1_1 + female1_1) SELECT saps_label,primary_degree10_4,(male1_1 + female1_1) as Pop,as Pop,

((cast (primary_degree10_4 as float)) / (cast ((male1_1 + ((cast (primary_degree10_4 as float)) / (cast ((male1_1 + female1_1) as float)) * 100) as Grad_percentfemale1_1) as float)) * 100) as Grad_percent

FROM dublin_edsFROM dublin_edsWHERE primary_degree10_4 IS NOT NULLWHERE primary_degree10_4 IS NOT NULLORDER BY Grad_percentORDER BY Grad_percent DESC LIMIT 1;DESC LIMIT 1; saps_label | primary_degree10_4 | pop | grad_percentsaps_label | primary_degree10_4 | pop | grad_percent------------------------------+-----------------------------+------+------------------------------------------------+-----------------------------+------+------------------ 130 Pembroke West A | 455 | 4262 | 10.6757390896293130 Pembroke West A | 455 | 4262 | 10.6757390896293(1 row)(1 row)

Note table & column names may differ.Check with \d tableName

AccuracyAccuracy

You should calculate the area of county You should calculate the area of county Meath and compare the result to the area Meath and compare the result to the area stored in the stored in the county county table, you should table, you should have get two different figures for the area have get two different figures for the area of Meath. Which area is correct? To of Meath. Which area is correct? To answer this question we would need to answer this question we would need to know the accuracy of the area stored in know the accuracy of the area stored in the database and the accuracy the map the database and the accuracy the map that we used to calculate the area.that we used to calculate the area.

AccuracyAccuracy

Obviously no spatial database or GIS can Obviously no spatial database or GIS can increase the positional accuracy of a increase the positional accuracy of a spatial dataset. The accuracy does not spatial dataset. The accuracy does not change as the viewing scale changes.change as the viewing scale changes.

So we need to know the scale of the So we need to know the scale of the original survey and the expected accuracy original survey and the expected accuracy at at thatthat scale. scale.

AccuracyAccuracy

The accuracy of a The accuracy of a mapmap is dependent on is dependent on the differences between the the differences between the true positiontrue position of features and their of features and their representative representative position in the mapposition in the map. .

To find the To find the truetrue position requires highly position requires highly accurate devices such as industrial grade accurate devices such as industrial grade Global Positioning Systems (GPS) and Global Positioning Systems (GPS) and sophisticated mathematical software.sophisticated mathematical software.

AccuracyAccuracy

A common of way of defining positional A common of way of defining positional accuracy for maps is to place limits on the root accuracy for maps is to place limits on the root mean square error (RMSE) for individual mean square error (RMSE) for individual position components (the X,Y and possibly Z i.e. position components (the X,Y and possibly Z i.e. height)height)

The RMSE is derived from the square root of the The RMSE is derived from the square root of the average of the squared discrepancies when average of the squared discrepancies when compared to a higher level independent survey. compared to a higher level independent survey. The RMSE is normally defined in terms of The RMSE is normally defined in terms of ground scale errors (e.g. +- one metre).ground scale errors (e.g. +- one metre).

A Rough Guide to AccuracyA Rough Guide to Accuracy

ScaleScale Expected RMSEExpected RMSE

1:10001:1000 RMSE < 0.5 metresRMSE < 0.5 metres

1:25001:2500 0.5 < RMSE < 20.5 < RMSE < 2

1:10,0001:10,000 4 < RMSE < 54 < RMSE < 5

1:250,000 1:250,000 100 < RMSE < 120100 < RMSE < 120

Accuracy Accuracy

RMSE can be viewed as RMSE can be viewed as oneone criteria for criteria for positional accuracy. It is measured with respect positional accuracy. It is measured with respect to some more precise truth (often GPS). RMSE to some more precise truth (often GPS). RMSE is a measure of is a measure of absolute accuracyabsolute accuracy, with respect , with respect to a more precise framework.to a more precise framework.

Together with an acceptable RMSE a map Together with an acceptable RMSE a map must be consistent within itself. That is the must be consistent within itself. That is the various components must various components must fit togetherfit together. This is a . This is a measure of measure of relative accuracyrelative accuracy..

Accuracy Accuracy

If data has a nominal scale of say 1:250,000, it still may be more precise than the rough guidelines on slide 51 would indicate.

For example it could be derived from more accurate maps e.g. 1:50,000.

Without meta-data it is difficult for humans or computers to know a map’s accuracy.

Accuracy Accuracy

The following table shows the expected The following table shows the expected absolute and relative accuracy values for absolute and relative accuracy values for well defined points within each accuracy well defined points within each accuracy category. The relative values apply up to category. The relative values apply up to the stated maximum measured distances the stated maximum measured distances quoted in the table.quoted in the table.

Accuracy Accuracy

From The British Ordnance Survey (OSGB)

SQLSQL

create table staff (employee text,dept text,salary int4,

PRIMARY KEY (employee, dept));

;

SQLSQL

SELECT dept,employee,salary

FROM staff AS p1

WHERE salary >

(SELECT avg(salary) FROM staff as p2 WHERE p2.dept = p1.dept)

ORDER by dept;

SQLSQL

SELECT employee, dept, salary

FROM staff AS p1

WHERE salary >

ANY (SELECT salary FROM staff as p2 WHERE p2.dept = p1.dept)

ORDER BY dept;

spatial databases: lecture 3 dt249 semester 2 2014-15 pat browne

Documents