Postgresql PostGIS Usage Summary

ways to create a database with PostGIS functionality1. Check the default created GIS library when installing2. Create a new database to execute create extension PostGISCreate a table with a Geography type column1. First create a table that does not contain a geographic column

CREATE TABLE public. " Station_address "
(
"ID" character (.) is not NULL,
"Create_station_code" character varying (50),
"Coordinate_value" character varying (200),
"Point" text,
"REMARK" character varying (200),
CONSTRAINT "Station_address_pkey" PRIMARY KEY ("ID")
)

Import data to the data table "Station_address" Using the Copy command (http://blog.csdn.net/namelb/article/details/7909910)

Copy "Station_address" from ' d:/test/testdata.csv ' delimiter as ', ' csv quote as ' "'

--Add a Geography type column

SELECT addgeometrycolumn (' station_address ', ' point_coordinate ', 4326, ' point ', 2)

--query two points with a distance of less than 100
SELECT * from public. " Station_address "WHERE st_distance" ("Point_coordinate", St_geomfromtext (' Point (112.308407 22.192877) ', 4326)) = 0;

--Convert a field of string type to a polygon column

UPDATE "Express_range" SET "SHAPE" =st_makepolygon (St_geomfromtext (' LINESTRING (select REMARK "from" Express_range "t where T. " ID "=" Express_range "." ID ") ', 4326))

--To see if a point returns F within a polygon indicates that the T in the range is not within the polygon
SELECT St_contains (St_makepolygon (St_geomfromtext (' LINESTRING (121.312350 30.971457, 121.156783 31.092221, 121.353250 31.278195, 121.509125 31.157431, 121.312350 30.971457)), St_point (121.632378,31.07106))

--Display the Geom column in text form

Select St_astext ("SHAPE") from "Express_range"

Postgresql PostGIS Usage Summary