mysql gis postgre_Postgre GIS数据类型使用介绍

— gis类型主要分为两种：geometry和geometry，下面这些类型都是基于这两种类型的。

POINT(0 0)

LINESTRING(0 0,1 1,1 2)

POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

MULTIPOINT((0 0),(1 2))

MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))

MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))

GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))

— 使用gis插件

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

CREATE EXTENSION fuzzystrmatch;

CREATE EXTENSION postgis_tiger_geocoder;

— 创建几何类型的表

create table geometry01  (the_geom geometry, the_name  char varying(100));

INSERT INTO geometry01 ( the_geom, the_name ) VALUES ( ST_GeomFromText(‘POINT(-126.4 45.32)’, 312), ‘A Place’);

select * from geometry01

— 创建几何类型的表2

create table geometry02  (the_geom geometry, the_name  char varying(100));

INSERT INTO geometry02 ( the_geom, the_name )

VALUES ( ST_GeomFromText(‘LINESTRING(0 0,1 1,1 2)’, 312), ‘A Place’);

INSERT INTO geometry02 ( the_geom, the_name )

VALUES ( ST_GeomFromText(‘MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))’, 312), ‘A Place’);

INSERT INTO geometry02 ( the_geom, the_name )

VALUES ( ST_GeomFromEWKT(‘SRID=312;POINTM(-126.4 45.32 15)’), ‘A Place’ )

SELECT * FROM geometry02;

— 创建地理位置的表1

CREATE TABLE geography01(gid serial PRIMARY KEY, the_geog geography(POINT,4326) );

insert into geography01 values(1,ST_GeographyFromText(‘SRID=4326;POINT(-110 30)’));

select * from geography01;

参考：