find polygon which contains the point using postgis/postgres

2
votes

I have 2 tables and both of them contain 2 columns. The first table contains 50 million rows while 2nd table contains 50 rows

Table1 - Columns: point_coord (datatype: point), id (integer)

Table2 - columns: region (datatype: geometry - they are polygons), id (integer)

The id column in the 1st table is empty and is to be filled using the 2nd table. If a point(point_coord) lies inside any of the 50(number of rows in 2nd table) polygons available in the second table then the corresponding id of that polygon gets filled in the id column of the 1st table.

I am new to using postgis using postgres. I was able to figure out that I need to use st_contains function but I am not sure what would be the best way to use it.

1
sqlpostgresqlpostgis

1 Answers

1
votes

Something like this

UPDATE table1
SET id = table2.id
WHERE st_intersects(table1.point_coord , table2.region )

This would be the basic structure. you need to explore the geometry function st_intersects of postGIS. You can also use st_within too.