在PostgreSQL中从CSV文件复制postgis数据

czfnxgou 于 2023-10-13 发布在 PostgreSQL

关注(0)|答案(1)|浏览(149)

我需要从CSV文件导入名称和几何数据到PostgreSQL。
individuals.csv文件格式如下：

first_name,last_name,location
Russell,Wolff,{"type":"Feature","geometry":{"type":"Point","coordinates":[73.0786759127481,29.730356801638685]},"properties":{}}

CSV文件中的所有几何类型都是"type":"Point"，并且该文件具有大约500K+记录。以上只是一个记录示例。
我创建了下面的表格来存储数据：

CREATE TABLE individuals (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  location_point GEOGRAPHY(Point, 4326) -- Use GEOGRAPHY data type for storing points
);

我写了下面的plpgsql函数来做同样的事情：

CREATE OR REPLACE FUNCTION insert_csv_data(directory text) RETURNS void AS $$
DECLARE
    file_name text;
    csv_data json;
begin
    -- Loop through GeoJSON files in the specified directory
    FOR file_name IN 
        SELECT *
        FROM pg_ls_dir(directory)
    loop
        IF file_name LIKE '%.csv' then

        RAISE NOTICE 'GeoJSON data: %', pg_read_file('path/to/individuals.csv');
        
        COPY individuals(first_name, last_name, location_point)
        FROM 'path/to/individuals.csv'
        CSV HEADER;
        END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

因此，我的想法是将directory传递给包含多个CSV文件的函数，然后将每个文件中的数据COPY传递给表个体。所以我在每个file_name上运行一个循环。
当我运行这个函数时：
SELECT public.insert_csv_data('path/to/directory');
我有一个
RAISE NOTICE 'GeoJSON data: %', pg_read_file('path/to/individuals.csv');是个人.csv文件的路径，它正确打印数据，如下所示：

GeoJSON data: first_name,last_name,location
Russell,Wolff,{"type":"Feature","geometry":{"type":"Point","coordinates":[73.0786759127481,29.730356801638685]},"properties":{}}

我得到一个错误：

SQL Error [22P04]: ERROR: extra data after last expected column
  Where: COPY individuals, line 2: "Russell,Wolff,{"type":"Feature","geometry":{"type":"Point","coordinates":[73.0786759127481,29.730356..."
SQL statement "COPY individuals(first_name, last_name, location_point)
        FROM 'path/to/individuals.csv'
        CSV HEADER"
PL/pgSQL function insert_csv_data(text) line 15 at SQL statement

我的问题是：
1.现在我正在运行COPY命令，并将FROM作为1个文件的硬编码路径。如何动态地替换每个file_name的路径，例如COPY FROM中的directory || '/' || file_name，以便从该文件复制数据，然后复制后续文件？
1.我需要从CSV中提取,之后的geometry键，然后将其插入location_point GEOGRAPHY(Point, 4326)列。我怎么能做到呢？
我被困在这里，任何帮助都将不胜感激。谢谢你，谢谢

csv

来源：https://stackoverflow.com/questions/77209610/copy-postgis-data-from-csv-files-in-postgresql

1条答案

按热度按时间

uwopmtnx1#

现在我正在运行COPY命令，并将FROM作为1个文件的硬编码路径。如何动态地替换每个file_name的路径，例如COPY FROM中的directory || '/' || file_name，以便从该文件复制数据，然后复制后续文件？
您可以在PL/pgSQL块中使用动态SQL：

do $f$
declare 
    v_file text:=concat('/tmp/','individuals.csv');
begin 
    execute format('copy test from %1$L', v_file);
end $f$;

将text变量直接传递给copy命令是行不通的，调用text返回函数（如concat()或||）也是行不通的。该命令需要一个文字，上面的动态SQL允许您首先执行函数，然后在将其作为文字传递之前评估保存其结果的变量。
我需要从CSV中提取几何键，然后将其插入到列location_point GEOGRAPHY（Point，4326）中。我怎么能做到呢？
由于文件的结构，copy命令：

COPY individuals(first_name, last_name, location_point) 
FROM 'path/to/individuals.csv' CSV HEADER;

相当于这样的insert语句：

INSERT INTO individuals(
    first_name, 
    last_name, 
    location_point )
VALUES (
    'Russell',
    'Wolff',
    '{"type":"Feature"',
    '"geometry":{"type":"Point"',
    '"coordinates":[73.0786759127481',
    '29.730356801638685]}',
    '"properties":{}}' );

这将抛出一个错误;值比列多，因为copy默认为delimiter ','。
DELIMITER指定在文件的每一行中分隔列的字符。**默认为文本格式的制表符，CSV的逗号。**必须是单个单字节字符。
除非使用正确的字符串引号，否则它会在找到逗号的地方分割输入行，因此在本例中，它会将最后一个字段分割为多个部分。要解决这个问题，您可以
1.通过一个接受location五部分的中间表运行数据，然后使用concat_ws(',',...)重新组装它。使用quote '~'（或文件中未使用的任何字符）覆盖默认的quote '"'-否则copy将解释并删除最后一列中的所有双引号"：demo1

CREATE TABLE individuals_raw(
    first_name text, 
    last_name text, 
    location_point_part1 text, 
    location_point_part2 text, 
    location_point_part3 text, 
    location_point_part4 text, 
    location_point_part5 text);

COPY individuals_raw 
FROM '/tmp/individuals.csv' CSV HEADER QUOTE '~';

INSERT INTO individuals (first_name,last_name,location_point)
SELECT first_name, 
       last_name, 
       ST_MakePoint(
           location_jsonb['geometry']['coordinates'][0]::float
          ,location_jsonb['geometry']['coordinates'][1]::float ) as location_point
FROM (SELECT first_name, 
             last_name, 
             concat_ws(',',location_point_part1,
                           location_point_part2,
                           location_point_part3,
                           location_point_part4,
                           location_point_part5)::jsonb as location_jsonb
      FROM individuals_raw) a;

如果您的location_point字段结构在整个文件中不是恒定的，则可能会中断。
1.更稳健：用单引号将location_point字段括起来，并将quote $$'$$或quote ''''参数添加到copy命令中。将;上的逗号,替换为，并添加delimiter ';'参数：demo2

CREATE TABLE individuals_raw(
  first_name text, 
  last_name text, 
  location_point jsonb);

COPY individuals_raw(first_name, last_name, location_point) 
FROM 'path/to/individuals.csv' CSV HEADER DELIMITER ';' QUOTE '''';

INSERT INTO individuals (first_name,last_name,location_point)
SELECT first_name, 
       last_name, 
       ST_MakePoint(
           location_point['geometry']['coordinates'][0]::float
          ,location_point['geometry']['coordinates'][1]::float ) as location_point
FROM individuals_raw;

替换可以用sed来完成，例如：

sed -i -e 's/,/;/1' -e "s/,\(.*\)$/;'\1'/1" /tmp/individuals.csv

模式末尾的1表示它将替换一行中的第一个逗号。在第一次替换之后，第二个逗号变成了第一个逗号，所以第二个模式也是针对第一个查找并替换它，但也用单引号包围所有内容，直到行尾。

赞(0）回复(0）举报 2023-10-13

我来回答

在PostgreSQL中从CSV文件复制postgis数据

1条答案

相关问题

热门标签

最新问答