PostGIS

1
PostGIS
PHP scripts create the map object and access the
database.
Web Browser
PHP
Defines PHP classes for GIS objects.
Web Server
PHP/MapScript
Provides functions to manage geospatial data
MapServer
PostGIS
Generates map images.
PostgreSQLDatabase
Stores geospatial and business data.
2
Geometric Object Support by PostgreSQL

• Geometry objects are supported.
• However, no support for spatial references.
• Spatial indexes are supported by R-trees , which
  are built with GiST.
• GiST stands for Generalized Search Tree, which is
  a balanced, tree-structured access method that
  acts as a template for implementing other
  indexing mechanisms such as B-trees and R-trees.
  

3
Create Tables for Waterbodies

• create table water_body
• water_body_id int primary key
• default
  nextval('water_body_id_seq'),
• d_water_body_type int,
• water_body_name varchar(60),
• modified_date date,
• modified_by integer,
• row_owner_id integer
• )
• create table river (
• ) inherits (water_body)
• create table lake (
• ) inherits (water_body)

4
Create Tables for Fishing Spots

• create table fishing_spot (
• fishing_spot_id int primary key
• default nextval('fishing_spot_i
  d_seq'),
• water_body_id int,
• fishing_spot_name varchar(60),
• . . .
• )
• create table fishing_spot_point (
• ) inherits (fishing_spot)
• create table fishing_spot_area (
• inherits (fishing_spot)

5
Adding Geometry Columns

• Adding column the_geom to table
  fishing_spot_point
• select AddGeometryColumn(
• , 'fishing_spot_point', 'the_geom', 2992,
• 'POINT', 2)
• Adding column the_geom to table
  fishing_spot_area
• select AddGeometryColumn(
• , 'fishing_spot_area', 'the_geom', 2992,
• 'POLYGON', 2)

6
Creating Spatial Indexes

• Create index fishing_spot_point_the_geom_idx on
  column the_geom of table fishing_spot_point
• CREATE INDEX fishing_spot_point_the_geom_idx
• ON fishing_spot_point USING GIST (the_geom)
• Create index fishing_spot_area_the_geom_idx on
  column the_geom of table fishing_spot_area
• CREATE INDEX fishing_spot_area_the_geom_idx
• ON fishing_spot_area USING GIST (the_geom)

7
Well-Known Text Form Representations

• POINT(0 0)
• LINESTRING(0 0,1 1,1 2)
• POLYGON(
• (0 0, 5 0, 5 5, 0 5, 0 0),
• (1 1, 2 1, 2 2, 1 2, 1 1),
• (3 3, 4 3, 4 4, 3 4, 3 3)
• )
• MULTIPOINT(0 0, 1 2, 5 5)
• MULTILINESTRING((0 0, 1 1, 1 2), (2 3, 3 2, 5
  4))
• MULTIPOLYGON(
• ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2
  2, 1 2, 1 1)),
• ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))
• )
• GEOMETRYCOLLECTION(POINT(2 3), LINESTRING((2 3, 3
  4)))

8
Inserting a Polygon Feature

• insert into property(property_id, the_geom)
• values(999, geometryfromtext(
• 'POLYGON((100 100, 200 100,
• 200 200, 100 200, 100 100))',
• 2992)
• )
• select property_id, st_astext(the_geom)
• from property
• where property_id 999
• 999 POLYGON((100 100, 200 100, 200 200,
• 100 200, 100 100))

9
Inserting a Polygon with Multiple Holes

• insert into property(property_id, the_geom)
• values(1002, geometryfromtext(
• 'POLYGON(
• (100 100, 200 100, 200 200, 100 200, 100
  100),
• (110 110, 180 110, 180 180, 110 180, 110
  110),
• (160 160, 180 160, 180 180, 160 180, 160
  160))',
• 2992)
• )

10
Spatial Type Function ST_Contains(geometry A,
geometry B)

• Returns 1 (TRUE) if Geometry A "spatially
  contains" Geometry B.
• (PostGIS Manual) This function call will
  automatically include a bounding box comparison
  that will make use of any indexes that are
  available on the geometries.

select gid, st_astext(the_geom) from
clu_parcel where st_contains( the_geom,
geometryfromtext( 'POINT(1994577 1209379)',
2992) )
11
Table with an Indexed Geometry Column

• nrcs \d public.clu_parcel
• gid integer
• statecd character varying
• the_geom geometry
• Indexes
• "clu_parcel_pkey" PRIMARY KEY, btree (gid)
• "clu_parcel_the_geom_gist_idx" gist (the_geom)
• Check constraints
• "enforce_geotype_the_geom" CHECK
• geometrytype(the_geom) 'MULTIPOLYGON'text
  
• OR the_geom IS NULL)
• "enforce_srid_the_geom" CHECK
• (srid(the_geom) 2992)

12
GiST Index Not Used

• explain analyze select gid, st_astext(the_geom)
• from clu_parcel
• where st_contains(the_geom,
• geometryfromtext('POINT(1994577 1209379)',
  2992))
• QUERY PLAN
• ------------------------------------
• Seq Scan on clu_parcel
• (cost0.00..22648.24 rows88731 width879)
• (actual time508.587..31353.423 rows1 loops1)
• Filter st_contains(the_geom,
  '0101000020B00B000000000000516F3E41000000002374324
  1'geometry)
• Total runtime 31361.671 ms

13
GiST Index Used

• In order to use the Gist index, apply first an
  overlaps operation on the extents.
• Operation uses extents instead of actual
  shapes,
• and hence extents can be used.

• explain analyze select gid, st_astext(the_geom)
• from clu_parcel
• where the_geom geometryfromtext('POINT(1994577
  1209379)', 2992)
• and st_contains(the_geom, geometryfromtext('POIN
  T(1994577 1209379)', 2992))

14
GiST Index Used (Contd)

• Index Scan using clu_parcel_the_geom_gist_idx on
  clu_parcel
• (cost0.00..6.02 rows1 width879)
• (actual time1.662..4.579 rows1 loops1)
• Index Cond
• (the_geom '0101000020B00B000000000000516F3E41
  0000000023743241'
• geometry)
• Filter
• ((the_geom '0101000020B00B000000000000516F3E41
  0000000023743241'
• geometry)
• AND st_contains(the_geom, '0101000020B00B00000000
  0000516F3E410000000023743241'
• geometry))
• Total runtime 4.750 ms