0

I've got two tables which I want to combine and enrich with each others data. I'm getting stuck on the join part, as this needs to be done via a spatial query.

First the table structure: a table with addresses and a table with ensembles.

address: zipcode | number | addition | geom_point
ensemble: id | ensemble_id | geom_polygon

The resulting table I want to achieve has a row for every address with the corresponding ensemble id. Corresponding means: where the geom_point is within geom_polygon.

There can be addresses which have their point outside of the geom_polygon. The ensemble ID should then remain NULL

Is it possible to use some kind of spatial function in the join part of a query?

SELECT a.*, e.ensemble_id
FROM address as a
LEFT JOIN ensemble as e ON [[a.geom_point within e.geom_polygon]]
4
  • Can you do joins without common field? Isn't this querry do what you want: SELECT a.*, e.ensemble_id FROM address as a, ensemble as e WHERE ST_Within(a.geom_point, e.geom_polygon). Plz clarify about ensemble_id - do you want to update it? If yes - with what an in which table? Commented Nov 5, 2014 at 11:14
  • There is no need to be able to update the table's. It's just a view. So the query you give should also be possible. There is indeed no common field between the two tables, except for the spatial 'common' field.
    – stUrb
    Commented Nov 5, 2014 at 12:52
  • Do you need any further help? Commented Nov 5, 2014 at 13:41
  • Maybe I'm now trying the query's :)
    – stUrb
    Commented Nov 5, 2014 at 13:47

1 Answer 1

1

The query you need:

SELECT a.*, e.ensemble_id 
FROM address as a, ensemble as e  
WHERE ST_Within(a.geom_point, e.geom_polygon)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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