INTRODUCTION TO POSTGIS

ULANBEK TURDUKULOV
Spatial Objects for PostgreSQL

 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
Why PostGIS?

 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
QGIS PostGIS Web
MapGuide Client
GRASS
uDig
ArcGIS
Who is using PostGIS?

Lots of people …

 1400 mailing list members

 14K visits/month
10K visitors/month
 100 source code downloads per day
100 windows binary downloads per day
 970K Google search results
 Great Google trends …
Who is using PostGIS?

 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.
Who is using PostGIS?

 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.
Who is using PostGIS?

 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.
Who is using PostGIS?

 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.
Workshop Summary

 Installation (PostgreSQL and PostGIS)

 System Configuration
 Creating and Loading Data
 Creating Indexes
 Query Tuning
 Spatial SQL
2.1 – PostgreSQL Installation

 Windows Installer
 PostgreSQL 8.2.4
 PgAdmin III
 Install as Service to allow automatic database start on boot
 Demonstrate Installation …
2.1 – PostgreSQL Installation
2.1 – PostgreSQL Installation

 Directories created during installation:

 \bin - Executables
 \include - Include files for compilation
 \lib - DLL shared library files
 \share - Extensions
2.1 – PostgreSQL Installation

 Tools included with the install:

 PgAdmin III
 psql Command Line
2.2 – PostGIS Installation
2.2 – PostGIS Installation

 PostGIS bundled with PostgreSQL lags the current release by a few

versions
 Current release is 1.5.2
(1.1.x is bundled with PostgreSQL)
 Our Lab
 Already has PostgreSQL installed
 Already has PostGIS installed
 We need to create an empty spatial database
2.2 – PostGIS Installation

 A PostgreSQL instance has one software

version and one network port (5432)
 An instance contains many databases
 Connection string specifies a database
“-h host –U user –d database –p password”
 A database contains many schemas
 public
 A schema contains many tables
 public.geometry_columns
 A table contains many tuples
2.3 – Spatially Enable PostgreSQL

 Connect to the instance

 User “postgres”, password “postgres”
 Create a new database in our local instance
 Select “template_postgis” as the template
 Connect to the database
 Check for the spatial system tables
 spatial_ref_sys
 geometry_columns
2.3 – Spatially Enable PostgreSQL
2.3.1 – Without template_postgis

 Create a new database

 Select “template1” as the template
 Connect to the database
 Load/run the PostGIS extension (lwpostgis.sql)
 Load/run the PostGIS spatial reference systems (spatial_ref_sys.sql)
2.3.1 – Without template_postgis

 Run PG Admin III …

3 Using PostGIS
3.1 – Simple Spatial SQL

“Manually” create geometries

create table points (pt geometry, name varchar);

insert into points values ('POINT(0 0)', 'Origin');
insert into points values ('POINT(5 0)', 'X Axis');
insert into points values ('POINT(0 5)', 'Y Axis');
select name, ST_AsText(pt),
ST_Distance(pt, 'POINT(5 5)') from points;
 3.1 – Simple Spatial SQL

 name | astext | distance

 --------- + -------------- + ------------------
 Origin | POINT(0 0) | 7.071067
 X Axis | POINT(5 0) | 5
 Y Axis | POINT(0 5) | 5

 (3 rows)
3.2 OGC Metadata Tables

 GEOMETRY_COLUMNS
 F_TABLE_CATALOG = ‘’
 F_TABLE_SCHEMA = ‘public’
 F_TABLE_NAME = ‘bc_roads’
 F_GEOMETRY_COLUMN = ‘the_geom’
 COORD_DIMENSION = 2
 SRID = 3005
 TYPE = ‘MULTILINESTRING’
3.2 OGC Metadata Tables

 SPATIAL_REF_SYS
 SRID = 3005
 AUTH_NAME = ‘EPSG’
 AUTH_SRID = 3005
 SRTEXT = ‘PROJCS["NAD83 / BC Albers”, … ]’
 PROJ4TEXT = ‘+proj=aea …’
3.3 – Loading Shape Files

 Shape File
 .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
3.3 – Loading Shape Files

 shp2pgsql [opts] shapefile tablename

 Read in .shp file
 Write out .sql file
 Load .sql file into PostgreSQL
 using psql
 using PgAdmin
3.3 – Loading Shape Files

notepad bc_pubs.sql
3.3.1 – Command Line Options

-D = Use “dump” format

-i = Do not use “bigint”, even for long numbers
-s <#> = Use this SRID
-W <encoding> = Use this character encoding
-a = Run in append mode
3.4 Viewing Data in PostGIS

 Quick desktop viewing options

 uDig
 QGIS
 gvSIG
 CadCorp SIS*
 FME Viewer*
 Web based application options
 MapGuide
 Mapserver
 Geoserver
3.4 Viewing Data in PostGIS

 Program Files  uDig 1.1-RC11  uDig

3.4 Viewing Data in PostGIS

 File  New  Layer

3.4 Viewing Data in PostGIS
3.4 Viewing Data in PostGIS
3.4 Viewing Data in PostGIS
3.5.1 Creating Spatial Indexes

PostGIS implements R-Tree indexes on top of the GiST indexing

system
Organizes data into nesting rectangles for fast searching

 CREATE INDEX bc_roads_gidx ON bc_roads USING GIST

(the_geom);

 (now create indexes on the other tables

too…)
3.5.2 Using Spatial Indexes

Indexes are brought into play when PostgreSQL recognizes an

“operator” in the SQL statement. For example:
SELECT * FROM mytable
WHERE myname = ‘Paul’
= is an operator
SELECT * FROM mytable
WHERE age < 2
< is an operator
3.5.2 Using Spatial Indexes

 Spatial index operator is “&&”

 “Bounding Boxes Interact”

A && B = TRUE
A && B = FALSE
3.5.2 Using Spatial Indexes

 Bounding Boxes are not enough!

A && B = TRUE
_ST_Intersects(A && B) = FALSE

 Two pass system required

 Use bounding boxes to reduce candidates
 Use real topological test to get final answer
3.5.2 Using Spatial Indexes

ST_Intersects(A,B)
A && B AND _ST_Intersects(A,B)
3.5.2 Using Spatial Indexes

A && B
3.5.2 Using Spatial Indexes

A && B
3.5.2 Using Spatial Indexes

_ST_Intersects(A,B)
3.5.2 - Using Spatial Indexes

 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
3.5.3 - Spatial Index Test

 Run query with un-indexed function

 SELECT gid, name FROM bc_roads
WHERE _ST_Crosses( the_geom,
ST_GeomFromText(‘..’, 3005) );
 Run query with indexed function
 SELECT gid, name FROM bc_roads
WHERE ST_Crosses( the_geom,
ST_GeomFromText(‘..’, 3005) );
 Note speed difference
3.5.4 - Indexes and Query Plans

 Run queries using the “Explain”

button instead of the “Run”
button
 Note the graphical display of how the database
is using indexes
 Click on the icons to get more information
about each step of the query
 Remember to try this later when we start
performing more complicated queries
3.5.4 - Indexes and Query Plans
3.5.4 - Indexes and Query Plans
3.5.5 - 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
3.6 - PostgreSQL Optimization

 PostgreSQL settings are controlled by the postgresql.conf file

 Programs =>
PostgreSQL 8.2 =>
Configuration Files =>
Edit postgresql.conf
 Some settings require a restart
 Some can be changed at run-time using the SET command!
3.6 - PostgreSQL Optimization

 PostgreSQL ships with conservative settings

 Uses very little memory
 Runs on very limited hardware
 Disk access is slow, so higher performance can be gained by using more
memory to cache data!
 Increase shared_buffers
 Physical RAM - OS needs * 75%
3.6 - PostgreSQL Optimization

 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 wal_buffers
 Increase checkpoint_segments
 Decrease random_page_cost
3.7 - Spatial Analysis in SQL

 Get out the data dictionary, flip to the back:

 ST_Length(geometry) returns Float
 ST_Area(geometry) returns Float
 ST_AsText(geometry) returns Text
 ST_GeomFromText(text) returns Geometry
3.7 - Spatial Analysis in SQL

 ST_Intersects(A, B)
3.7 - Spatial Analysis in SQL

 ST_Contains(A, B)
 ST_Within(B, A)
3.7 - Spatial Analysis in SQL

 ST_Touches(A, B)
3.7 - Spatial Analysis in SQL

 ST_Crosses(A, B)
3.7 - Spatial Analysis in SQL

 ST_DWithin(A, B, D)

D
3.7 - Spatial Analysis in SQL

 What is the total area of all landslides in kilometers (using Guass Kruger
projection?
 SELECT
Sum( ST_Length( the_geom ) ) / 1000
AS km_roads
FROM bc_roads;
3.7 - Spatial Analysis in SQL

 What is the largest municipality in the province, by area?

 SELECT
name,
ST_Area(the_geom)/10000
AS hectares
FROM bc_municipality
ORDER BY hectares DESC
LIMIT 1;
3.8 - Basic Exercises

 What is the perimeter of the municipality of ‘VANCOUVER?’

 Hint – name = ‘VANCOUVER’
 SELECT ST_Perimeter(the_geom)
FROM bc_municipality
WHERE name = 'VANCOUVER';
3.8 - Basic Exercises

 What is the total area of all voting areas in hectares?

 Hint – 1 hectare = 10,000 m2
 SELECT Sum(ST_Area(the_geom))/10000
AS hectares
FROM bc_voting_areas;
3.8 - Basic Exercises

 What is the total area of all voting areas with more than 100 voters in
them?
 SELECT Sum(ST_Area(the_geom))/10000 AS hectares
FROM bc_voting_areas
WHERE vtotal > 100;
3.8 - Basic Exercises

 What is the length in kilometers of all roads named ‘Douglas St’?

 SELECT Sum(ST_Length(the_geom))/1000 AS kilometers
FROM bc_roads
WHERE name = 'Douglas St';
4.1 - Data Integrity

 Spatial data algorithms embed expectations of structure

 PostGIS expects data to conform to the OGC Simple Features for SQL
standard
 Polygon rings don’t cross other rings
or self-intersect
 Multi-polygons are non-overlapping
 Interior rings can touch exteriors, but only at one point
 Ring orientation is unimportant
4.1 - Data Integrity

Valid Invalid

SELECT gid
FROM bc_voting_areas
WHERE
NOT ST_IsValid(the_geom);
4.1 - Data Integrity

 4897 is INVALID!
 SELECT
ST_IsValid(ST_Buffer(the_geom, 0.0))
FROM bc_voting_areas
WHERE gid = 4897;
 UPDATE bc_voting_areas
SET
the_geom = ST_Buffer(the_geom, 0.0)
WHERE gid = 4897;
4.2 - Distance Queries

 How many BC Unity Party supporters live within 2 kilometers of the

‘Tabor Arms’ pub?
 SELECT ST_AsText(the_geom)
FROM bc_pubs
WHERE name ILIKE ‘Tabor Arms%’;
4.2 - Distance Queries

 SELECT Sum(unity) AS unity_voters

FROM bc_voting_areas
WHERE
ST_Distance(
the_geom,
ST_GeomFromText(‘POINT(…)’, 3005),
) < 2000;
4.2 - Distance Queries

 SELECT Sum(unity) AS unity_voters

FROM bc_voting_areas
WHERE
ST_DWithin(
the_geom,
ST_GeomFromText(‘POINT(…)’, 3005),
2000
);
4.2 - Distance Queries

 ST_DWithin(geometry, geometry, float)

 SELECT
$1 && ST_Expand($2, $3) AND
$2 && ST_Expand($1, $3) AND
ST_Distance($1, $2) < $3
4.2.1 Distance Not Buffer

 “What pubs fall within a 1km buffer of the

roads?”
 You do not need to buffer to answer this
question, because the return value is not a
buffered road geometry, it is the pub.
 SELECT p.name
FROM bc_pubs p, bc_roads r
WHERE
ST_DWithin(p.the_geom, r.the_geom, 1000)
4.2.1 Distance Not Buffer

 This is the WRONG way to do it:

 SELECT p.name
FROM bc_pubs p, bc_roads r
WHERE
ST_Contains(
ST_Buffer(r.the_geom, 1000),
p.the_geom
);
4.3 - Spatial Joins

 Standard joins use a common key

 SELECT a.var1, b.var2
FROM a, b
WHERE a.id = b.id
 Spatial joins use the “universal key”, location
 SELECT a.var1, b.var2
FROM a, b
WHERE ST_Intersects(a.geom, b.geom)
4.3 - Spatial Joins

 “Tabor Arms Pub” distance query as join

 SELECT Sum(v.unity) AS unity_voters
FROM
bc_voting_areas v,
bc_pubs p
WHERE
ST_DWithin(v.the_geom, p.the_geom, 2000)
AND
p.name ILIKE 'Tabor Arms%';
4.3 - Spatial Joins

 Where are all of the pubs located within 250 meters of

a hospital?
 SELECT bc_hospitals.name, bc_pubs.name
FROM
bc_hospitals,
bc_pubs
WHERE
ST_DWithin(
bc_hospitals.the_geom,
bc_pubs.the_geom,
250
);
4.3 - Spatial Joins

 Summarize the 2000 provincial election by municipality

 SELECT
m.name,
Sum(v.ndp) AS ndp …
Sum(v.vtotal) AS total
FROM
bc_voting_areas v,
bc_municipality m
WHERE ST_Intersects(v.the_geom, m.the_geom)
GROUP BY m.name
ORDER BY m.name;
4.4 - Overlays

 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() =
4.4 - Overlays

 Create a new table containing all voting areas that fall within
PRINCE GEORGE clipped to that municipality’s bounds …
4.4 - Overlays
4.4 - Overlays

 CREATE TABLE pg_voting_areas AS

SELECT
ST_Intersection(v.the_geom, m.the_geom)
AS intersection_geom,
ST_Area(v.the_geom) AS va_area,
v.*,
m.name
FROM
bc_voting_areas v,
bc_municipality m
WHERE
ST_Intersects(v.the_geom, m.the_geom) AND
m.name = ‘PRINCE GEORGE’;
4.4 - Overlays

• A quick check …
– Area of overlay should be same as area of
clipping polygon
• SELECT Sum(ST_Area(intersection_geom))
FROM pg_voting_areas;
• SELECT ST_Area(the_geom)
FROM bc_municipality
WHERE name = ‘PRINCE GEORGE’;
4.5 - Coordinate Projection

• View the SRID of geometries using the

ST_SRID() function
• SELECT ST_SRID(the_geom)
FROM bc_roads
LIMIT 1;
• What’s “3005”?
• SELECT srtext
FROM spatial_ref_sys
WHERE srid = 3005;
• Ah, it’s “BC Albers”
4.5 - Coordinate Projection

• PROJCS[“NAD83 / BC Albers",
GEOGCS["NAD83",
DATUM["North_American_Datum_1983",
SPHEROID["GRS 1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],
UNIT["degree",0.01745329251994328],
AUTHORITY["EPSG","4269"]],
PROJECTION["Albers_Conic_Equal_Area"],
PARAMETER["latitude_of_center",45],
PARAMETER["longitude_of_center",-126],
PARAMETER["standard_parallel_1",50],
PARAMETER["standard_parallel_2",58.5],
PARAMETER["false_easting",1000000],
PARAMETER["false_northing",0],
UNIT["metre",1],
AUTHORITY["EPSG","3005"]]
4.5 - Coordinate Projection

• What’s “3005” again?

• SELECT proj4text
FROM spatial_ref_sys
WHERE srid = 3005;
• +proj=aea +ellps=GRS80 +datum=NAD83
+lat_0=45.0 +lon_0=-126.0
+lat_1=50.0 +lat_2=58.5
+x_0=1000000 +y_0=0
• PROJ4 is coordinate re-projection library used by
PostGIS
4.5 - Coordinate Projection

• Coordinate Re-projection is done using the

ST_Transform() function
• SELECT ST_AsText(the_geom)
FROM bc_roads
LIMIT 1;
• SELECT
ST_AsText(
ST_Transform(the_geom, 4326) )
FROM bc_roads
LIMIT 1;
4.5 - Coordinate Projection

MULTILINESTRING((
1004687.04355194 594291.053764096,
1004729.74799931 594258.821943696))

ST_Transform(the_geom)

MULTILINESTRING((
-125.9341 50.3640700000001,
-125.9335 50.36378))
4.6 - Advanced Exercises

 What is the length in kilometers of

‘Douglas St’ in ‘VICTORIA’?
 SELECT
Sum(ST_Length(r.the_geom))/1000
AS kilometers
FROM
bc_roads r,
bc_municipality m
WHERE
ST_Contains(m.the_geom, r.the_geom) AND
r.name = 'Douglas St' AND
m.name = 'VICTORIA';
4.6 - Advanced Exercises

 What two pubs have the most Green Party supporters within 500
meters of them?
 SELECT
p.name, p.city,
Sum(v.green) AS greens
FROM
bc_pubs p,
bc_voting_areas v
WHERE
ST_DWithin(v.the_geom, p.the_geom, 500)
GROUP BY p.name, p.city
ORDER BY greens DESC LIMIT 2;
4.6 - Advanced Exercises

 What is the latitude of the most southerly hospital in the province?

 Hint – The SRID of lat/lon is 4326
 SELECT ST_Y(ST_Transform(the_geom,4326))
AS latitude
FROM bc_hospitals
ORDER BY latitude ASC
LIMIT 1;
4.6 - Advanced Exercises

 What were the percentage NDP and Liberal

vote within the city limits of Prince George in
the 2000 provincial election?
 SELECT
100*Sum(v.ndp)/Sum(v.vtotal) AS ndp,
100*Sum(v.liberal)/Sum(v.vtotal) AS liberal
FROM
bc_voting_areas v,
bc_municipality m
WHERE
ST_Contains(m.the_geom, v.the_geom) AND
m.name = 'PRINCE GEORGE';
4.6 - Advanced Exercises

 What is the largest voting area polygon that has a hole?

 Hint – A hole implies more than one ring
 SELECT
gid,
id,
ST_Area(the_geom) AS area
FROM bc_voting_areas
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC
LIMIT 1;
4.6 - Advanced Exercises

 How many NDP voters live within 50 meters of

‘Simcoe St’ in Victoria?
 SELECT
Sum(v.ndp) AS ndp
FROM
bc_voting_areas v, bc_municipality m,
bc_roads r
WHERE
ST_DWithin(r.the_geom, v.the_geom, 50) AND
ST_Contains(m.the_geom, r.the_geom) AND
r.name = 'Simcoe St' AND
m.name = 'VICTORIA';
5 Mapserver & PostGIS

http://localhost/postgis

Start the application…

c:\ms4w\apps\postgis\htdocs\template.html
5.1 Basic Configuration

LAYER
CONNECTIONTYPE postgis
NAME "bc_voting_areas”
CONNECTION "user=postgres
password=postgres dbname=postgis
host=localhost"
DATA "the_geom FROM bc_voting_areas"
STATUS ON
TYPE POLYGON
CLASS
COLOR 255 255 200
END
END
5.2 Filters and Expressions

• CONNECTIONTYPE, CONNECTION and

DATA elements have unique PostGIS
forms
• Most other Mapserver map file
elements are the same using PostGIS
• There are two exceptions:
– FILTER
– EXPRESSION
5.2 Filters and Expressions

• For PostGIS connections, FILTER uses

SQL “where clause” syntax instead of
Mapserver expression syntax.
• FILTER “type = 6”
• FILTER “name ilike ‘Dunbar%’”
• FILTER “age > 6 and height < 4”
5.3 Mapserver with SQL

• The Mapserver DATA statement can

also use arbitrary SQL to compose
spatial result sets for mapping.
• Useful when your data does not quite
have the information you want to map,
but it can be calculated or derived.
5.3 Mapserver with SQL

• “Map the % voter turnout.”

DATA "the_geom from
(SELECT gid, the_geom, (case
when vregist = 0 then 0.0
else vtotal::real /
vregist::real end) AS percent
FROM bc_voting_areas)
as foo using srid=3005
using unique gid"
5.3 Mapserver with SQL

• “Label the ‘important’ roads.”

DATA "the_geom from (SELECT name,
Sum(Length(the_geom)) AS length,
Collect(GeometryN(the_geom,1))
AS the_geom FROM bc_roads WHERE
the_geom && setsrid(!BOX!,3005)
GROUP BY name
ORDER BY length DESC
LIMIT 5) as foo
using SRID=3005, using unique
name"
5.4 - Dynamic SQL

• Take advantage of Mapserver

%variable% substitution in CGI program
• Fill out SQL values on the fly to create
new query result sets for mapping
• Very powerful tool, very easy to make
use of
5.4 - Dynamic SQL

DATA "the_geom from

(SELECT the_geom,gid,
ST_Distance(the_geom,
SetSRID(MakePoint(
%mx% + %img.x% * %mw% / %iw%,

%my% - %img.y% * %mh% / %ih

%), 3005)) AS dist FROM
bc_roads)
as foo using srid=3005 using
unique gid
5.4 - Dynamic SQL
5.5 - Very Dynamic SQL

• Take the SQL insertion trick to its

logical conclusion
• Provide a way to visualize arbitrary
SQL queries on a map on the fly
• Using only the Mapserver CGI and a
template based interface
• See the qlyr LAYER entry at the end of
the example map file
5.5 - Very Dynamic SQL

• DATA statement
DATA "the_geom from (%sql%) as
foo using SRID=3005 using unique
gid"
• Template line
<textarea name="sql" rows=3
cols=50>[sql]</textarea>
• Only trick is choice of Mapserver LAYER
“TYPE”
– Arbitrary SQL can return any geometry type
5.5 - Very Dynamic SQL

Enter a SQL
query and see it
on the map
outlined in red
5.5 - Very Dynamic SQL

• Try some examples:

• All roads that start with ‘B’
• SELECT gid, the_geom
FROM bc_roads
WHERE name LIKE ‘B%’
• All roads that are longer than 100 metres
• SELECT gid, the_geom
FROM bc_roads
WHERE ST_Length(the_geom) > 100
5.5 - Very Dynamic SQL

• Buffered voting areas with Liberal

vote > 100
• SELECT
gid,
ST_Buffer(the_geom,250) AS
the_geom
FROM bc_voting_areas
WHERE liberal > 100
5.5 - Very Dynamic SQL

• All roads inside the city of Victoria

• SELECT r.gid, r.the_geom
FROM
bc_roads r,
bc_municipality m
WHERE
ST_Contains(m.the_geom,
r.the_geom)
AND m.name = ‘VICTORIA’
Thank You …

Ulanbek Turdukulov
turdukulov@itc.nl
PostGIS
http://www.postgis.org
Mapserver
http://mapserver.gis.umn.edu