Postgis practice 20201202

create_test2.sql

DROP TABLE IF EXISTS test;
CREATE TABLE test (
	id serial,
	long double precision,
	lat double precision,
	country varchar,
	species varchar,
	geom geometry(POINT, 4326),
	CONSTRAINT pk_test PRIMARY KEY (id)
);
CREATE INDEX test_gist ON test USING gist(geom);
DROP TABLE IF EXISTS test_staging;
CREATE TABLE test_staging (
	id serial,
	long double precision,
	lat double precision,
	country varchar,
	species varchar,
	CONSTRAINT pk_test_staging PRIMARY KEY (id)
);
COPY test_staging FROM '/tmp/s.csv' DELIMITER AS ',';
-- create geometry column
INSERT INTO test (id, long, lat, country, species, geom)
	SELECT 
		id,long,lat,country,species,
		ST_SetSRID(ST_Point(long,lat), 4326) AS geom
	FROM test_staging;

s.csv