7
Functions and procedures in the PostgreSQL environment are costly due to their overhead, compared to the directly interpreted SQL. And functions are, indeed, a performance fence (or rather, a non-optimizable container) for the planner, in particular cases, and especially when used in complex statements.
While there are plenty of scenarios where a complex ...
5
The SQL should pick the first value
To give an accurate answer a definition of what is "first" is needed. I mean, it could be just the first random result returned by the database or it cat be sorted by some criteria.
One easy, probably inaccurate, way of getting a result, is to use an aggregate function in the additional desired columns:
CREATE ...
3
If you do not want to include these fields in the GROUP BY clause, you must use an aggregation function in your SELECT clause. I illustrate an example using the array_agg function. (Documentation array_agg function)
For the demonstration, I have created the following table:
Execute the following query by using the array_agg function directly in the SELECT ...
3
While still not in the official release, the withPoints family of functions is in a stable state since pgRouting 2.2 (?) and provides a dynamic interface for temporary nodes in a graph, i.e. routing between arbitrary points.
The key here is the points_sql where you specify the closest edge to any of your temporary nodes, and the fraction of line length their ...
2
Unfortunately, any JOIN combination would lead to a Cartesian product of matches between the two joined tables (i.e for each match of pointtable1 there would be all matches from pointtable2 in the result set).
Assuming proper indexes and updated statistics for both point tables, running
SELECT ply.polyname, SUM(pnt1.cnt) AS pointtable1count, SUM(pnt2.cnt) ...
2
If you want to run a query within a function call, it must be enclosed in another set of parentheses select myfunction((select .. from ..))
But this won't be necessary for this query. ST_Collect expects a geometry field, not a table and even less record(s) with something else but geometries.
INSERT INTO multypoly_test (name,geom)
SELECT 'MyName', ST_Collect(...
1
Thanks to the extensive help of @mdsumner, this solution works by using the polymer R package to break polygons down into a mesh of triangles, calculating the overlapping triangle segments, and then reassembling the triangles into polygons.
This approach is slower but it seems to generally be robust to these non-noded intersection errors which here stem from ...
1
I believe you need to specify the path to proj.db in an PROJ_LIB environment variable.
See proj documentation
Only top voted, non community-wiki answers of a minimum length are eligible
