Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
    
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

1 Answer 1

up vote 3 down vote accepted

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;
share|improve this answer
    
Thanks! Error was in the detail ;) –  Styp Mar 8 '12 at 16:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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