Timeline for PostGIS, create a view with a spatial join
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 27, 2022 at 9:00 | history | tweeted | twitter.com/StackGIS/status/1574685292031840256 | ||
| Sep 26, 2022 at 20:00 | vote | accept | CaptainAhab | ||
| Sep 26, 2022 at 19:59 | answer | added | CaptainAhab | timeline score: 0 | |
| Sep 26, 2022 at 12:25 | comment | added | CaptainAhab | Obviously stated I'm a postgis newbe, i tried this: SELECT aton_float.*, depth_area_a.drval1 AS depth_min, depth_area_a.drval2 AS depth_max FROM sandbox_daily_data.nl_aton_float_latest AS aton_float, sandbox_daily_data.nl_depare_med_latest AS depth_area_a LEFT JOIN sandbox_daily_data.nl_depare_med_latest AS depth_area ON st_contains(aton_float.geom,depth_area.geom); But this gives an error with this hint: INT: There is an entry for table "aton_float", but it cannot be referenced from this part of the query. | |
| Sep 26, 2022 at 11:06 | comment | added | Mario | 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. | |
| Sep 26, 2022 at 10:57 | history | edited | CaptainAhab | CC BY-SA 4.0 | added 626 characters in body |
| Sep 23, 2022 at 16:19 | comment | added | Mapperz♦ | Trigger after your data is updated then gis.stackexchange.com/a/147611/276 | |
| Sep 23, 2022 at 14:35 | comment | added | robin loche | 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. | |
| Sep 23, 2022 at 13:20 | comment | added | CaptainAhab | hey, not yet, but i'll get the manual and get cracking, thnx | |
| Sep 23, 2022 at 13:07 | comment | added | geozelot | 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? | |
| Sep 23, 2022 at 12:50 | history | asked | CaptainAhab | CC BY-SA 4.0 |