csv文件导入到PostGIS（一）

 一种导入csv文件的方式是通过copy命令。

1.建表

CREATE TABLE firenews
(
  x float8,
  y float8,
  place varchar(100),
  size float8,
  update date,
  startdate date,
  enddate date,
  title varchar(255),
  url varchar(255),
  the_geom geometry(POINT, 4326)
);

2.导入csv文件

COPY firenews (x, y, place, size, update, startdate, enddate, title, url) 
FROM 'D:\firenews.csv' WITH CSV HEADER;

3.查看导入的数据总数   

SELECT COUNT(*) FROM firenews;

 在pgAdmin中的查询结果如下:

4.生成几何数据

UPDATE firenews SET the_geom = ST_SetSRID(ST_MakePoint(x,y), 4326);

5.查询几何数据

SELECT place,x,y, ST_AsText(the_geom) AS wkt_geom 
FROM firenews 
ORDER BY place 
LIMIT 5;

在pgAdmin中的查询结果如下:

 6.创建索引

CREATE INDEX idx_firenews_geom ON firenews USING GIST (the_geom);

7.QGIS中显示几何数据