[postgis-users] Check if points are inside polygon performance problem

[liglio.pessoal at nexxa.com.br]

Hi,

 

I am trying to update a flag indicating if a point is inside a country. I
have a table with 27 states (tb_state) and a partitioned table with points
(tb_heat), child tables per year, from tb_heat_2000 to tb_heat_2025.

The update longs forever, and I have to cancel. I don't't know if is a
postgresql tune problem, or a SQL problem.

 

tb_state has index for column state_area

tb_heat and childs have index for columns din_heat, point_heat

 

tb_state - 27 rows

tb_heat  - From '2020-12-09 00:00:00' to '2020-12-10 00:00:00' (1day) - 4002
rows


UPDATE tb_heat SET flg_insidecountry = ST_CONTAINS(foo.country,
tb_heat.point_heat) FROM (SELECT ST_UNION(tb_state.area_state) as country
FROM tb_state) AS foo 
WHERE tb_heat.din_heat >= '2020-12-09 00:00:00' and tb_heat.din_heat <
'2020-12-10 00:00:00'



 

Regards,

 

Liglio

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20201212/a120945b/attachment.html>