About PostGIS

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows the OpenGIS “Simple Features Specification for SQL” and has been certified as compliant with the “Types and Functions” profile.

PostGIS development was started by Refractions Research as a project in open source spatial database technology. PostGIS is released under the GNU General Public License. PostGIS continues to be developed by a group of contributors led by a Project Steering Committee and new features continue to be added.

The translation:

PostGIS has added support for geographic objects to the PostgreSQL object relational database. In effect, PostGIS has spatially enabled the PostgreSQL server to be used as a back-end Spatial database for geographic Information Systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows OpenGIS “Simple Feature Specification for SQL” and has been certified to conform to the “types and functions” profile.

The development of PostGIS was started by Refractions Research as a project in open source spatial database technology. PostGIS is distributed under the GNU General Public License. PostGIS continues to be developed by a group of contributors led by the project steering committee and continues to add new features.

PostGIS is an important plugin for PostgreSQL, which is the driving force of the GIS industry’s preferred database at home and abroad. If you are interested, you can collect them first and then study them slowly

DownLoad

In the url below, you can choose to download the version of Windows that works for you. If you are working in a production environment, you are advised not to select the latest release. Before you can download and install PostGIS, you need to have a PostgreSQL runtime environment.

In the url below, we select the PostGIS plugin for our own version of PostGreSQL, as these are validated and supported by the Official PostGIS team.

Download.osgeo.org/postgis/win…

At this site, you can select other versions of the installation package that suits you, including installation-free zip files.

postgis.net/install/

The installation

After downloading, install;

PostGIS is an easy installation, but PostGIS needs to be installed in the same directory as PostgreSQL (PostGIS needs to be installed in the PostgreSQL root directory).

You may or may not create a spatial database during installation. In your case, if you want to create a spatial database, you need to connect to the current PostgreSQL database.

Prepare PostGIS

  1. In the Start menu barPostGreSQLClick insidepgAdmin4;
  2. Click the Server in the upper left corner and enter the connection information as prompted to log in.
  3. Once logged in, create a database instance and create a new database;
  4. Click on database name -Extensions -right click on create-extension and enter:postgis;

Pre-knowledge of using PostGIS

1. Common GIS objects supported by PostGIS

The syntax for creating GIS objects should be familiar and easy to forget

• POINT(0 0) // POINT • LINESTRING(0 0,1,1,1 2) // line • POLYGON((0 0,4 0,4 4,0 4 0 0),(1 1, 2 1, 2 2, 1,1,1)) // MULTIPOINT • MULTIPOINT((0 0),(1 2)) // MULTIPOINT • MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) // multiline • MULTIPOLYGON(((0) 0,4,4, 4,0,4),(1, 2,1,2, 2,1,1), ((1-1-1-2-2-2, - 2-1, 1-1))) / / polyhedral • GEOMETRYCOLLECTION (POINT (2, 3), LINESTRING (2, 3, 3, 4)) / / geometric setCopy the code

2. SRID — The number of the geographic coordinate system of the storage space object. Its input and output can be through the following interface

bytea WKB = ST_AsBinary(geometry);
text WKT = ST_AsText(geometry);
geometry = ST_GeomFromWKB(bytea WKB, SRID);
geometry = ST_GeometryFromText(text WKT, SRID);
Copy the code
The sample
SELECT (ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place'); The output is as follows: (0101000020380100009 a99999999995fc0295c8fc2f5a84640, "A Place")Copy the code

3. Support the syntax of 3D GIS objects

PostGIS EWKB/EWKT added support for 3DM, 3DZ, 4D coordinates and embedded SRID information.

• POINT(0 0 0) -- XYZ • SRID=32632; POINT (0, 0) - XY with SRID • POINTM (0, 0, 0) - XYM, POINT (0 0 0 0) - XYZM, SRID = 4326; MULTIPOINTM(0 0,1 2 1) -- XYM with SRID • MULTILINESTRING((0 0,1 1 0,1 2 1),(2 3 1,3 2 1)) • POLYGON((0 0,4 0 0,4 4 0,0 4 0,0 0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0) • MULTIPOLYGON((0 0 0,4 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 2 0,1 2 0,1 1 0)),((-1-1 0, -1-2 0, -2-2 0, -2-1 0, -1-1 0))) MULTICURVE(0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4)) • POLYHEDRALSURFACE((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)), ((1 1 1 0 0 0 0, 0, 0, 1, 0, 0, 0, 0, 0)), ((1 0 0 0 0, 0, 0, 1, 0, 0, 1, 0, 0, 0)), ((1 1 1 1 0, 1, 0, 1, 1, 0, 0, 1 1 0), ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 0 1))) • TRIANGLE ((0 0, 0 9, 9 0, TIN(((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0)))Copy the code

Conversion interface between 3DM, 3DZ and 4D:

bytea EWKB = ST_AsEWKB(geometry);
text EWKT = ST_AsEWKT(geometry);
geometry = ST_GeomFromEWKB(bytea EWKB);
geometry = ST_GeomFromEWKT(text EWKT)
Copy the code
The sample
SELECT 'SRID=4; POINT(0 0)'::geometry; The output is as follows: 01010000200400000000000000000000000000000000000000Copy the code

4. Curved geometric entities

The SQL Multimedia Application Space specification extends the simple functions of the SQL specification and defines some circular interpolation curves. Sql-mm is defined to include 3DM, 3DZ, and 4D coordinates, but does not allow SRID information to be embedded. The well-known Text extension is not yet fully supported.

Here are some simple examples of curvilinear geometry.

CIRCULARSTRING(0 0, 1 1, 1 0) • CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0) • COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1) CIRCULARSTRING(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, • MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4)Copy the code

5. Create a tablespace table

CREATE TABLE global_points (
id SERIAL PRIMARY KEY,
name VARCHAR(64),
location GEOGRAPHY(POINT,4326)
);
Copy the code

6. Insert data

INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326; POINT(-110 30)'); INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326; POINT(-109 29)'); INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326; POINT(0 49)');Copy the code

7. Build an index

Creating an index works the same as GEOMETRY. PostGIS will note that the column type is GEOGRAPHY and create an

appropriate sphere-based index instead of the usual planar index used for GEOMETRY.

CREATE INDEX global_points_gix ON global_points USING GIST ( location );
Copy the code

8. Query and calculation

SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326; POINT(-110 29)':: geography, 1000000);Copy the code
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT (21.96 64.15) : : geography); SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15) '::geometry);Copy the code

9. Spatial_ref_sys table

Spatial_ref_sys table is a PostGIS and OGC-compatible database table that lists over 3000 known spatial reference systems and the details required to transform/project between them. While PostGIS’s Spatial_ref_SYS table contains more than 3,000 definitions of the more commonly used spatial reference system definitions, it does not contain all known definitions, and if you are familiar with Proj4’s structure, you can define your own projections.

10. SRID

An integer value that uniquely identifies the spatial reference system (SRS) in the database.

11. Create a tablespace

CREATE TABLE ROADS (ID serial, ROAD_NAME TEXT, Geom Geometry (LINESTRING,4326)); ALTER TABLE roads ADD COLUMN geom2 Geometry (LINESTRINGZ,4326);Copy the code

12. Create views to simplify SQL statements

CREATE VIEW public. Vwmytablemercator AS SELECT GID, ST_Transform(geom, 3395) AS geom, f_name FROM public. Mytable;  DROP VIEW public. Vwmytablemercator;Copy the code

13. Load GIS (Vector) data

INSERT INTO roads (id, geom, road_name) VALUES (1,'SRID=4326; LINESTRING (191232, 243118191, 108, 243242) ': : geometry,' Jeff Rd '); INSERT INTO roads (id, geom, road_name) VALUES (2,'SRID=4326; LINESTRING (189141, 244158189, 265, 244817) ', 'Geordie Rd'); INSERT INTO roads (id, geom, road_name) VALUES (3,'SRID=4326; LINESTRING (192783, 228138192, 612, 229814) ', 'Paul St'); INSERT INTO roads (id, geom, road_name) VALUES (4,'SRID=4326; LINESTRING (189412, 252431189, 631, 259122) ', 'Graeme Ave'); INSERT INTO roads (id, geom, road_name) VALUES (5,'SRID=4326; LINESTRING (190131, 224148190, 871, 228134) ', 'Phil Tce'); INSERT INTO roads (id, geom, road_name) VALUES (6,'SRID=4326; LINESTRING (198231, 263418198, 213, 268322) ', 'Dave Cres'); COMMIT;Copy the code

14. Import data

14.1 use PSQL
psql -d [database] -f roads.sql
Copy the code
14.2 use shp2pgsql
# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > roads.sql
# psql -d roadsdb -f roads.sql
Copy the code
14.3 a Unix command
# shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb
Copy the code

15. Retrieve data

SELECT id, ST_AsText(geom) AS geom, road_name FROM roads; SELECT id, road_name FROM roads WHERE geom='SRID=312; LINESTRING (191232, 243118191, 108, 243242) ': : geometry; SELECT id, road_name FROM roads WHERE ST_Intersects(geom, 'SRID=312; POLYGON((...) ) '); SELECT ST_AsText(geom) AS geom FROM roads WHERE geom &&st_makeenvelope (191232, 243117,191232, 243119312432 6);Copy the code

16. pgsql2shp

Pgsql2shp [<options>] <database> [<schema>.]<table> PGSQL2SHp [<options>] <database> <query>Copy the code

17. Build an index

Indexes make it possible to handle large data integrations in spatial databases.

GiST Indexes

GiST, short for “Universal search tree,” is a common form of index. In addition to GIS indexing, GiST is also used to speed up searches for a variety of irregular data structures (integer arrays, spectral data, and so on) that are not suitable for ordinary B-tree indexes.

CREATE INDEX [indexName] ON [tablename] USING GIST ([geometryfield]); // CREATE INDEX [indexName] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd); // Make sure that the index can still be written to the table when it is updated or created; CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST ( [geometryfield] ); // Collect table statistics report to optimize query SQL VACUUM ANALYZE [table_name] [(column_name)];Copy the code
BRIN Indexes

BRIN stands for “block-wide index” and is a generic form of index introduced in PostgreSQL 9.5. BRIN is a loss-making index whose main purpose is to provide a trade-off between read and write performance. Its main goal is to work with very large tables, some of which have some natural associations with their physical location in the table. The physical locations in the table have some natural associations. In addition to GIS indexing, BRIN is also used to speed up searches on various regular or irregular data structures (integers, arrays, etc.)

CREATE INDEX [indexName] ON [tablename] USING BRIN ([geometryfield]); //3D INDEX CREATE INDEX [indexName] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d); //4D INDEX CREATE INDEX [indexName] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d); CREATE INDEX [indexName] ON [tablename] USING BRIN ([geometryfield]) WITH (pages_per_range = [number]);Copy the code
SP-GiST Indexes

Sp-gist, short for “Spatially split universal search tree,” is a universal index form that supports split search trees, such as quadtrees, K-d trees, and radians. What these data structures have in common is that they repeatedly divide the search space into partitions that do not require equal size. In addition to GIS indexing, SP-GIST is also used to speed up searches for many kinds of data, such as phone routes, IP routes, and substring searches. Like GiST indexes, SP-GIST indexes suffer because they store bounding boxes that contain spatial objects. Object. The SP-GIST index can be seen as an alternative to the GiST index. Performance testing has shown that SP-GIST indexes are particularly useful when there are many overlapping objects, known as “noodle data.” Once a GIS data table exceeds a few thousand rows, sp-GIST indexes can be used to speed up the spatial search of data.

CREATE INDEX [indexName] ON [tablename] USING SPGIST ([geometryfield]); //3D INDEX CREATE INDEX [indexName] ON [tablename] USING SPGIST ([geometryfield] spgist_geometry_ops_3d); // Make sure that the index can still be written to the table when it is updated or created; CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING SPGIST ( [geometryfield] ); // Collect table statistics to optimize query SQL VACUUM ANALYZE [table_name] [(column_name)];Copy the code

On the specific use of spatial index and the operation of the introduction, please refer to this article, here is not specific one by one.

To apply | PostgreSQL spatial index

That’s not exactly PostGreSQL, it’s PostGIS, and that’s the point of the extension: it’s invisible, but it’s everywhere!

18. Complex query example

SELECT the_geom FROM geom_table WHERE ST_Distance(the_geom, 'SRID=312; SELECT the_geom FROM geom_table WHERE ST_DWithin(the_geom, 'SRID=312; POINT(100000 200000)', 100)Copy the code

19. raster2pgsql

Raster2pgsql raster_optionS_go_here raster_file someschema.sometable > out. SQL Raster2pgsql -s 4326 -i-c -m *. Tif -f -t 100x100 public. Demelevation > elev. SQL PSQL -d gisdb -f elev / / raster data warehousing operation under Unix command raster2pgsql - 4326 - s I - C - M * tif - F - t 100 x100 public. Demelevation | PSQL -d gisdb // Create a full view, level 2 and level 4 overview table, Using replication model insert raster2pgsql - I - C - e - Y - 128 x128 - F - 26986 - s t l, 2, 4 bostonaerials2008 / *. JPG aerials. Boston | PSQL -u postgres -d gisdb -h localhost -p 5432 // get a list of raster types supported: Raster2pgsql -g CREATE TABLE myrasters(RID Serial primary key, rast raster); // CREATE INDEX myrasters_rast_ST_convexhull_idx ON myrasters USING gist(ST_ConvexHull(rast));Copy the code

20. PostGIS Geometry/Geography/Box Data Types

box2d

Box2d is a spatial data type used to represent a two-dimensional enclosing box of a geometry or collection of geometry. For example, the ST_Extent aggregate function returns a Box2D object. The notation contains the values of xmin, ymin, xmax, and ymax. These are the minimum and maximum of the X and Y axes. And the minimum and maximum of Y expansion.

box3d

Box3d is a PostGIS spatial data type used to represent a three-dimensional bounding box of a geometry or collection of geometry. A three-dimensional bounding box of geometry. For example, the ST_3DExtent aggregation function returns a Box3D object. The notation contains the values of xmin, ymin, zmin, xmax, ymax, zmax. These are the minimum and maximum X, Y, and Z values.

geometry

Geometry is a basic spatial data type of PostGIS, which is used to represent a feature in planar (Euclidean) coordinate system. All spatial operations on the geometry use the units of the spatial reference system in which the geometry is located.

geometry_dump

Geometry_dump is a composite data type that contains fields.

  • Geom – A reference to a geometry component
  • Path [] – A one-dimensional array of integers that defines the navigation path of the dumped geometry to the GEOM component. The path array starts at 1 (for example, path[1] is the first element.) It is used as an output type by the ST_Dump* family of functions to decompose a complex geometry into its constituent parts.
geography

Geography is a spatial data type used to represent a feature in a geodetic coordinate system. Geodetic coordinate system models use an ellipsoid to model the earth. By taking into account the ellipsoid model, more accurate results can be provided for geotypic spatial manipulation.

conclusion

In this article, we will give a systematic introduction to the background, download and installation of PostGIS, as well as some common prerequisites.

If you are interested in PostGIS, hope to help you ~