автоматический расчет площадей полигонов средствами PostGIS

VorSer
Интересующийся
Сообщения: 24
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: автоматический расчет площадей полигонов средствами Post

Сообщение VorSer » 18 июл 2017, 23:39

Александр Мурый писал(а):Рабочий пример триггера, который использует созданную функцию для расчёта площади
выполнил запрос, заменив предварительно "import.osm_admin" на свою таблицу "oopt_pgn_all_wgs84"
появился триггер и триггерная функция calc_area().
При попытке построить полигон, или сдвинуть узел существующего QGIS выводит:
Ошибки: ОШИБКА: не добавлен 1 объект.

Ошибки источника:
Ошибка PostGIS при добавлении объектов: ERROR: record "new" has no field "geometry"
CONTEXT: SQL statement "SELECT ST_Area(ST_Transform(NEW.geometry, 32637))/10000"
PL/pgSQL function calc_area() line 3 at assignment

trir
Гуру
Сообщения: 3686
Зарегистрирован: 09 апр 2010, 19:30
Статьи: 1
Репутация: 618
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: автоматический расчет площадей полигонов средствами Post

Сообщение trir » 19 июл 2017, 05:14

Нет поля geometry
как всё сложно, когда не понимаешь то, что делаешь

Александр Мурый
Гуру
Сообщения: 5149
Зарегистрирован: 26 сен 2009, 16:26
Статьи: 3
Проекты: 5/1
Репутация: 776
Ваше звание: званий не имею
Откуда: Москва

Re: автоматический расчет площадей полигонов средствами Post

Сообщение Александр Мурый » 19 июл 2017, 06:51

Замените "geometry" на правильное название поля геометрии в таблице "oopt_pgn_all_wgs84". Обычно это либо "geom", либо "shape", либо что-то типа того.
Редактор материалов, модератор форума

VorSer
Интересующийся
Сообщения: 24
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: автоматический расчет площадей полигонов средствами Post

Сообщение VorSer » 19 июл 2017, 10:05

Замените "geometry" на правильное название поля геометрии в таблице "oopt_pgn_all_wgs84". Обычно это либо "geom", либо "shape", либо что-то типа того.
Спасибо огромное! Работает. В PGAdmin в заголовке таблицы имя (geom) и тип данных (geometry), а я этому как-то значения не придал.
Подскажите, нужно ли дополнять функцию условием, в соответствие с которым будет выбираться зона UTM, или там разница будет незначительная для точности в 0,001 га?
В моем случае выбор из двух вариантов 32636/32637, думаю можно долготу центроида в WGS84 взять относительно 36 в.д.

trir
Гуру
Сообщения: 3686
Зарегистрирован: 09 апр 2010, 19:30
Статьи: 1
Репутация: 618
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: автоматический расчет площадей полигонов средствами Post

Сообщение trir » 19 июл 2017, 11:10

Если вы будете сравнивать площади в МСК69 и UTM - то это разные площади и разница около 0.5%

Александр Мурый
Гуру
Сообщения: 5149
Зарегистрирован: 26 сен 2009, 16:26
Статьи: 3
Проекты: 5/1
Репутация: 776
Ваше звание: званий не имею
Откуда: Москва

Re: автоматический расчет площадей полигонов средствами Post

Сообщение Александр Мурый » 19 июл 2017, 11:17

Функция для вычисления зоны UTM (и соотв-го кода EPSG) (отсюда):

Код: Выделить всё

-- Function: utmzone(geometry)
-- DROP FUNCTION utmzone(geometry);
-- Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom)) ) FROM sometable;

CREATE OR REPLACE FUNCTION utmzone(geometry)
RETURNS integer AS
$BODY$
DECLARE
 geomgeog geometry;
 zone int;
 pref int;

BEGIN
 geomgeog:= ST_Transform($1,4326);

 IF (ST_Y(geomgeog))>0 THEN
	pref:=32600;
 ELSE
	pref:=32700;
 END IF;

 zone:=floor((ST_X(geomgeog)+180)/6)+1;

 RETURN zone+pref;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
Соответственно, меняем функцию "calc_area":

Код: Выделить всё

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.area := ST_Area(ST_Transform(NEW.geom, utmzone(ST_Centroid(NEW.geom))))/10000;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
Редактор материалов, модератор форума

VorSer
Интересующийся
Сообщения: 24
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: автоматический расчет площадей полигонов средствами Post

Сообщение VorSer » 20 июл 2017, 00:50

Для таблиц с геометрией в WGS84 все прекрасно работает. Да, площадь несколько отличается от указанной в зем.деле, но для наших целей пока этого вполне хватает.
А вот таблицы в МСК победить не смог, хотя вроде и в spatial_ref_sys добавил все три зоны.
QGIS, при попытке редактирования слоя при включеном тригере пишет мне
Ошибка PostGIS при изменении геометрии: ERROR: Input geometry has unknown (0) SRID
CONTEXT: PL/pgSQL function utmzone(geometry) line 8 at assignment
PL/pgSQL function calc_area() line 3 at assignment
Или я извращенец и зачем-то пытаюсь пересчитать МСК в UTM, хотя можно посчитать площадь прямо в МСК?

trir
Гуру
Сообщения: 3686
Зарегистрирован: 09 апр 2010, 19:30
Статьи: 1
Репутация: 618
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: автоматический расчет площадей полигонов средствами Post

Сообщение trir » 20 июл 2017, 06:48

хотя вроде и в spatial_ref_sys добавил все три зоны.
тогда у них есть srid
Или я извращенец и зачем-то пытаюсь пересчитать МСК в UTM, хотя можно посчитать площадь прямо в МСК?
ага :mrgreen:

VorSer
Интересующийся
Сообщения: 24
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: автоматический расчет площадей полигонов средствами Post

Сообщение VorSer » 20 июл 2017, 16:59

При попытке рассчитать площади слоя в МСК столкнулся со следующей проблемой.
Запрос:

Код: Выделить всё

UPDATE table_1
	SET area = (SELECT ST_Area(geom)/10000)
вернул вот такое сообщение:
Query returned successfully: 92 rows affected, 63 msec execution time.
Посмотрел в таблицу, а там почему только у двух полигонов в поле geom есть содержимое (насколько понял WKB), а у остальных пусто, хотя сами полигоны на карте отображаются и QGIS даже расчеты какие-то производит над ними.

Слой был импортирован из shp через плагин DB Manager. Или мне в другую уже ветку пора?
Вложения
geom.JPG
geom.JPG (146.46 КБ) 2134 просмотра

trir
Гуру
Сообщения: 3686
Зарегистрирован: 09 апр 2010, 19:30
Статьи: 1
Репутация: 618
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: автоматический расчет площадей полигонов средствами Post

Сообщение trir » 20 июл 2017, 20:46

она и не должна отображаться, выполните запрос:
select ST_AsText(geom) from table_1

Александр Мурый
Гуру
Сообщения: 5149
Зарегистрирован: 26 сен 2009, 16:26
Статьи: 3
Проекты: 5/1
Репутация: 776
Ваше звание: званий не имею
Откуда: Москва

Re: автоматический расчет площадей полигонов средствами Post

Сообщение Александр Мурый » 20 июл 2017, 22:39

VorSer писал(а): Запрос:

Код: Выделить всё

UPDATE table_1
	SET area = (SELECT ST_Area(geom)/10000)
Немного о другом: здесь SELECT лишний, можно оставить просто SET area = ST_Area(geom)/10000
Редактор материалов, модератор форума

VorSer
Интересующийся
Сообщения: 24
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: автоматический расчет площадей полигонов средствами Post

Сообщение VorSer » 21 июл 2017, 00:25

Обобщу, ибо все заработало, благодаря старшим товарищам. А потом может пригодиться таким олухам как я, когда "некогда учиться - работать надо".
2.функция для определения зоны UTM создается SQL запросом:

Код: Выделить всё

CREATE OR REPLACE FUNCTION utmzone(geometry)
RETURNS integer AS
$BODY$
DECLARE
 geomgeog geometry;
 zone int;
 pref int;

BEGIN
 geomgeog:= ST_Transform($1,4326);

 IF (ST_Y(geomgeog))>0 THEN
   pref:=32600;
 ELSE
   pref:=32700;
 END IF;

 zone:=floor((ST_X(geomgeog)+180)/6)+1;

 RETURN zone+pref;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
2.функция для расчета площади (га) в UTM с автоматическим определением зоны создается SQL запросом:

Код: Выделить всё

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.[имя_поля] := ST_Area(ST_Transform(NEW.geom, utmzone(ST_Centroid(NEW.geom))))/10000;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
3. функция для расчета площади (га) в МСК создается SQL запросом:

Код: Выделить всё

CREATE OR REPLACE FUNCTION calc_msk_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.[имя_поля] := ST_Area(NEW.geom)/10000;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
4. для расчета площади в UTM в таблице создается триггер:

Код: Выделить всё

CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON [имя_таблицы]
    FOR EACH ROW EXECUTE PROCEDURE calc_area();
5. для расчета площади в МСК в таблице создается триггер:

Код: Выделить всё

CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON [имя_таблицы]
    FOR EACH ROW EXECUTE PROCEDURE calc_msk_area();
6. площади существующих полигонов и мультиполигонов в UTM расcчитываются:

Код: Выделить всё

UPDATE [имя_таблицы] SET [имя_поля] = ST_Area(ST_Transform(geom, utmzone(ST_Centroid(geom))))/10000
7. площади существующих полигонов и мультиполигонов в МСК расcчитываются соответственно:

Код: Выделить всё

UPDATE [имя_таблицы] SET [имя_поля] = ST_Area(geom)/10000
Наверняка можно как-то изящнее, но это вполне работает и для небольших проектов (в моем случае из нескольких слоев, до 1000 объектов в каждом, в масштабах одной области) по-моему годится. Лучше было бы пересчитывать в МСК а не в UTM, но подозреваю, что это будет несколько сложнее - мне пока не удалось научить postGIS/Qgis воспринимать МСК как родную, а там еще и зоны определять придется как-то. Буду рад, если расскажете, но пока спасибище trir и Александр Мурый, за решение простой но важно задачки.
Последний раз редактировалось VorSer 21 июл 2017, 19:13, всего редактировалось 1 раз.

Аватара пользователя
Филиппов Владислав
Гуру
Сообщения: 992
Зарегистрирован: 17 фев 2006, 06:28
Проекты: 1
Репутация: 134
Ваше звание: Геннадич
Откуда: Новосибирск
Контактная информация:

Re: автоматический расчет площадей полигонов средствами Post

Сообщение Филиппов Владислав » 21 июл 2017, 08:06

"некогда учиться - работать надо"
с такими советами работа всегда будет дерьмо, а олух так и останется олухом :(
всегда нужно разбираться с тем что используешь

Александр Мурый
Гуру
Сообщения: 5149
Зарегистрирован: 26 сен 2009, 16:26
Статьи: 3
Проекты: 5/1
Репутация: 776
Ваше звание: званий не имею
Откуда: Москва

Re: автоматический расчет площадей полигонов средствами Post

Сообщение Александр Мурый » 21 июл 2017, 09:35

Филиппов Владислав писал(а):"
с такими советами работа всегда будет дерьмо
Ждём ваши варианты советов.
Редактор материалов, модератор форума

trir
Гуру
Сообщения: 3686
Зарегистрирован: 09 апр 2010, 19:30
Статьи: 1
Репутация: 618
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: автоматический расчет площадей полигонов средствами Post

Сообщение trir » 21 июл 2017, 09:57

Ждём ваши варианты советов.
книжки читать

Ответить

Вернуться в «PostGIS/PostgreSQL»

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и 3 гостя