DB2, etc. And there are two major products MySQL and PostgreSQL in the open source database.
PostgreSQL is an object-relational database management system (ORDBMS) and the most powerful one at present.
The most abundant and complex free software database system. It originated from the Database Research Program (BSD) in Berkeley.
At present, it is one of the most important open source database product development projects, with a very wide range of users.
PostgreSQL can be said to be the most distinctive free database management system, and some people think it can be the most powerful freedom.
Software database management system. PostgreSQL is the only system that supports transactions, subqueries, multi-version parallel control and data completion.
Integrity check and other features of the only free software database management system. Can be used on multiple platforms-including Linux,
FreeBSD and Windows, etc. -Run and support multilingual development.
In the comparison of two open source database products, it is generally believed that MySQL is faster, so it is more widely used.
Use; The performance of PostgreSQL is more advanced, and PostgreSQL provides many functions that MySQL does not currently support.
Such as triggers, views, stored procedures, etc. After recording tens of millions, it performed particularly well.
The latest version is PostgreSQL 8.2.3, and official website is:
www.postgresql.org
1.2 What is PostGIS?
PostGIS enhances the ability to store and manage spatial data on the object-relational database PostgreSQL, which is equivalent to
The space part of Oracle Bone Inscriptions. The biggest feature of PostGIS is that it conforms to and realizes some specifications of OpenGIS, which is the most famous.
An open source GIS database named.
The latest version is PostGIS 1.2. 1, and official website is:
www.postgis.org
2.PostgreSQL and installation of PostgreSQL
2. 1 is installed under windows.
The new version of PostgreSQL integrates PostgreSQL into its installation program, which only needs to be selected during installation.
PostGIS and pgsql items are both acceptable.
2.2 installing PostGIS under Debian
# apt-get to install PostgreSQL-8.1PostgreSQL-8.1-postgis.
The current version in Etch: PostgreSQL is 8. 1.7, while PostgreSQL is 1. 1.6, although it is not the latest version.
But that's enough.
What still needs to be done is:
1) Create a database dedicated to PostGIS and import PostGIS support for the dedicated database:
# su–postgres
$ CD/usr/share/PostgreSQL-8. 1-postgis
$ createdb wen 1
$ createlang plpgsql wen 1
$ psql-d Wen 1-f lwpostgis . SQL
$ psql-d Wen 1-f spatial _ ref _ sys . SQL
2) Create a special user and set the owner of the corresponding database and data table as this user:
# su–postgres
$ psql
$ Create user wen 1 password'123456';
Change the owner of the database wen 1 to wen1;
$ Change the owner of table spatial_ref_sys to wen1;
$ change table geometry_columns owner to wen1;
3) Modify the PostgreSQL configuration file so that it can be used remotely.
Modify the configuration file of PostgreSQL/etc/PostgreSQL/8.1/main/PostgreSQL.conf and add a sentence:
listen_address='localhost '
Delete the previous comments and replace "localhost" with "*".
Modify the configuration file of PostgreSQL/etc/PostgreSQL/8.1/main/pg _ hba.conf and add the following sentence after the file:
The host has all the passwords of 192. 168.0.0/24.
Machines on the same network as 192.5438+068.0. * All databases can be used in the form of passwords. larger
Just look at the comments in the configuration file for the parameter meaning of body.
You must configure it correctly here, otherwise you can't connect to PostgreSQL database remotely.
2.3 source code installation PostgreSQL and PostgreSQL
Refer to the old articles written before.
Thirdly, the use of postal geographic information system.
Using PostGIS requires two aspects of preparatory knowledge, one is to be familiar with the basic SQL syntax; The second is familiarity.
Basic use of PostgreSQL database.
quick start
We use the database wen2 created earlier, and the user is wen 1. Now let's create a database with three points.
test 1:
First log in with wen 1 under Etch, and then open wen2 database:
$ psql-d Wen 2；
Then enter the following command under the console of PostgreSQL:
Wen2 => Create table test 1 (myID int4, pt geometry, myName.
varchar)；
Wen2 => Insert into test 1 value (1,' Point (0 0)',' Beijing'.
);
Wen2 => insert into test 1 value (2,' point (3 1.5 60.87)',
Shanghai');
Wen2 => insert into test 1 value (3,' point (65438+85.902)',
Tianjin');
In this way, we created a three-point GIS data table with PostGIS.
In order to open this layer in QGIS, we need to create a primary key for the data table:
Wen2 = & gtalter table test 1 add primary key (myid);
As you can see, using PostGIS is actually equivalent to using extended SQL syntax. The above statement is familiar.
People who are familiar with SQL syntax are familiar with it at first glance. They are all ordinary SQL statements, but the difference is that PostGIS special is added.
Geometric data type.
You can try these SQL statements again:
select * from test 1；
select myID，as text(pt)from test 1；
Select the distance (pt,' POINT(0 0)')；') from test 1;
(b) Geometric data types of postal geographic information system
Geometry can be said to be the most important concept of PostGIS, which means "geometry" because PostGIS is very
It is well in line with OGC's "simple characteristics of specification"
SQL "specification, currently supported geometric type packages.
Examples include:
Point (1 1)
Multipoint (1 1, 3 4,-1 3)
LINESTRING( 1 1，2 2，3 4)
Polygon ((00,01,1 1,10,00))
Polypolygons ((00,01,1 1,10,00), (55,56,66,65,5)
5))
Multiple strings ((1 1, 22,34), (22,33,45))
There are two specific forms of geometry, one is called WKT (well-known writing), as mentioned above.
Examples. Or browse using the following SQL statement:
Select AsText(pt) from test 1;
Another form, called canonical form, looks like a series of odd numbers, but it is actually an enhanced hexadecimal.
Use the following SQL statement to browse binary codes:
Select pt from test 1;
(PostGIS data reading and writing
After building PostGIS database, we now need to read and write GIS data, including writing GIS into.
Use data from PostGIS databases and applications. At present, reading and writing PostGIS mainly includes
The following four ways:
3. 1 Use psql language.
Psql language is a command-line tool embedded in Postgresql, and its syntax is basically the same as that of standard SQL.
Therefore, you can use Psql tools, combined with standard sql syntax and some PostGIS extensions to realize PostGIS database.
Read and write operations.
A concrete example is the example in "Quick Start" above.
This method is powerful, but it needs manual operation, which is cumbersome and cumbersome.
3.2 Use some gadgets
There are two very useful small conversion tools, one is shp2pg；; One is ogr2ogr.
3.2. 1 shp2pgsql and pgsql2shp
Shp2pgsql and pgsql2shp are a pair carried by PostGIS itself, which is between the Shape file and PostGIS database.
Line conversion tool, after installing PostGIS in Debian, these two programs are already in the search path of executable files.
Diameter, so it can be used anywhere in the system.
If there is a complete china.shp file in the current directory (there should be three files with the same name and different extensions), then you need to
Input the data into the data table china_postgis in the wen2 database mentioned above, and the specific operation is (the user is
wen 1):
$ shp 2 pgsql China China _ postgis & gt； tmp.sql
$ psql -d wen2 -f roads.sql
This tool is very easy to use, but the disadvantage is that its scope of use is limited, only for shape file formats.
ogr2ogr
The shp2pg tool of PostGIS itself only converts shape files into PostGIS database, so how to convert other files?
What about adding GIS data? What about the mid format of MapInfo and the e00 format of ESRI? This requires the use of ogr.
I see.
Ogr is currently a part of gdal, so what is gdal? Its official home page (with
Various useful command-line tools for data conversion and
Working on it.
Simply put, Gdal is a software library for converting various Gis data formats, while ogr is used for converting vector GIS data.
Software library.
Currently, the data formats that ogr can support include:
Arc/Info binary coverage, DWG, ESRI personal
Geodatabase, ArcSDE, ESRI
Shapefile, GML, GRASS, Mapinfo file, Microstation DGN, ODBC, Oracle.
Space sum
PostgreSQL and so on. It should be said that this basically includes all the vector GIS file formats we usually use.
For raster data formats supported by Gdal, please refer to find related tools. I found an e002pg tool on it, which supports put.
E00 file is imported into postGIS database.
3.3 read and write PostGIS data in other GIS software
For example, in QGIS, you can open the PostGIS layer, and the SPIT plug-in can input the shape file into.
PostGIS database
Other GIS software, such as uDig, Grass, etc. And even ArcInfo all support or partially support reading and writing PostGIS data.
3.4 Use the interface to read and write PostGIS data in the application.
Most open source GIS programmers have designed interfaces to read and write PostGIS for almost every programming language, such as Li.
With the JDBC library of PostgreSQL, you can read and write PostgreSQL data in the program in Java language. Using libpq library,
You can read and write PostGIS data in C language.
(d) Using PostGIS functions
4. 1 PostGIS function
Mastering all the functions of PostGIS is the key to skillfully use PostGIS. By mastering these PostGIS functions,
We can handle all kinds of geographic data efficiently and stably. Because the functional design of PostGIS follows the OpenGIS specification,
We can also call these PostGIS functions OpenGIS functions.
4.2 Functional Classification of Postal Geographic Information System
PostGIS functions can be roughly divided into the following four categories
1) field processing function
There are currently three such functions, namely:
Add geometry column (var 1, var 2, var 3, var 4, var 5, var 6): Add geographical location to the existing data table.
Data field. Var 1 indicates the schema name of the data table, which is generally public and can also be omitted.
Previous default mode; Var2 is the name of the existing data table; Var3 is the name of the new geographic data field; Var4 is
SRID value, if uncertain, take-1; Var5 is the type of geographic data, which can be points and so on. What does var6 mean?
Whether the data is two-dimensional or three-dimensional.
The previous SQL statement create table test1(myid int 4, pt geometry, myname.
Varchar) more standardized writing
The law is:
Create the table test 1 (myID int4, myname varchar);
select AddGeometryColumn(' test 1 '，' pt '，- 1，' GEOMETRY '，2)；
The DropGeometryColumn function obviously deleted a geographic data field;
The SetSRID function obviously sets the SRID value.
2) Geometric relation function
At present, there are 10 such functions, namely:
Distance is equal to disjoint intersection point and intersection point.
Overlapping inclusion association
3) Geometric analysis function
At present, there are 12 such functions, namely, the lenth point of the centroid area on the surface boundary.
buffer
Geographical coordinates of convex hull intersection with difference
Memory immunity
4) read and write function
There are many such functions, which are mainly used for conversion between various data types, especially in geometric data classes.
Types and other data types, such as character type conversion, function names, such as AsText, GeomFromText, etc. , its role is to
Obviously.
4.3 PostGIS function use example.
Suppose there are two tables in our wen2 database, one is Guojia, which comes from "National Basic Geographic Data".
The national boundary data table downloaded from the website, with the data type of LINE；; Secondly, the same hymn _ Polygon downloaded from the same website,
The geographic data type is polygon.
1) Inquire about the length of China border:
Wen 1= > select sum(length(the_geom)) from Guo Jie as lenth.
2) Query the name of the largest province in China:
Wen 1= > select the name area (_geom) as myarea.
From Living Area _ Polygon
Myarea DESC order
Limit1;
(5) Using PostGIS to extend functions
In addition to the above-mentioned functions after OpenGIS, PostGIS also extends some current OpenGIS specifications by itself.
Functions, mainly including the following categories:
5. 1 management functions
Extended management functions mainly include some software version query functions, such as
Functions such as postgis_version (), postgis_geos_version () and postgis_proj_version () to query the current.
The version of PostGIS and the version of Geos and Proj libraries it uses.
5.2 Input and output functions of data types
In addition to the geographic data types defined by OpenGIS, PostGIS also extends the data types.
Mainly in two aspects: one is to expand two-dimensional data to three-dimensional and four-dimensional; The second is the data of WKT and WKB.
EWKT and EWKB data types are extended on the basis of type.
PostGIS provides the conversion between these geographic data types and commonly used data types, such as character data and floating-point data.
In the function.
5.3 Measurement function
For example, the length3d function is an extension of the length2d function.
5.4 Geometric operation function
For example, the addBBox(geometry) function adds a border to a given geometry.
For example, through Douglas-Peuker algorithm, simplification (geometry, tolerance) function can be used to compare polylines and polygons.
Some nodes are deleted, which makes the graph simpler and clearer, and it is more efficient when transmitting data on the network.
5.5 Operation symbols
5.6 Other extended functions
(6) Establishing postal geographic information system index.
When the records in the database increase, if there is no index, the efficiency of the operation will decrease obviously.
POstGIS suggests that indexes should be established when the number of records exceeds several thousand, and GIS databases are generally massive data.
So for PostGIS, the index is very important.
Shapfile file is a file storage format of ESRI company, which has been widely supported by the industry. Shapfile format is a simple method to store geometric position and ground in the form of non-topological relationship.
Format of feature attribute information. Although GeoServer can use Shapfile file to quickly create online map service, its disadvantages are obvious:
1 and Shapefile only support one layer, which is actually meaningless.
2. It is unsafe to directly guarantee the SHP file, and the Shapfile is easily deleted by viruses or other reasons.
3. It is inefficient to use Shapfile file as data source in GeoServer.
4.GeoServer, Chinese characters in Shapfile, unable to parse, garbled.
5. The database can query geographic information conveniently.
Using PostGIS to manage spatial data
PostGIS supports GIST spatial index (appendix 1) and standard tables, which can greatly improve the processing efficiency.
OGC format only provides two-dimensional geometry, and the related SRID has never been deeply used for input and output requests. PostGIS supports OpenGIS to organize "simplicity"
Function of
SQL "standardizes all the specified GIS objects and functions, and expands them. The formats are EWKB and EWKT, in which 3DZ, 3DM and 4D are added.
With the support of coordinate system (of course, the OGC standard for 3D and 4D data has not been fully formulated), SRID information is deeply introduced.
Spatial data table structure: There are two necessary metadata tables in PostGIS, SPATIAL_REF_SYS (spatial reference table) and.
GEOMETRY_COLUMNS (geometry attribute column), two tables are used to store the coordinate system digital ID and text description used by spatial database.
The shp2pgsql command of PostGIS can import Shapfile directly into the database or export it as an sql file. It is suggested to export to SQL file first, and then execute it in the SQL running window to import the data into the database.
Shapfile to SQL statement:
Shp2pgsql path Shp data file name new data table name >;
Path sql file name. Structured query language
Shapfile direct storage:
Shp2pgsql -c path Shp data file name newly created data table name database name | PSQL-D database name
For example:
For example, a Shap file "c: Road.shp" is imported into the data table "road", and the database is "sjzmap".
1. Run the command prompt.
2. Switch to the bin directory in the PostgreSQL database installation directory.
3. execute the shp2pgsql command in this directory: "shp2pgsqlc: road.shpload >
c:road.sql .
4. If this file is directly imported into the database (not recommended): "shp2pgsql-c c: road.shpload"
sjzmap | psql -d sjzmap .
Step 5 use pgAdmin3
Select a database and import a table.