.

Geoinformatics_03-2009
article powerful gis functions in the database system postgresql postgis postgis offers powerful gis functionality within a database management system. it is a geospatial extension on top of postgresql, a well-known open source relational database management system running on linux, windows, mac os x and others. in this article, spatial functionalities and features are explored, as well as some practical examples for new users. by stephan holl and hans plum the functions are classified into: data management functions geometry relationship functions geometry processing functions geometry accessors geometry constructors let's have a look at one example before you try it out for yourself. imagine you are going to a specific municipality xyz and you'd like to know where the pubs are. so, you need to get the geometric intersection for the municipality and the map of pubs. in terms of sql, you just need to ask the following question: <code> select -- identifier from table 'municipality' municipality.gid as munici_gid, -- identifier from table 'pubs' pubs.gid as pubs_gid, -- name of pub pubs.name as pubs_name from municipality, pubs where -- criterion for spatial selection st_intersects(municipality.the_geom, pubs.the_geom) -- filter just for the desired municipality and municipality.name = 'xyz'; </code> figure 1: postings to the users and developers mailing lists between april 2002 and december 2008 ostgis offers powerful gis functionality within a database management system. it is a geospatial extension on top of postgresql, a wellknown open source relational database management system running on linux, windows, mac os x and others. starting from a more academic concept in 2001, postgis evolved into a powerful free and open source geospatial database which is used in many different companies, governments and private sectors all around the world. for instance, in the netherlands postgis is used extensively for providing official planning information at ruimtelijkeplannen.nl and in several applications of the crossborder geospatial data infrastructure (x-gdi) initiative between the netherlands and germany. p functionalities and features in postgis with postgis 1.3.5, released in december 2008, a wide variety of sql commands are available to solve virtually all kinds of spatial questions. after installation of postgis and enabling the spatial operations (for instructions on debian gnu/linux and windows see below), postgis ships with: 7 new geometry-related data types like point, polyline, polygon etc. 9 aggregate functions for geospatial operations like intersect, union, etc. 297 basic sql functions for spatial operations on geo data result: munici_gid | pubs_gid | pubs_name 1 10 at james' 1 15 at anne's ... based on these mechanisms and starting from here, you can answer a lot of spatial questions with postgis. after looking a bit more at the history of the project, we will go to a quickstart tutorial with some examples that can be used for the first steps. april/may 2009 34
Powered by fluidbook tm