PostGIS UPDATE Statement with ST_Contains(table1.geom, table2.geom)

Question

I'm strugeling with a query I should write to UPDATE my dataset.

First of all, I have 2 tables containing data and I need to mix it.

Tabel 1 -- lets call it names:

• name_str
• the_geom (points)

Table 2 -- lets call it objects:

• the_geom (polygons)

Now i want to do a cross selection and update my objects table. If a point is in the polygon it should update the objects.name_str with the value in the names.name_str.

Please keep in mind that I have over 2 mil datasets in the table!

I tried a selection query like this without success:

SELECT * FROM name, object WHERE st_contains(the_geom, way)='t' LIMIT 10

Please help! I have no clue where the error is hidden!

Answer 1

The first thing I see is your test ='t' That I think will fail because ST_Contains() returns a boolean, so you don't need any explicit test.

Also, make sure you specify your geometry properly. The docs say ST_Contains(geometry geomA, geometry geomB):

Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

So as you're only dealing with point in polygon tests, you'll have to make sure your polygon geometry is the first parameter, and your point geometry is the second.

So your query should be:

SELECT * FROM name, object WHERE st_contains(object.the_geom, name.the_geom) LIMIT 10;