SpatialDocumentation.docx

shrubberystatuesqueΔιαχείριση Δεδομένων

1 Δεκ 2012 (πριν από 4 χρόνια και 8 μήνες)

327 εμφανίσεις

A

S
patial Querying Framework
Using PostGIS

Background


While one could hand craft necessary
functions

to execute spatially
aware
queries, it is far more economical to take advantage of what already exists. The
active PostGIS community has done an excell
ent job
ensuring that Post
greSQL can
be spatially enabled.
However
, data must be
adequately ingested,

stored
, and
indexed

such that any

client application can rapidly

execute spatial queries
.

PostgreSQL / PostGIS
Backend


PostgreSQL is not spatially aware
out
-
of
-
the
-
box. Rath
er, it must be extended
with

PostGIS
stored procedures

to execute

spatial queries.

The PostGIS manual is an
excellent resource

and should be printed and kept
on hand:
http:/
/postgis.refractions.net/docs/index.html
.

A frequently overlooked point

is that creating a spatial table in a
PostgreSQL
/
Po
stGIS

database is a 2
-
step process:
1.
creat
e a normal non
-
spatial table, 2.
then a
spatial column must be added using the OpenGIS
"AddGeometryColumn" function
(see
http://postgis.refractions.net/docs/ch04.html#Create_Spatial_Table
).

This
spatial column must also be properly indexed.

Re
quired

Softw
are


PostgreSQL 9.0:
http://www.postgresql.org/

PostGIS 1.5.2:
http://postgis.refractions.net/

GEOS 3.2.2:
http://trac.osgeo.org/geos/

Proj 4.7:
http://trac.osgeo.org/proj/

GDAL 1.7.2:
http://www.gdal.org/


pgAdmin 1.12.2:
http://www.pgadmin.
org/

(excellent GUI for managing PostgreSQL
databases)


If
a resource

wishes to also provide Web Map Service (WMS) or Web Feature
Service (WFS), which are OpenGIS standards for
serving images

or XML responses,
respectively, then I highly recommend MapServe
r,
http://mapserver.org/
. This is a
CGI
-
based application that may connect directly to PostgreSQL and/or ArcView
shapefiles to return tiled images or WFS responses. There are thousands of
MapServer
-
based applications,
but one such example is one I maintain called
SimpleMappr

(
http://www.simplemappr.net
). This uses

PHP
-
MapScript instead of
the CGI
-
based instance

to avoid having to create static “map files”, a required
MapServer
configuration file
. There are also Python and SWIG
-
based versions of
MapScript.

Although MapServer can

be used to execute spatial queries
directly
against

ArcView shapefiles, it is an in
-
memory
application and thus will not scale

if
the shapefiles are appr
eciably large or if there are many shapefiles.

Instead,
shapefiles can be ingested into a performance
-
friendly PostGIS/
PostgreSQL
database
and MapServer used to serve
tiled images or WFS responses

from the
spatial table(s)
.

Environment Set
-
up


In order to
spatially
-
enable PostgreSQL database, it is useful to create a
database template that can be re
-
used without superuser permissions:


# Set postgis
-
1.5
.2 path where postgis.sql and spatial_ref_sys.sql are found

$ POSTGIS_SQL_PATH=`pg_config
--
sharedir`/contrib/postgis
-
1.5


# Create

the
UTF
-
8
template spatial database

(CAUTION: PostgreSQL is SQL_ASCII by default)

$ createdb
-
E UTF8
-
T template0 template_postgis


# A
dd PLPGSQL language support.

$ createlang
-
d template_postgis plpgsql


# Load

the PostGIS SQL routines.

$ psql
-
d template_postgis
-
f $POSTGIS_SQL_PATH/postgis.sql

$ psql
-
d template_postgis
-
f $POSTGIS_SQL_PATH/spatial_ref_sys.sql


# Enable

users to alter spatial tables.

$ psql
-
d template_postgis
-
c "GRANT ALL ON geometry_columns

TO PUBLIC;"

$ psql
-
d template_postgis
-
c "GRANT ALL ON geography_columns TO PUBLIC;"

$ psql
-
d template_postgis
-
c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"


# Garbage
-
collect and freeze.

$ psql
-
d template_postgis
-
c "VACUUM FULL;"

$ psql
-
d template_po
stgis
-
c "VACUUM FREEZE;"


# Allow

non
-
superusers the ability to create from this template.

$ psql
-
d postgres
-
c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"

$ psql
-
d postgres
-
c "UPDATE pg_database SET datallowconn='fa
lse' WHERE datname='template_postgis';"


A new database,
test_gis
, may now be created using the newly created UTF
-
8
template:


$ creat
edb
-
h host
-
name test_gis

-
W
-
T template_postgis

Loading Spatial Data



There are at least two tools to load or prepare data for insertion into a spatial
table. One is called
shp2pgsql

and is provided with PostGIS. It converts an ArcView
shapefile into SQL statements. However, a more economical approach that has
served me well

is a command
-
line tool called
ogr2ogr

bundled with GDAL. It can be
used to load ArcView shapefiles directly into a PostGIS database and documentation
can be found at
http://www.gdal.org/ogr2ogr.html
. By way

of example:


ogr2ogr
-
f PostgreSQL

PG:dbname=test_gis
-
overwrite

-
nln mammals

nlt MULTIPOLYGON
/path/to/shapefile.shp


The above will import
a MULTIPOLYGON
shapefile.shp

into an existing database
test_gis

and will create a spatial table called
mammals

or

overwrite it if it already
exists.



Loading a tab
-
delimited text

file containing latitude and longitude
coordinat
es

in decimal degrees

into a spatial table is a three
-
step process: 1. load
the file, 2.
create the spatial column, 3.
update the spatial col
umn
. For example:


CREATE TABLE test_gis (latitude float8, longitude float8) WITHOUT OIDS;

COPY test_gis FROM '/path/to/file/relative/to/server/myfile.tab';

SEL
ECT AddGeometryColumn(
'
test_gis
'
,
'
the_geom
'
, 4326, 'POINT', 2 );

#table, column, SRID, type, dimension

UPDATE test_gis SET the_geom = ST_SetSRID(

ST_Po
int(longitude, latitude),4326)
;


[Aside: the SRID
is the spatial reference id and is the same as the stock EPSG codes:
http:
//spatialreference.org/
]

Ubuntu
-
based Installation


If
the back
-
end server is

Ubuntu
-
based
,
a bit of preparatory work must be
done;
the apt packages for PostgreSQL and PostGIS are dated. Fortunately, the
Ubuntu GIS project is hosting updated packages such

that thes
e repositories may be
added to

Lucid
(10.04)
or Maverick
(10.10, current as of writing)
.

An older Ubuntu
server such as Karmic

(9.10) will have too many unmet dependencies (
e.g.

libxml2)
so is not recommended.


For example (
adapted
from
http://tinyurl.com/45tpz2t
):


sudo add
-
apt
-
repository ppa:pitti/postgresql

sudo add
-
apt
-
repository ppa:ubuntugis/ubuntugis
-
unstable

sudo apt
-
get update

sudo apt
-
get install
-
y postgresql
-
9.0 postgresql
-
server
-
dev
-
9.0 postg
resql
-
contrib
-
9.0 proj libgeos
-
3.2.2 libgeos
-
c1 libgeos
-
dev libgdal1
-
1.7.0 libgdal1
-
dev build
-
essential libxml2 libxml2
-
dev checkinstall


wget http://postgis.refractions.net/download/postgis
-
1.5.2.tar.gz

tar zxvf postgis
-
1.5.2.tar.gz && cd postgis
-
1.5.2/

sudo ./configure && sudo make && sudo checkinstall
--
pkgname postgis
-
1.5.2
--
pkgversion 1.5.2
-
src
--
default


# create a test database called test_gis and import the PostGIS functions and spatial reference systems

sudo su postgres

createdb
-
U postgres test_
gis

createlang

d test_gis plpgsql

psql
-
U postgres
-
d test_gis
-
f /usr/share/postgresql/9.0/contrib/hstore.sql

psql
-
U postgres
-
d test_gis
-
f /usr/share/postgresql/9.0/contrib/postgis
-
1.5/postgis.sql

psql
-
U postgres
-
d test_gis
-
f /usr/share/postgresql/
9.0/contrib/postgis
-
1.5/spatial_ref_sys.sql

psql
-
U postgres
-
d test_gis
-
c"select postgis_lib_version();"

exit

Mac Development Environment


I recommend MacPorts (
http://www.macports.org/
)
because it has the
necessary, up
-
to
-
date ports:


sudo port install postgresql90

sudo port install postgresql90
-
server

sudo port install postgis

sudo port install proj

sudo port install gdal

port clean
--
all vile


Creating and spatially enabling a test PostgreSQL database wil
l be much the
same as the Ubuntu example provided above. However, the necessary postgis.sql
and spatial_ref_sys.sql locatio
ns will of course be different
.


Front
-
end Applications


By far the best, freely available front
-
end application for querying or
displaying data from PostgreSQL is OpenLayers,
http://openlayers.org/
. It can draw
tiles from Google Maps, Yahoo, or Bing, data from WMS, WFS, KM
L, GeoRSS, among
other sources, and has a rich set of GUI tools such as free
-
hand line, circle, or
polygon drawing
. I
f

Google Maps, Yahoo, or Bing are used as base layers, the
commercial
Spherical Mercator projection must be added to the spatial database’s

spatial_ref_sys table as follows:


INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 900913, ’spatialreference.org’,
6, ‘+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m
+nadgrids=@
null +wktext +no_defs’, ‘PROJCS["unnamed",GEOGCS["unnamed
ellipse",DATUM["unknown",SPHEROID["unnamed",6378137,0]],PRIMEM["Greenwich",0]

,UNIT["degree",0.0174532925199433]],PROJECTION["Mercator_2SP"],PARAMETER["standard

_parallel_1",0],PARAMETER["central_m
eridian",0],PARAMETER["false_easting",0],PARAMETER

["false_northing",0],UNIT["Meter",1],EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0
+lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs"]]’);


OpenLayers has a met
hod to capture the WKT (well
-
known text) that a user may
“draw” on a map and the ability to re
-
project coordinate systems on the client
’s

side.
Thus, the OpenLayers WKT
line or polygon in
a
Spherical Mercator
projection
can be
translated into EPSG 4326 on
the client side then passed to the backend to generate
a recordset for all intersected data

in
a spatial table
. In PHP pseudo
-
code:


$spatial = pg_escape_string($_POST
['WKT
']);

//get POSTed WKT using

e.g.

jQuery

//find rows that intersect the WKT

$qry = "
SELECT * FROM mammals WHERE
ST_Intersects(
the_geom
, ST_GeomFromText('".$spatial."',4326))"
;