csv文件导入到PostGIS（二）

另外一种导入csv文件的方式是通过vrt文件。

1.在csv同目录下创建vrt文件

vrt文件的内容如下：

<OGRVRTDataSource>
    <OGRVRTLayer name="Global_24h">
        <SrcDataSource>Global_24h.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>EPSG:4326</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

用ogrinfo命令检查vrt文件正确性

> ogrinfo global_24h.vrt Global_24h -fid 1

结果如下：

INFO: Open of `global_24h.vrt'
      using driver `OGR_VRT' successful.

Layer name: Global_24h
Geometry: Point
Feature Count: 30326
Extent: (-155.284000, -40.751000) - (177.457000, 70.404000)
Layer SRS WKT:
GEOGCRS["WGS 84",
    DATUM["World Geodetic System 1984",
        ELLIPSOID["WGS 84",6378137,298.257223563,
            LENGTHUNIT["metre",1]]],
    PRIMEM["Greenwich",0,
        ANGLEUNIT["degree",0.0174532925199433]],
    CS[ellipsoidal,2],
        AXIS["geodetic latitude (Lat)",north,
            ORDER[1],
            ANGLEUNIT["degree",0.0174532925199433]],
        AXIS["geodetic longitude (Lon)",east,
            ORDER[2],
            ANGLEUNIT["degree",0.0174532925199433]],
    USAGE[
        SCOPE["unknown"],
        AREA["World"],
        BBOX[-90,-180,90,180]],
    ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
latitude: String (0.0)
longitude: String (0.0)
brightness: String (0.0)
scan: String (0.0)
track: String (0.0)
acq_date: String (0.0)
acq_time: String (0.0)
satellite: String (0.0)
confidence: String (0.0)
version: String (0.0)
bright_t31: String (0.0)
frp: String (0.0)
OGRFeature(Global_24h):1
  latitude (String) = -23.386
  longitude (String) = -46.197
  brightness (String) = 307.5
  scan (String) = 1.1
  track (String) = 1
  acq_date (String) = 2012-08-20
  acq_time (String) =  0140
  satellite (String) = T
  confidence (String) = 54
  version (String) = 5.0
  bright_t31 (String) = 285.7
  frp (String) = 16.5
  POINT (-46.197 -23.386)

2. csv文件导入到PostGIS

      导入csv文件的同时，将坐标系转为EPSG:3857。

ogr2ogr -f "PostgreSQL" -t_srs EPSG:3857 PG:"host=localhost port=5432 dbname=postgis_32_sample user=postgres password=postgres schemas=public" global_24h.vrt -lco GEOMETRY_NAME=the_geom

3.查看导入的数据总数

SELECT count(*) FROM global_24h;

在pgAdmin中的查询结果如下:

 4.查询几何数据

SELECT ogc_fid, latitude,longitude, ST_AsEWKT(the_geom) FROM global_24h LIMIT 5;

在pgAdmin中的查询结果如下:

 5.QGIS中显示几何数据