I have a PostgreSQL database with PostGIS.
In a scheme are two tables.
Table one contains polygons with two depth values recorded per polygon.
These values are stored in the datamodel in columns 'drval1' and 'drval2'
Table two contains points that represent floating Aids to Navigation.
In the datamodel of this layer, information is stored that describes the physical characteristics of the AtoN.
I would like to make a view with a spatial join, but I don't know where to begin.
I need guidance and direction on how to move forward.
Both tables are automaticly synchronised with an external source at midnight every day by a script. At the same moment I would like to make a new view.
- The view be a join of table 2 with de drval1 and drval2 colums added from table 1
- Every drval1 and drval2 value in the view should be the value out of the polygon where the point is within.
Example: one AtoN (point) is within a deptharea (polygon) with a drval1 of 12 and a drval2 of 13. the view should have all the colums out of table 1 supplemented with the two values above.
I have the feeling i'm getting there, but not quite yet.
This is my query:
SELECT * FROM sandbox_daily_data.nl_aton_float_latest AS aton_float
JOIN sandbox_daily_data.nl_depare_med_latest AS depth_area
aton_float is the layer with points depth_area is the layer with polygons
The query works, but the problem is that the geometry of depth_area is also being joined. This results (I think) in that my GIS environment can't load the layer. I only need the query to join the drval1 and drval2 columns from depth_area.