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 objective.
- 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.
Edit 26/09/2022:
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
ON st_intersects(depth_area.geom,aton_float.geom);
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.
ON ST_Intersects?MATERIALIZED VIEW: it's a table that is generated by a query, and this query is stored with it so you can easily update it. You can even add index on this view to fasten the access. But as geozelot said, start by doing the query you want, and then create the view with it.select * from ...query. You should only select the columns you really need. Probably something likeselect aton_float.attr1, aton_float.attr2, depth_area.drval1, depth_area.drval2, aton_float.geom from ....You can use this query to create a materialized view as @robinloche wrote. postgresql.org/docs/current/sql-creatematerializedview.html Also, bear in mind to dorefresh materalized view...when the data is updated, i.e. in your update script, that runs at midnight.