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!

  • It would help if you posted any output generated by PostgreSQL, to help us locate the source of the problem. – MerseyViking Mar 8 '12 at 10:42

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.