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

[Hugues François]

Hi,

The ST_union costs and like this you don't take advantage of any spatial index. You may want to create a temporary table made of the merged states with a gist index in order to use it for updating your table. 

HTH,

Hug

Le 13 déc. 2020 09:59, liglio.pess...@nexxa.com.br a écrit :

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

 

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users