3

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.

6
  • 1
    Think of a View as a stored query - whenever you access a View the query you created it with will get executed, so it will always return the current state of the queried relations. So no need to recreate them. Do you know how to write a simple query that joins ON ST_Intersects?
    – geozelot
    Commented Sep 23, 2022 at 13:07
  • hey, not yet, but i'll get the manual and get cracking, thnx Commented Sep 23, 2022 at 13:20
  • What you want is probably a 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. Commented Sep 23, 2022 at 14:35
  • Trigger after your data is updated then gis.stackexchange.com/a/147611/276
    – Mapperz
    Commented Sep 23, 2022 at 16:19
  • Regarding your problem with the polygon geometry that is included: This is due to your select * from ...query. You should only select the columns you really need. Probably something like select 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 do refresh materalized view...when the data is updated, i.e. in your update script, that runs at midnight.
    – Mario
    Commented Sep 26, 2022 at 11:06

1 Answer 1

0

recognition to the good folks in the comments.
I finalized it on a MATERIALIZED VIEW with a automated REFRESH.

SELECT aton_float.*,
depth_area.drval1 AS depth_at_pos_min,
depth_area.drval2 AS depth_at_pos_max
FROM sandbox_daily_data.nl_aton_float_latest AS aton_float
LEFT JOIN sandbox_daily_data.nl_depare_med_latest AS depth_area
ON st_within(aton_float.geom,depth_area.geom);

thnx for all the help

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.