Архив рубрики: Базы данных

Здесь размещаются заметки о базах данных

Grib2 to PostGIS

Now fire up Postgres and run the interactive psql terminal. First, enable the PostGIS extension on the database. At the psql prompt:

Conversion from GRIB2 to CSV is now a one-liner:

Next, we need to create table to hold our downloaded forecast data and a new spatial attribute. PostGIS provides both Geometry and Geography data types. The Geometry type offers better performance and advanced features at the expense of accuracy on large scales. Since our data is nationwide, we’ll opt for the Geography type:

Finally, import the CSV and populate the spatial attribute as a function of longitude and latitude coordinates:

Notice that we haven’t loaded anything into the long_lat column. This is the Geography column and must be computed by PostGIS using the longitude and latitude values we read in from the CSV file. SRID 4326 = WGS84.

BackUp part of table

UPSERT через функцию

— Комбинация колонок должна быть уникальной!!!

— Name: UniStChDate; Type: CONSTRAINT; Schema: public; Owner: maxselym; Tablespace:

ALTER TABLE ONLY «Data_ch»
ADD CONSTRAINT «UniStChDate» UNIQUE («Station_ID», «Chanel_ID», «DateTime»);

CREATE OR REPLACE FUNCTION merge_db(«Station_ID_in» bigint, «Chanel_ID_in» integer, «DateTime_in» timestamp without time zone, «val_in» real) RETURNS VOID AS
$$
BEGIN
LOOP
— first try to update the key
— UPDATE db SET b = data WHERE a = key;
UPDATE «Data_ch» Set «val» = «val_in» WHERE («Station_ID»= «Station_ID_in» and «Chanel_ID» = «Chanel_ID_in» and «DateTime» = «DateTime_in»);
IF found THEN
RETURN;
END IF;
— not there, so try to insert the key
— if someone else inserts the same key concurrently,
— we could get a unique-key failure
BEGIN
—        INSERT INTO db(a,b) VALUES (key, data);
INSERT INTO «Data_ch» («Station_ID», «Chanel_ID», «DateTime», «val») VALUES («Station_ID_in»,»Chanel_ID_in»,»DateTime_in»,»val_in»);
RETURN;
EXCEPTION WHEN unique_violation THEN
— do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

—SELECT merge_db(1,110,’2014-03-05 09:00:00′,15);

PSQL Dump

Dump:

Restore:

PostgreSQL установка

opensuse:~ #

local   all         all                               ident sameuser
host    all         all         127.0.0.1/32          ident sameuser
host    all         all         ::1/128               ident sameuser

to

local   all         all                               md5 sameuser
host    all         all         127.0.0.1/32          md5 sameuser
host    all         all         ::1/128               md5 sameuser

In phpPgAdmin in config.inc.php check localhost