POSTGIS增删改查 点、线、面表

一、点表（3857坐标系）

1.建表

    创建包含空间数据的表mytable。

 

CREATE TABLE pointtable ( 
  id SERIAL PRIMARY KEY,
  name VARCHAR(128),
   geom GEOMETRY(Point, 3857)
);

　　

2.添加GIST索引

CREATE INDEX pointtable_gix ON pointtable USING GIST (geom); 

　　

3.插入数据

INSERT INTO pointtable (NAME, geom)
VALUES
    (
        'p1',
        st_transform (
            ST_GeomFromText ('POINT(105.1 30)', 4326),
            3857
        )
    );

INSERT INTO pointtable (NAME, geom)
VALUES
    (
        'p2',
        st_transform (
            ST_GeomFromText ('POINT(105.2 30)', 4326),
            3857
        )
    );

INSERT INTO pointtable (NAME, geom)
VALUES
    (
        'p3',
        st_transform (
            ST_GeomFromText ('POINT(105.3 30)', 4326),
            3857
        )
    );

4.字段自增

alter table "public"."user" alter column id set default nextval('userTable_id_seq');

CREATE SEQUENCE userTable_id_seq
START WITH 2
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

 

5.增加字段

ALTER TABLE shenzhen_roads
ADD COLUMN source INTEGER,
ADD COLUMN target INTEGER,
ADD COLUMN cost DOUBLE PRECISION,
ADD COLUMN reverse_cost DOUBLE PRECISION;