Use of Postgresql+postgis

First, the geometry type in the PostGIS

PostGIS supports the "simple Features" type of all OGC specifications, and on this basis expands support for 3DZ, 3DM, and 4D coordinates.

1. OGC WKB and WKT formats

The OGC defines two formats for describing geometric objects, namely WKB (well-known Binary) and wkt (well-known Text).

In the SQL statement, you can use the WKT format to define a Geometry object in the following ways:

• Point (0 0)--Points
• LINESTRING (0 0,1 2)--line
• POLYGON ((0 0,4 0,4 4,0 4,0 0), (1 1, 2 1, 2 2, 1 2,1 1)--face
• MULTIPOINT (0 0,1 2)--multipoint
• Multilinestring ((0 0,1 2), (2 3,3 2,5 4))--Multi-line
• Multipolygon (((0 0,4 0,4 4,0 4,0 0), (1 2,1 2,1 1)), (( -1-1,-1-2,-2-2,-2-1,-1-1)))--Multi-faceted
• GeometryCollection (Point (2 3), LINESTRING ((2 3,3 4))--geometric set

The following statements can be used to insert a point feature into a table using the WKT format, where functions such as geomfromtext are described in detail later:
INSERT into table (SHAPE, NAME)
VALUES (Geomfromtext (' Point (116.39 39.9) ', 4326), ' Beijing ');

2. Ewkt, EWKB, and canonical formats

Ewkt and EWKB have 3DZ, 3DM, 4D coordinates, and inline spatial reference support compared to the main extensions of the OGC wkt and WKB formats.

Some geometric objects are defined in the EWKT statement:
Point (0 0 0)--3d points
srid=32632; Point (0 0)--embedded spatial reference points
Pointm (0 0 0)--points with M values
Point (0 0 0 0)--3D points with M values
srid=4326; MULTIPOINTM (0 0 0,1 2 1)--multi-point with M-values embedded in the spatial reference

The following statement can insert a point feature into a table using the EWKT format:
INSERT into table (SHAPE, NAME)
VALUES (Geomfromewkt (' srid=4326; Pointm (116.39 39.9 10), ' Beijing ')

The canonical format is a 16-encoded geometric object, which is the format that is queried directly with SQL statements.

3. sql-mm format

The SQL-MM format defines some interpolation curves that are somewhat similar to ewkt and support 3DZ, 3DM, and 4D coordinates, but do not support embedding spatial references.

Some interpolation geometries are defined with the SQL-MM statement:
Circularstring (0 0, 1 1, 1 0)--interpolation arc
Compoundcurve (circularstring (0 0, 1 1, 1 0), (1 0, 0 1))--interpolation compound curve
Curvepolygon (circularstring (0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 3 3, 3 1, 1 1))--Curved polygon
Multicurve ((0 0, 5 5), circularstring (4 0, 4 4, 8 4))--Multi-curve
Multisurface (Curvepolygon (circularstring (0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 3 3, 3 1, 1 1)), ((10 10, 14 12, 11 10, 10 10), (11 11, 11.5 11, 11 11.5, 11 11))--Multi-surface

Realization of spatial information processing in PostGIS

1. Spatial_ref_sys table

In the public mode of a database created based on the PostGIS template, there is a spatial_ref_sys table that holds the spatial reference of the OGC specification. Let's take a look at our most familiar 4326 references:

Its srid storage is the spatial reference of the well-known ID, the definition of this spatial reference consists of two fields, the Srtext is stored in a string description of the spatial reference, Proj4text is stored in a string description of the proj.4 Projection definition (PostGIS using proj.4 for projection).

4326 Srtext content of the spatial reference:
geogcs["WGs", datum["wgs_1984", spheroid["WGs", 6378137,298.257223563,authority["EPSG", "7030"]],towgs84[ 0,0,0,0,0,0,0],authority["EPSG", "6326"]],primem["Greenwich", 0,authority["EPSG", "8901"]],unit["degree", 0.01745329251994328,authority["EPSG", "9122"]],authority["EPSG", "4326"]

4326 Proj4text content of the spatial reference:
+proj=longlat +ellps=wgs84 +datum=wgs84 +no_defs

2. Geometry_columns table

The Geometry_columns table holds information about all the geometry fields in the current database, such as the two spatial tables in my current library, where you can find the definition of the geometry fields in the Geometry_columns table:

Where the F_table_schema field represents the pattern in which the spatial table is located, the F_table_name field represents the table name of the spatial table, and the F_geometry_column field represents the name of the geometry field in the spatial table. The Srid field represents the spatial reference of the spatial table.

3. Create a spatial table in PostGIS

Creating a spatial table with a geometry field in PostGIS is divided into 2 steps: The first step is to create a generic table, and the second step is to add a geometry field to the table.

The following first creates a generic table named cities in test mode:
CREATE TABLE test.cities (ID int4, name varchar (20))

And then add a Geometry field named shape (two-dimensional point) to cities:
Select Addgeometrycolumn (' Test ', ' cities ', ' shape ', 4326, ' point ', 2)

4. PostGIS Inspection of geometrical information

PostGIS can check the correctness of geometry information, which is mainly achieved through the IsValid function.
The following statement examines the correctness of the 2 geometric objects, obviously, (0, 0) points and (the) points can form a line, but (0, 0) points and (0, 0) points do not constitute, the statement after the execution of the result is true,false.

Select IsValid (' LINESTRING (0 0, 1 1) '), IsValid (' LINESTRING (0 0,0 0) ')
The default PostGIS does not use the IsValid function to check for new data inserted by the user, because it consumes more CPU resources (especially complex geometries). When you need to use this feature, you can use the following statement to create a new constraint for the table:
ALTER TABLE Cities
ADD CONSTRAINT Geometry_valid
CHECK (IsValid (Shape))

When we try to insert a wrong space object into this table, we get an error:
INSERT into Test.cities (shape, name)
VALUES (Geomfromtext (' LINESTRING (0 0,0 0) ', 4326), ' Beijing ');

Error:new row for relation "Cities" violates Check constraint "Geometry_valid"
SQL Status: 23514

5. Spatial index in PostGIS

Database access to multidimensional data has two kinds of indexing scheme, r-tree and gist (generalized Search Tree), gist in PostgreSQL is better than r-tree robustness, Therefore, the postgis of spatial data is generally implemented by GIST.

The following statement adds a spatial index shape_index_cities to the cities table in the SDE schema, and the same functionality can be done through the graphical interface in Pgadmin.
CREATE INDEX shape_index_cities
On Sde.cities
USING Gist
(shape);

It is also important to note that spatial indexes only work when a boundary-based query is made, such as the "&&" operation.

The common functions in PostGIS

The following includes more angle brackets, published to the blogger when the display is not normal, too many of the content I do not have time to manually change the code, so if you have problems, refer to the official PostGIS document.

The first thing to illustrate is that many of the functions here are named in the form of st_[x]yyy, in fact many functions can also be accessed through xyyy, and in the library of PostGIS we can see that the two functions are defined exactly the same way.

1. OGC Standard Functions

Management functions:
Add Geometry field addgeometrycolumn (,,,,,)
Delete Geometry field dropgeometrycolumn (,,)
Check database geometry fields and archive in Geometry_columns Probe_geometry_columns ()
Set a spatial reference for a geometric object (commonly used when making spatial queries from a range) St_setsrid (geometry, Integer)

Geometric object relationship function:
Gets the distance between two geometries st_distance (geometry, geometry)
Returns True St_dwithin if the distance between two geometries is within a given range of values (geometry, Geometry, float)
Determines whether two geometric objects are equal
(such as linestring (0 0, 2 2) and linestring (0 0, 1 1, 2 2) are the same geometry) st_equals (geometry, GeoMet RY)
Determines whether two geometries are detached st_disjoint (geometry, geometry)
to determine whether two geometries intersect st_intersects (geometry, geometry)
Determines whether the edges of two geometric objects Touch st_touches (geometry, geometry)
to determine if two geometries cross each other st_crosses (geometry, geometry)
To determine if A is contained by B St_ Within (geometry A, geometry B)
Determines whether two geometries are overlapping st_overlaps (geometry, geometry)
To determine whether A contains B st_contains (geometry A, Geometry b)
Determine if a overrides B st_covers (geometry A, Geometry b)
to determine if a is covered by B st_coveredby (geometry A, geometry B)
Determine whether the relationship of two geometric objects is established by De-9im matrix st_relate (geometry, Geometry, Intersectionpatternmatrix)
Get the relationship of two geometric objects (De-9im matrix) st_ Relate (geometry, geometry)

Geometric object handler:
Gets the center st_centroid (geometry) of the geometric object
Area measurement st_area (geometry)
Length measurement st_length (geometry)
Returns a point on the surface st_pointonsurface (geometry)
Get boundary st_boundary (geometry)
Gets the buffered geometry st_buffer (geometry, double, [ Integer])
Gets the add-in Object St_convexhull (geometry)
of a multi-geometry object St_intersection (geometry, geometry)
for the intersection of two geometries Add a value of less than 0 to the longitude of 360 so that all longitude values in 0-360 st_shift_longitude (geometry)
Get two geometric objects that do not intersect (A, b are interchangeable) st_symdifference (geometry A, Geometry b)
returns St_difference (geometry A, geometry b) After removing the part of a and B from a
returns the merge result of two geometries st_union (geometry, geometry)
Returns the combined result of a series of geometric objects st_union (geometry set)
to complete the merge operation with less memory and longer time, with the same result as St_union st_memunion (geometry set)

Geometric object access function:
Gets the wkt description of the geometric object St_astext (geometry)
Gets the WKB description of the Geometry st_asbinary (geometry)
Gets the spatial reference ID of the Geometry object St_srid ( Geometry)
Gets the dimension of the Geometry st_dimension (geometry)
Gets the bounding range of the geometric object St_envelope (geometry)
Determines whether the geometry object is empty st_isempty ( Geometry)
Determines whether a geometry object does not contain a special point (such as self-intersecting) st_issimple (geometry)
Determines whether the geometry is closed st_isclosed (geometry)
Determines whether the curve is closed and does not contain a special point St_isring (geometry)
Gets the number of objects in a multi-geometry object St_numgeometries (geometry)
Gets the Nth object in a multi-geometry object St_geometryn (geometry,int)
Gets the number of points in the Geometry st_numpoints (geometry)
Gets the nth point of a geometric object St_pointn (Geometry,integer)
Gets the outer edge of the polygon st_exteriorring ( Geometry)
Gets the number of boundaries within a polygon st_numinteriorrings (geometry)
st_numinteriorring (geometry)
Gets the nth inner boundary of the polygon St_ INTERIORRINGN (Geometry,integer)
Get the end of the line St_endpoint (geometry)
Get the starting point of the line St_startpoint (geometry)
Get the type of the Geometry object Geometrytype (geometry)
is similar, but does not check the M-value, that is, the Pointm object is judged to be point st_geometrytype (geometry)
to get the X-coordinate of the points st_x (geometry) The
gets the Y-coordinate of the point st_y (geometry)
Gets the Z-coordinate of the point st_z (geometry)
Gets the M-value of the point st_m (geometry)

Geometric object constructors:
Reference semantics:
Text:wkt
Wkb:wkb
Geom:geometry
M:multi
Bd:buildarea
Coll:collection St_geomfromtext (text,[])

St_pointfromtext (text,[])
St_linefromtext (text,[])
St_linestringfromtext (text,[])
St_polyfromtext (text,[])
St_polygonfromtext (text,[])
St_mpointfromtext (text,[])
St_mlinefromtext (text,[])
St_mpolyfromtext (text,[])
St_geomcollfromtext (text,[])
ST_GEOMFROMWKB (bytea,[])
ST_GEOMETRYFROMWKB (bytea,[])
ST_POINTFROMWKB (bytea,[])
ST_LINEFROMWKB (bytea,[])
ST_LINESTRINGFROMWKB (bytea,[])
ST_POLYFROMWKB (bytea,[])
ST_POLYGONFROMWKB (bytea,[])
ST_MPOINTFROMWKB (bytea,[])
ST_MLINEFROMWKB (bytea,[])
ST_MPOLYFROMWKB (bytea,[])
ST_GEOMCOLLFROMWKB (bytea,[])
St_bdpolyfromtext (text WKT, integer SRID)
St_bdmpolyfromtext (text WKT, integer SRID)

Iv. Examples of PostGIS

Let's look at the usage of PostGIS using a simple Flex application example:

Assuming that a terrorist attack has taken place in some cities, we now have to calculate the extent of the pollution spread based on pollutants and local wind and wind conditions, and to alert and evacuate these areas in a timely manner.

First we want to get information about the current wind speed and wind direction of all polluted cities, and we have a spatial table in our PostGIS database that holds this information, and we construct such an SQL statement to query:
Select *,st_asgeojson (Shape) from Sde.wind

All wind-related information is obtained here, and the geometry information returned in JSON format is appended, which helps us resolve in flex. As the result of the search for wind:

Below we want PostGIS to help us achieve some spatial analysis. We start with the pollution of the city, the local wind direction, the construction of a 30-degree open angle range, this range will be the main direction of pollution diffusion, the extent of the diffusion is mainly related to the intensity of the wind; After constructing this area, we are buffering it for the sake of insurance, Finally, the extent to which each pollutant source may spread is obtained. The SQL statements we construct are as follows:
Select *,st_asgeojson (St_buffer (St_polygonfromtext (' POLYGON St_x (shape) | | ' ' | | St_y (shape) | | ', ' | | St_x (Shape) +velocity*cos ((direction+15) *pi ()/180)/20| | ' ' | | St_y (Shape) +velocity*sin ((direction+15) *pi ()/180)/20| | ', ' | | St_x (Shape) +velocity*cos ((direction-15) *pi ()/180)/20| | ' ' | | St_y (Shape) +velocity*sin ((direction-15) *pi ()/180)/20| | ', ' | | St_x (shape) | | ' ' | | St_y (shape) | | ')) ' ), velocity/50)) from Sde.wind

The following is the result returned by the PostGIS operation:

Here, the flex application interacts with the server through BlazeDS, and here is the Java code on the server side of this example:

 
package wuyf;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class Wind
{
private Connection conn = null;

public Connection getConn()
{
if (conn==null)
{
try
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/sde" ;
conn = DriverManager.getConnection(url, "sde" , "pwd" );
conn.setAutoCommit(false);
}
catch(Exception e)
{
System.err.print(e);
}
}

return conn;
}

public ArrayList > getWinds()
{
ArrayList > result = new ArrayList >();

if ( this.getConn()==null )
return result;

try
{
String sql = "select *,ST_AsGeoJson(shape) from sde.wind";

Statement st = this.getConn().createStatement();
st.setFetchSize(0);
ResultSet rs = st.executeQuery(sql);
while (rs.next())
{
HashMap map = new HashMap ();
map.put("shape", rs.getString("ST_AsGeoJson"));
map.put("velocity", rs.getString("velocity"));
map.put("direction", rs.getString("direction"));
result.add(map);
}
rs.close();
st.close();
}
catch(Exception e)
{
System.err.print(e);
}

return result;
}

 

public ArrayList > getEffectZones()
{
ArrayList > result = new ArrayList >();

 

if ( this.getConn()==null )
return result;

try
{
String sql = "select *,ST_AsGeoJson(";
sql+= "ST_Buffer(";
sql+= "ST_PolygonFromText(";
sql+= "‘POLYGON((‘";
sql+= "||ST_X(shape)||‘ ‘||ST_Y(shape)||‘,‘";
sql+= "||ST_X(shape)+velocity*cos((direction+15)*PI()/180)/20||‘ ‘||ST_Y(shape)+velocity*sin((direction+15)*PI()/180)/20||‘,‘";
sql+= "||ST_X(shape)+velocity*cos((direction-15)*PI()/180)/20||‘ ‘||ST_Y(shape)+velocity*sin((direction-15)*PI()/180)/20||‘,‘";
sql+= "||ST_X(shape)||‘ ‘||ST_Y(shape)||‘))‘";
sql+= ")";
sql+= ", velocity/50";
sql+= ")";
sql+= ") ";
sql+="from sde.wind";

Statement st = this.getConn().createStatement();
st.setFetchSize(0);
ResultSet rs = st.executeQuery(sql);
while (rs.next())
{
HashMap map = new HashMap ();
map.put("shape", rs.getString("ST_AsGeoJson"));
map.put("velocity", rs.getString("velocity"));
map.put("direction", rs.getString("direction"));
result.add(map);
}
rs.close();
st.close();
}
catch(Exception e)
{
System.err.print(e);
}

return result;
}

}

Use of Postgresql+postgis